Home

OMS / Log Analytics - Filter Servers vs. Client computers

%3CLINGO-SUB%20id%3D%22lingo-sub-260566%22%20slang%3D%22en-US%22%3EOMS%20%2F%20Log%20Analytics%20-%20Filter%20Servers%20vs.%20Client%20computers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-260566%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20a%20rookie%20into%20using%20Dashboards%2C%20PowerBI%2C%20Query%2C%20so%20bear%20with%20me%2C%20if%20my%20questions%20are%20basic%20%3A)%3C%2Fimg%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1.%20I%20would%20like%20to%20setup%20an%20Custom%20Update%20Dashboard%20on%20Azure%2C%20that%20contains%20the%20following%20views%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3BChile%20servers%20with%20missing%20critical%20updates%20or%20security%20updates%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3BDenmark%20servers%20with%20missing%20critical%20updates%20or%20security%20updates%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3BNorway%20servers%20with%20missing%20critical%20updates%20or%20security%20updates%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3BChile%20client%20computers%20with%20missing%20critical%20updates%20or%20security%20updates%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3BDenmark%20%3CSPAN%3Eclient%20computers%3C%2FSPAN%3E%20with%20missing%20critical%20updates%20or%20security%20updates%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3BNorway%20%3CSPAN%3Eclient%20computers%3C%2FSPAN%3E%20with%20missing%20critical%20updates%20or%20security%20updates%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20hoping%20to%20be%20able%20to%20filter%20by%20RemoteIPCountry%20and%20OSVersion%20or%20similar%3C%2FP%3E%3CP%3EIt%20would%20be%20perfert%20with%20a%20nice%20graph%20per%20country%20with%20possibility%20to%20drill%20down%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20do%20I%20do%20that%20%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E2.%20I%20have%20also%20read%20that%20I%20should%20be%20able%20to%20clone%20current%20views%20in%20Log%20Analytics%20View%20Designer%2C%20but%20in%20my%20case%2C%20I%20don't%20see%20any%20of%20the%20standard%20OMS%20views%20in%20the%20View%20Designer%20-%20just%20a%20clean%20template%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheers%3C%2FP%3E%3CP%3EMorten%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-260566%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Log%20Analytics%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-288416%22%20slang%3D%22en-US%22%3ERe%3A%20OMS%20%2F%20Log%20Analytics%20-%20Filter%20Servers%20vs.%20Client%20computers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-288416%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%0A%3CP%3EI%20see%20the%20OS%20information%20is%20not%20always%20populated%20in%20the%20%22Update%22%20table%2C%20so%20if%20that's%20the%20case%20you%20can%20join%20on%20the%20%22Heartbeat%22%20table%2C%20like%20in%20%3CA%20href%3D%22https%3A%2F%2Fportal.loganalytics.io%2FDemo%3Fq%3DH4sIAAAAAAAAA2WPwWrDMAyG74G8w09OLfiyB8gpg62XtSzt7k6sUHeOHWyZkdGHn5KthdGT8adf0qfTZDQTyuKKrzNFwmkFLS%252B0rlG9ERkyFbQ32E9sg9duLQzaJfrljdMp2cH2eqnDemyqlvocLc9%252FA1OlUDUCJOTubLvsvQRp%252BLTe1HA0cMhMEZuyAPBKOnJHmtffFSmPo472m9DNaMI4LVmFfXucJ1J4pzEw7Q5NyJ7jrPBBMYlRWWwhXreG9dophgv1jKMd6YU8RREyCm3IsadbcifkkDtn05nMsySU5NnJ8%252F9k9SDzdF8uE2IwuecHP%252FH4AbwYUxN%252FAQAA%26amp%3Btimespan%3DP1D%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ethis%20query%3C%2FA%3E%3A%3C%2FP%3E%0A%3CPRE%3EUpdate%20%0A%7C%20where%20UpdateState%20%3D%3D%20%22Needed%22%20and%20Optional%20%3D%3D%20%22false%22%20and%20Classification%20in%20(%22Security%20Updates%22%2C%20%22Critical%20Updates%22)%0A%7C%20join%20kind%3D%20leftouter%20(%0A%20%20%20Heartbeat%0A%20%20%20%7C%20summarize%20by%20Computer%2C%20OSType%2C%20RemoteIPCountry%2C%20Version%0A)%20on%20Computer%20%0A%7C%20project%20TimeGenerated%2C%20SourceComputerId%2C%20PublishedDate%2C%20Title%2C%20Classification%2C%20Computer%2C%20OSType1%2C%20Version%2C%20Product%2C%20RemoteIPCountry%20%3C%2FPRE%3E%0A%3CP%3Enote%20that%20in%20the%20last%20%22project%22%20line%2C%20I%20include%20OSType1%20(taken%20from%20Heartbeat%20table)%20instead%20of%20OSType%20(from%20the%20Update%20table)%20which%20is%20empty.%20That%20way%20you%20also%20get%20the%20Version%20(of%20the%20OS)%20and%20RemoteIPCountry%20to%20filter%20by.%20I%20hope%20the%20OS%20Type%20and%20version%20together%20can%20help%20you%20identify%20client%20vs%20server%20OSs.%3C%2FP%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
Morten Waltorp Knudsen
Contributor

I'm a rookie into using Dashboards, PowerBI, Query, so bear with me, if my questions are basic :)

 

1. I would like to setup an Custom Update Dashboard on Azure, that contains the following views:

   Chile servers with missing critical updates or security updates

   Denmark servers with missing critical updates or security updates

   Norway servers with missing critical updates or security updates

 

   Chile client computers with missing critical updates or security updates

   Denmark client computers with missing critical updates or security updates

   Norway client computers with missing critical updates or security updates

 

I'm hoping to be able to filter by RemoteIPCountry and OSVersion or similar

It would be perfert with a nice graph per country with possibility to drill down

 

How do I do that ?

 

2. I have also read that I should be able to clone current views in Log Analytics View Designer, but in my case, I don't see any of the standard OMS views in the View Designer - just a clean template

 

 

Cheers

Morten

 

1 Reply

Hi,

I see the OS information is not always populated in the "Update" table, so if that's the case you can join on the "Heartbeat" table, like in this query:

Update 
| where UpdateState == "Needed" and Optional == "false" and Classification in ("Security Updates", "Critical Updates")
| join kind= leftouter (
   Heartbeat
   | summarize by Computer, OSType, RemoteIPCountry, Version
) on Computer 
| project TimeGenerated, SourceComputerId, PublishedDate, Title, Classification, Computer, OSType1, Version, Product, RemoteIPCountry 

note that in the last "project" line, I include OSType1 (taken from Heartbeat table) instead of OSType (from the Update table) which is empty. That way you also get the Version (of the OS) and RemoteIPCountry to filter by. I hope the OS Type and version together can help you identify client vs server OSs.

 

HTH,

Noa