Query Details
Compliance Number Of Active Devices In The Last7and30days
Query
let Monthly = IntuneDeviceComplianceOrg | where todatetime(LastContact) > ago(30d) | summarize arg_max(TimeGenerated, *) by DeviceName | summarize count(DeviceName) | extend CustomName="Active Devices" | extend MonthlyCount=count_DeviceName; let Weekly= IntuneDeviceComplianceOrg | where todatetime(LastContact) > ago(7d) | summarize arg_max(TimeGenerated, *) by DeviceName | summarize count(DeviceName) | extend CustomName="Active Devices" | extend WeeklyCount=count_DeviceName; Monthly | join kind=inner Weekly on CustomName | project-rename ['Last 30 Days']=MonthlyCount, ['Last 7 Days'] = WeeklyCount, Description=CustomName | project Description,['Last 7 Days'],['Last 30 Days']
Explanation
This query is creating two separate counts of active devices, one for the past 30 days and one for the past 7 days, from the IntuneDeviceComplianceOrg data.
The first part of the query, defined as 'Monthly', filters the data to only include devices that have had contact in the past 30 days. It then groups this data by the device name and counts the number of unique device names, which gives the total number of active devices in the past 30 days.
The second part of the query, defined as 'Weekly', does the same thing but for the past 7 days.
Finally, the two counts are joined together based on the custom name 'Active Devices', and the results are displayed with the descriptions 'Last 30 Days' and 'Last 7 Days'.
Details
Ugur Koc
Released: June 27, 2022
Tables
Keywords
Operators