Custom Query for finding VMs without software installed

%3CLINGO-SUB%20id%3D%22lingo-sub-1501074%22%20slang%3D%22en-US%22%3ECustom%20Query%20for%20finding%20VMs%20without%20software%20installed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1501074%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20was%20hoping%20someone%20maybe%20able%20to%20help%20me.%20Within%20Log%20Analytics%20I%20can%20query%20for%20VMs%20with%20certain%20software%20installed.%3C%2FP%3E%3CP%3EBut%20I%20need%20to%20query%20where%20the%20VMs%20haven't%20got%20a%20certain%20software%20installed.%20Has%20anyone%20any%20suggestions%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1501074%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Eloganalytics%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1501560%22%20slang%3D%22en-US%22%3ERe%3A%20Custom%20Query%20for%20finding%20VMs%20without%20software%20installed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1501560%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F715386%22%20target%3D%22_blank%22%3E%40awood86%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%0A%3CP%3EThere%20are%20different%20ways%20to%20go%20about%20it%2C%20one%20way%20is%20to%20create%20a%20set%20of%20all%20installed%20software%20items%20(within%20a%20given%20time%20range)%20and%20check%20if%20a%20value%20is%20in%20that%20set.%3C%2FP%3E%0A%3CP%3ENote%20that%20if%20it's%20not%20in%20the%20set%2C%20it%20only%20means%20it%20wasn't%20installed%20in%20that%20time%20range%2C%20but%20it's%20still%20possible%20it's%20been%20installed%20earlier...%20so%20think%20well%20what's%20the%20time%20range%20you%20want%20to%20use.%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3EConfigurationData%20%0A%7C%20where%20TimeGenerated%20%26gt%3B%20ago(3d)%20%0A%7C%20where%20ConfigDataType%20%3D%3D%20%22Software%22%0A%7C%20summarize%20all_sotftware_installed%20%3D%20make_set(SoftwareName)%20by%20Computer%0A%7C%20where%20set_has_element(all_sotftware_installed%2C%20%22Microsoft%20365%20-%20en-us%22)%20%3D%3D%200%20%2F%2F%200%20means%20it's%20not%20in%20the%20set%2C%201%20means%20it%20is%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHTH%2C%3C%2FP%3E%0A%3CP%3ENoa%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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 installed. Has anyone any suggestions?

 

Thanks

3 Replies

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

@Noa Kuperberg thank you so much! This gives me exactly what I needed.

Just out of interest what other way would you suggest about getting this data?

@awood86 really depends on your needs and your setup. The suggested query is probably the most straightforward. If you're using the Update Management solution, you can also check out the Update table for installed or needed updates.