KQL Search

Query Details

HomeAI ToolsDevice Query

Compliance Number Of Company Owned Devices That Are Compliant Or Not Compliant

Query

// Number of Company owned Devices that are Compliant or Not Compliant summarized by OS Platform
let notcompliant =
    IntuneDeviceComplianceOrg
    | where ComplianceState == "Not compliant"
    | where OwnerType == "Company"
    | where OSDescription != ""
    | summarize arg_max(TimeGenerated, *) by DeviceName
    | project ComplianceState, DeviceName, LastContact, OSDescription, OS
    | summarize count(DeviceName) by OSDescription;
let compliant =
    IntuneDeviceComplianceOrg
    | where ComplianceState == "Compliant"
    | where OwnerType == "Company"
    | where OSDescription != ""
    | summarize arg_max(TimeGenerated, *) by DeviceName
    | project ComplianceState, DeviceName, LastContact, OSDescription, OS
    | summarize count(DeviceName) by OSDescription;
notcompliant
| join kind=inner compliant on OSDescription
| project-rename
    ["Compliant"]=count_DeviceName1,
    ["Not Compliant"]=count_DeviceName,
    ["Platform"]=OSDescription
| project ["Platform"], ["Compliant"], ["Not Compliant"]
| sort by Compliant desc

Explanation

This query is counting the number of company-owned devices that are either compliant or not compliant with certain standards, and then grouping these counts by the operating system (OS) of the devices.

First, it identifies all the non-compliant company-owned devices, excludes those without an OS description, and then counts them by their OS description.

Then, it does the same for compliant devices.

Finally, it combines the counts of compliant and non-compliant devices for each OS into a single table, and sorts this table in descending order by the number of compliant devices.

Details

Ugur Koc profile picture

Ugur Koc

Released: December 29, 2022

Tables

IntuneDeviceComplianceOrg

Keywords

Number,Company,Devices,Compliant,NotCompliant,OSPlatform,IntuneDeviceComplianceOrg,ComplianceState,OwnerType,OSDescription,TimeGenerated,DeviceName,LastContact,OS,Join,Platform,Sort

Operators

letIntuneDeviceComplianceOrgwhereComplianceStateOwnerTypeOSDescriptionsummarizearg_maxTimeGenerated*byDeviceNameprojectComplianceStateDeviceNameLastContactOSDescriptionOScountDeviceNamejoinkindinnercompliantonOSDescriptionproject-rename["Compliant"]count_DeviceName1["Not Compliant"]count_DeviceName["Platform"]OSDescriptionproject["Platform"]["Compliant"]["Not Compliant"]sortbyCompliantdesc.

Actions