Forum Discussion

awood86's avatar
awood86
Copper Contributor
Jul 01, 2020

Custom Query for finding VMs without software installed

Hi,   I was hoping someone maybe able to help me. Within Log Analytics I can query for VMs with certain software installed. But I need to query where the VMs haven't got a certain software install...
azure monitor
loganalytics
Noa Kuperberg's avatar
Noa Kuperberg
Icon for Microsoft rankMicrosoft
Jul 01, 2020

Hi awood86 ,

There are different ways to go about it, one way is to create a set of all installed software items (within a given time range) and check if a value is in that set.

Note that if it's not in the set, it only means it wasn't installed in that time range, but it's still possible it's been installed earlier... so think well what's the time range you want to use.

ConfigurationData 
| where TimeGenerated > ago(3d) 
| where ConfigDataType == "Software"
| summarize all_sotftware_installed = make_set(SoftwareName) by Computer
| where set_has_element(all_sotftware_installed, "Microsoft 365 - en-us") == 0 // 0 means it's not in the set, 1 means it is

 

HTH,

Noa

pshahi12's avatar
pshahi12
Brass Contributor
Dec 10, 2022

Hello Noa Kuperberg - This query helps a lot in creating a scenario like this, I'm not getting 0/1 exactly but a full list of all installed software even when I try to match it against one to test.

I'm requesting help on the extension of this request. Below is the attached format which I'm trying to achieve for 'n' Softwares & services (e.g. Microsoft Advance Threat Protection) to showcase it as one of the Tab in my overall Azure Monitor workbook in below format. Servers projects fine but want to spread out only needed software/services as column which would have Status (installed/pending) in cell.

  • Clive_Watson's avatar
    Clive_Watson
    Bronze Contributor
    Dec 11, 2022

    pshahi12 

     

    If you know and can define the list of Software (which I called myList in this example), you can build a query like this

     


    Go to Log Analytics and run query

    let myList=dynamic 
    ([
     "Microsoft Monitoring Agent",
     "Dependency Agent",
     "Windows Admin Center"
    ]);
ConfigurationData 
| where TimeGenerated > ago(3d) 
| where ConfigDataType == "Software"
| summarize all_software = make_set(SoftwareName) by Computer
| mv-expand all_software to typeof(string)
| where all_software has_any (myList)
| evaluate pivot(all_software)

     

    You can then use the Workbook to colour these / rename the values.

    You can also add extra wild card search data easily  e.g Any occurance of "SQL Server"

    | where all_software has_any (myList) or all_software has_any ("SQL Server")  


    • pshahi12's avatar
      pshahi12
      Brass Contributor
      Dec 11, 2022

      Thanks a lot Clive for guidance, this looks great. Sure, let me add the wildcard and try as softwares are also installed as services.


      Edit 04/01 - My requirements has changed and now drilling down on more varied states so Customizing query for it. Thanks a lot for the above guidance Clive_Watson, really appreciate your quick reply.

      Cheers (Y)

Resources