Forum Discussion
RE: Tracking Compliance Status changes for Intune Device(s) with KQL/Playbook
JMSHW0420
It will be something like this. Lines 1-15 are ok, you will have to play with 16 onwards - the Device details are only in the SecurityAlert so you have to use that for a join (I didnt look closely, so this is just the start of a solution).
let notCompliant_ =
IntuneDeviceComplianceOrg
// from 7days to 1day ago
| where TimeGenerated between (ago(7d) ..ago(1d))
| where isnotempty(DeviceHealthThreatLevel)
| where ComplianceState != "Compliant"
| distinct DeviceName;
IntuneDeviceComplianceOrg
// from 1d to now
| where TimeGenerated between (ago(1d) ..now() )
| where isnotempty(DeviceHealthThreatLevel)
// only show if Device was previously in the non comoliant list
| where ComplianceState == "Compliant" and DeviceName in (notCompliant_)
| project TimeGenerated, ComplianceState, DeviceName, DeviceId, OS, UserName, UserEmail
| summarize arg_max(TimeGenerated, *) by DeviceId
|join (
SecurityAlert
| where TimeGenerated between (ago(7d) ..now())
| where AlertName =="Non-Compliant Device Detected"
| extend DeviceName = tostring(parse_json(Entities)[1].HostName)
) on DeviceName
Hi Clive_Watson,
Sorry for not coming back to you, but I have been in Germany for the last several days.
...so I have been looking at Line 16 onwards and becoming a little stuck again. Again any advice would be appreciated.
So the end goal is to 'retrieve' the related Incident Number (or Id).
So I have tried to apply a SECOND join on the SecurityAlert table with the SecurityIncident table, matching related AlertIds and then extracting the Incident Number.
JOIN on the SecurityIncident table not working...
let notCompliant_ =
IntuneDeviceComplianceOrg
// from 7days to 1day ago
| where TimeGenerated between (ago(7d) ..ago(1d))
| where isnotempty(DeviceHealthThreatLevel)
| where ComplianceState != "Compliant"
| distinct DeviceName;
IntuneDeviceComplianceOrg
// from 1d to now
| where TimeGenerated between (ago(1d) ..now() )
| where isnotempty(DeviceHealthThreatLevel)
// only show if Device was previously in the non compliant list
| where ComplianceState == "Compliant" and DeviceName in (notCompliant_)
| project TimeGenerated, ComplianceState, DeviceName, DeviceId, OS, UserName, UserEmail
| summarize arg_max(TimeGenerated, *) by DeviceId
| join (
SecurityAlert
| where TimeGenerated between (ago(7d) ..now())
| where AlertName == "Non-Compliant Device Detected"
| extend Entities = iff(isempty(Entities), todynamic('[{"dummy" : ""}]'), todynamic(Entities))
| mv-expand Entities
| expand id_ = tostring(Entities.["$id"]),
DeviceName = tostring(Entities.HostName)
| summarize arg_max(TimeGenerated,*) by SystemAlertId
) on DeviceName
| join (
SecurityIncident
| summarize arg_max(TimeGenerated,*) by IncidentNumber
| extend Alerts = extract("\\[(.*?)\\]", 1, tostring(AlertIds))
| mv-expand AlertIds to typeof(string)
) on $left.SystemAlertId == $right.AlertIds
| project IncidentName = Title, IncidentNumber=IncidentNumber, AlertName = AlertName
- This line is wrong
| expand id_ = tostring(Entities.["$id"]),
DeviceName = tostring(Entities.HostName)
it should be:
| extend id_ = tostring(Entities.["$id"]), DeviceName = tostring(Entities.HostName)
The rest looks ok, but I dont have all the data for this to test- Hi Clive_Watson,
Of course. I will test that tomorrow.
Understand regarding 'testing'. It was more the logical context of the 'joins' between the IntuneDeviceComplianceOrg / SecurityAlert / SecurityIncident tables.
I will update you here on how it goes.