Forum Discussion

Browtastic670's avatar
Browtastic670
Copper Contributor
Oct 27, 2022
Solved

How to Prevent Duplicate Incidents from Being Generated due to Long Data Look Back

Hey everyone, We are facing an issue with regards to our rules on Sentinel and that is when we create a rule and, in its logic, we configure the query to lookup data from longer times, say the last ...
azure
data collection
detection
investigation
KQL
kusto
Log Data
monitoring
playbooks
siem
  • Clive_Watson's avatar
    Clive_Watson
    Oct 29, 2022

    Browtastic670 

     

    Step 1:
    Let's do a simple query over the past 2hrs to find distinct computers that start with "S" in their name

     

     

    Heartbeat
| where TimeGenerated between (now(-2h) .. now())
//| summarize min(TimeGenerated), max(TimeGenerated)
| where Computer startswith "S"
| distinct Computer

     


    This returns two computers that match:
    SQL12.na.contosohotels.com
    SQL00.na.contosohotels.com


    Step 2:
    Now we do similar for the past 14days, minus the most recent 2hrs
     

     

        Heartbeat
    | where TimeGenerated between (ago(14d) .. ago(2h))
    //| summarize min(TimeGenerated), max(TimeGenerated)
    | where Computer startswith "S"
    | distinct Computer

     

     This returns 3 computers:

    SQL12.na.contosohotels.com
    SQL00.na.contosohotels.com
    SQL01.na.contosohotels.com

    Step 3 (join it all together, in one query) 

     

    //
// Look back in the past 2 hours for a list of distinct Computers - 
// I only wanted to show named ones that start with an "S" to keep it simple
//
Heartbeat
| where TimeGenerated between (now(-2h) .. now())
//| summarize min(TimeGenerated), max(TimeGenerated)
| where Computer startswith "S"
| distinct Computer
//
// Now do the same for the past 14days minus the last 2 hours - this is key so we dont process the same data!!! 
//
| join kind=rightanti
 (
    Heartbeat
    | where TimeGenerated between (ago(14d) .. ago(2h))
    //| summarize min(TimeGenerated), max(TimeGenerated)
    | where Computer startswith "S"
    | distinct Computer
 ) on Computer​

     

    This returns the single computer that is only found in the last 2hrs but not in the previous 14days, usig a JOIN and rightanti :

     

    SQL01.na.contosohotels.com

     


    Click here to see this in action (it works at the time of writing but as the hours change, the filter I used on "S" may mean it won't be a good demo). 

    Go to Log Analytics and run query



    An alternative would be a similar query but lookup the found email or IP in the past two hours in the Incident/Alert in the SecurityIncident/SecurityAlert tables 

Browtastic670's avatar
Browtastic670
Copper Contributor
Oct 28, 2022
Hey Clive!

Thank you so much for your response.

The method that you are proposing seems very interesting (and a bit confusing) to us. How exactly do we go about implementing it?
For reference we have our original query below:
TheatIntel Table
| summarize by indicator_s
| join kind=rightsemi (
Web_Traffic_Table
| summarize by User=Email_s,Action_s,DestinationIP_s,HTTPMethod_s,HTTPHost_s,URL_s,Referer_s,UserAgent_s,DownloadedFileNames_s,UploadedFileNames_s
) on $left.indicator_s==$right.HTTPHost_s

Here what we are trying to do is correlate any matching Threat Intel Feeds with the Web Browsing Logs of our users.

Another solution that stems from SocInABox's comment is that we can add all the Threat Intel Feeds into a Watchlist instead of a table and that way we won't even have to worry about the time range.

But the issue of duplicate alerts will still remain 😕
Clive_Watson's avatar
Clive_Watson
Bronze Contributor
Oct 29, 2022

Browtastic670 

 

Step 1:
Let's do a simple query over the past 2hrs to find distinct computers that start with "S" in their name

 

 

Heartbeat
| where TimeGenerated between (now(-2h) .. now())
//| summarize min(TimeGenerated), max(TimeGenerated)
| where Computer startswith "S"
| distinct Computer

 


This returns two computers that match:
SQL12.na.contosohotels.com
SQL00.na.contosohotels.com


Step 2:
Now we do similar for the past 14days, minus the most recent 2hrs
 

 

    Heartbeat
    | where TimeGenerated between (ago(14d) .. ago(2h))
    //| summarize min(TimeGenerated), max(TimeGenerated)
    | where Computer startswith "S"
    | distinct Computer

 

 This returns 3 computers:

SQL12.na.contosohotels.com
SQL00.na.contosohotels.com
SQL01.na.contosohotels.com

Step 3 (join it all together, in one query) 

 

//
// Look back in the past 2 hours for a list of distinct Computers - 
// I only wanted to show named ones that start with an "S" to keep it simple
//
Heartbeat
| where TimeGenerated between (now(-2h) .. now())
//| summarize min(TimeGenerated), max(TimeGenerated)
| where Computer startswith "S"
| distinct Computer
//
// Now do the same for the past 14days minus the last 2 hours - this is key so we dont process the same data!!! 
//
| join kind=rightanti
 (
    Heartbeat
    | where TimeGenerated between (ago(14d) .. ago(2h))
    //| summarize min(TimeGenerated), max(TimeGenerated)
    | where Computer startswith "S"
    | distinct Computer
 ) on Computer​

 

This returns the single computer that is only found in the last 2hrs but not in the previous 14days, usig a JOIN and rightanti :

 

SQL01.na.contosohotels.com

 


Click here to see this in action (it works at the time of writing but as the hours change, the filter I used on "S" may mean it won't be a good demo). 

Go to Log Analytics and run query



An alternative would be a similar query but lookup the found email or IP in the past two hours in the Incident/Alert in the SecurityIncident/SecurityAlert tables 

Resources