KQL OSPlatform count, DeviceTvmSecureConfigurationAssessment

Occasional Contributor

I'm working with a query that was posted on Github for "Endpoint Agent Health Status Report" however I only want to show for our Servers.  I was able to put in a Where for specific OS but the server counts seem to be wrong.  I wanted to create a KQL query that would should the OS by count().

 

DeviceTvmSecureConfigurationAsseessment

| where innotempty(DeviceName)

| summarize OSCount = count() by OSPlatform

 

There query runs but it shows way to many devices for each OS.  I added a timestamp to only select from the last 24hrs but it still shows a lot.  For example, we have perhaps a total of 178 Servers but it is giving me over 3k linux servers and over 13k of Windows 2016.

 

Please assist with the correct method to user kql to get the info.

 

Thanks,

3 Replies
Spoiler
 

Try something like the following:

let serverbuilds = dynamic(["20349" , "20348" , "19042" , "18363" , "17763" , "14393"]);
DeviceTvmSecureConfigurationAssessment
| where isnotempty(DeviceName)
| join kind=leftouter (
    DeviceInfo    
) on DeviceName
| where isnotempty(OSBuild)
| where OSBuild in (serverbuilds)
| distinct DeviceName, OSPlatform, OSBuild
| summarize count() by DeviceName, OSPlatform, OSBuild
Thank you for the response. I get results, unfortunately Windows 10 are included. I was looking for only Servers, Linux and Window Servers. I'm going to work on resolving the issue.
I wanted to let you know I really appreciated your Must Learn KQL series. It is what has gotten me into learning KQL. A lot more to learn but I'm trying to write something everyday to help build my skill set.
Keep you posted.
Cheers,
That is great to hear! Thanks for letting me know it has helped!

I don't have the same data that you do, but this should give you a start. DeviceTvmSecureConfigurationAssessment doesn't contain what you need to get server vs workstation. You need to 'join' the table with DeviceInfo.