How to write a kql comparing 2 different tables(signins, threatintelligence) to create alert if the sign in ip matches with the ip reported by threatintelligence.

Harish Menti 0

I tried multiple ways to join the tables but ended up getting multiple errors, and I am not able to call the table that I referred into a variable using the let operator after I refer other table after it. As I was not able to use the first defined variable as universal variable so I can call that variable after I refer another table of interest into another variable and compare both IPs. I finally would want the kql to raise alert if it sees sigins from ip that is reported in the threatintell

Givary-MSFT 30,521 Reputation points Microsoft Employee

2024-06-24T05:11:58.0033333+00:00
@Harish Menti Thank you for reaching out to us, check the below approach/query if this helps to achieve your ask (generated by internal AI tool)

To compare the SigninLogs table with the ThreatIntelligenceIndicator table and create an alert if the IP address in the SigninLogs table matches the IP address in the ThreatIntelligenceIndicator table, you can use the following KQL query:

let threatIntelligence = ThreatIntelligenceIndicator | where type == "ip" | project ThreatIP = tostring(properties.indicatorValue); SigninLogs | where TimeGenerated > ago(1d) | where ClientIP != "" | join kind=inner ( threatIntelligence ) on $left.ClientIP == $right.ThreatIP | project TimeGenerated, ClientIP, ThreatIP

This query first defines a variable threatIntelligence that filters the ThreatIntelligenceIndicator table to only include indicators of type "ip" and projects the indicatorValue as ThreatIP.

Then, the query filters the SigninLogs table to only include logs from the last day and where the ClientIP is not empty. It then performs an inner join with the threatIntelligence variable on the ClientIP field in the SigninLogs table and the ThreatIP field in the threatIntelligence variable.

Finally, the query projects the TimeGenerated, ClientIP, and ThreatIP fields and returns the results.

You can use this query as the basis for an alert rule that triggers when the query returns results.

Let me know if this helps or not, feel free to post back.

Share via

How to write a kql comparing 2 different tables(signins, threatintelligence) to create alert if the sign in ip matches with the ip reported by threatintelligence.