Custom Detection rule to find Inactive Device

New Contributor

Hello, My Org Planning to create incidents whenever the device goes inactive state in Microsoft Defender for Endpoint. It would be much appreciated if I get the query(KQL) to list the Inactive device. Thanks in Advance

3 Replies

@Arjun_Rajan You can use the following query, which runs fine but there is an error in it per MDE, which wont let me save this as a custom detection rule :

| where ConfigurationId in ('scid-2000', 'scid-2001')
| extend Test = case(
ConfigurationId == "scid-2000", "SensorEnabled",
ConfigurationId == "scid-2001", "SensorDataCollection",
Result = case(IsApplicable == 0, "N/A", IsCompliant == 1, "GOOD", "BAD")
| extend packed = pack(Test, Result)
| summarize Tests = make_bag(packed), DeviceName = any(DeviceName) by DeviceId
| evaluate bag_unpack(Tests)
| where SensorEnabled == "GOOD" and SensorDataCollection == "BAD"
| summarize by DeviceName, DeviceId

@Princely  Much appreciated your response to my query. Unfortunately, It does not return any result even if I choose the last 30 days. Please let me know if you happen to know how to set the Time range in the query. 


However, I do get all inactive devices by running the below query


| where ConfigurationId in ("scid-91", "scid-2000", "scid-2001")
| summarize arg_max(Timestamp, IsCompliant, IsApplicable) by DeviceId, ConfigurationId
| extend Test = case(
    ConfigurationId == "scid-2000", "SensorEnabled",
    Result = case(IsApplicable == 0, "N/A", IsCompliant == 1, "GOOD", "BAD")
| extend packed = pack(Test, Result)
| summarize Tests = make_bag(packed) by DeviceId
| evaluate bag_unpack(Tests)

The query you had mentioned seems to be giving the health status of each device.

And the one I had mentioned would specifically return the devices with a sensor enabled but no sensor data returned. Do you not have any hosts in your environment that match this criteria ? I suspect that is the case here.  



Princely Dmello