Forum Discussion

JMSHW0420's avatar
JMSHW0420
Iron Contributor
Feb 09, 2023

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

Hello,

 

I am looking to build a Playbook based on Intune and Compliance Status.

 

First I have the following KQL query to check for 'Non-Compliance' status...

 

IntuneDeviceComplianceOrg
| where isnotempty(DeviceHealthThreatLevel)
| where ComplianceState != "Compliant"
| project TimeGenerated, ComplianceState, DeviceName, DeviceId, OS, UserName, UserEmail
| summarize arg_max(TimeGenerated, *) by DeviceId

 

Next, I will create an Analytics Rule (using the above query) that with generate an Incident but at the moment will NOT trigger any Automation.

 

Next, I want to 'capture' when an external trigger ((from a remediation action or Azure Policy) changes the 'Compliance' status changes to 'Compliant' for the Intune device.

 

Next, I want to capture in another KQL query the following:

 

- IF an Intune Device HAD the 'Compliance' status of 'Non-Compliance' OVER the last 7 days

- ...AND had an Incident created (this is the bit I am struggling with)

- <Is there a way to check for Incident creation?>

-  ...JOIN (or UNION) against SAME Intune Device where the 'Compliance' status has changed to 'Compliant' in the last 24hrs

 

Is there a way to perform a JOIN against the IntuneDeviceComplianceOrg TABLE and I believe the new-ish SecurityIncident TABLE?

 

Next, I want to set the above query against another Analytics Rule, where NO new Incident is generated BUT an Automation 'action' activates a Playbook/Logic App to commit an ACTION to close the 'related' Incident

analytics
KQL
playbooks
  • Clive_Watson's avatar
    Clive_Watson
    Bronze Contributor
    Feb 10, 2023

    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

     

    • JMSHW0420's avatar
      JMSHW0420
      Iron Contributor
      Feb 17, 2023

       

      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

    • JMSHW0420's avatar
      JMSHW0420
      Iron Contributor
      Feb 13, 2023
      Hi Clive_Watson,

      Thank you for the response.

      Had worked on something similar from lines 1- 7 BUT really do appreciate the remaining content of the query.

      I look at lines 16 onwards and come back to you later in the week, IF OK?

      Again, grateful for your help.

Resources