RE: Tracking Compliance Status changes for Intune Device(s) with KQL/Playbook

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