write KQL query rule for azure sentinel that should be triggered whenever we do not have logs from any of the endpoint devices for the last 24 hours

Danaa Salam 40 Reputation points
2023-11-24T12:30:24.7866667+00:00

I need a KQL query rule for azure sentinel that should be triggered whenever we do not have logs from any of the endpoint devices. The query should look for every tables. So we can start as the following:

 

union withsource = tt *

 

This is what I tried:

| summarize maxTimeGenerated= max(TimeGenerated) by DeviceName
| where maxTimeGenerated between (ago(1h) .. now())

 

But this is not proper as this may create some false positives. Because ,After I run this query i get some devices which are actually sending logs for the last 24 hours.

 

Microsoft Sentinel
Microsoft Sentinel
A scalable, cloud-native solution for security information event management and security orchestration automated response. Previously known as Azure Sentinel.
1,058 questions
0 comments No comments
{count} votes

Accepted answer
  1. Clive Watson 5,951 Reputation points MVP
    2023-11-24T15:25:18.3833333+00:00

    Hello, You can run a Logs Query like this

    union Device*
    | where TimeGenerated > ago(2d)
    | summarize LastReported=now()-max(TimeGenerated) by DeviceName
    | where LastReported !between(0m..60m)
    //| | where LastReported > 60m  //another way of writing this
    

    However, a Rule does not support a union using "*". So even when you finish testing it wont be runnable as an Analytic Rule. You'll have to list all the tables that have DeviceName and use that as part of the union
    User's image


0 additional answers

Sort by: Most helpful