Query Details
Compliance Numberof Deviceswith Device Health Threat Level Status
Query
// Number of Devices with DeviceHealthThreatLevel Status let secured = IntuneDeviceComplianceOrg | where isnotempty(DeviceHealthThreatLevel) | where DeviceHealthThreatLevel == "Secured" | distinct DeviceName, UserName , DeviceHealthThreatLevel | summarize count(DeviceName) | extend ['Number of Devices'] = count_DeviceName | extend Status = "Secured"; let notsecured = IntuneDeviceComplianceOrg | where isnotempty(DeviceHealthThreatLevel) | where DeviceHealthThreatLevel == "Not Secured" | distinct DeviceName, UserName , DeviceHealthThreatLevel | summarize count(DeviceName) | extend ['Number of Devices'] = count_DeviceName | extend Status = "Not Secured"; let unknown = IntuneDeviceComplianceOrg | where isnotempty(DeviceHealthThreatLevel) | where DeviceHealthThreatLevel == "Unknown" | distinct DeviceName, UserName , DeviceHealthThreatLevel | summarize count(DeviceName) | extend ['Number of Devices'] = count_DeviceName | extend Status = "Unknown"; secured | union notsecured, unknown | project Status, ['Number of Devices'] | sort by ['Number of Devices']
Explanation
This query is used to count the number of devices in an organization that are classified under three different health threat levels: "Secured", "Not Secured", and "Unknown". It first checks if the DeviceHealthThreatLevel field is not empty, then it filters the devices based on their health threat level. It counts the number of unique devices for each threat level and assigns the count to a new field called 'Number of Devices'. It also assigns the threat level status to a new field called 'Status'. Finally, it combines the results from the three threat level categories, displays the 'Status' and 'Number of Devices' for each category, and sorts the results by the number of devices.
Details
Ugur Koc
Released: July 14, 2022
Tables
Keywords
Operators