Forum Discussion
IdentityInfo with analytics KQL query
Hi,
I'm currently trying to create a KQL query for an alert rule in Sentinel. The log source upon which the alert rule is based, only contains the SAMAccountName, which prevents me from mapping it to an Account entity in the alert. I'm therefore trying to use the IdentityInfo table to lookup the AadUserId of the user, using the SAMAccountName.
The issue I'm running into is that I want my query to run every 10 minutes, and look up data from the past 10 minutes, as this is most suitable given the nature of the alert and the log source. This however causes the lookup in the IdentityInfo table to also only check data from the last 10 minutes, which doesn't work as the data in that table may be much older and therefor fail the lookup of the AadUserId of the user. According to the documentation, the IdentityInfo table is refreshed every 14 days, so for it to work I'd have to create a query that checks all logging, including that of the log source, from the past 14 days, which is not what I want.
Hopefully some of you have suggestions or ideas on how to make this work. Thanks a lot!
Marek
9 Replies
- AndrewBlumhardt
Microsoft
Hard to help without more transparency on your query but I would set the rule to 14 days, run a 14 day lookback query on IdentityInfo using arg_max, put that into a let variable, then run a 10 min query on the target table, and join the results. You might also look at building this as an XDR rule. Worst case setup a logic app to setup a reference watchlist.
IdentityInfo
| summarize arg_max(TimeGenerated, *) by SAMAccountNameThis is the query I currently have:
Log_source | where TimeGenerated >= ago(10m) | join kind=leftouter ( IdentityInfo | where TimeGenerated >= ago(14d) | distinct SAMAccountName, AccountObjectId ) on $left.sourceProcessUsername == $right.SAMAccountNameThis works as intended when run as a separate query, as it properly adds the AccountObjectId for each row. However when configuring it as an analytics rule, setting the lookup data to 14 days limits the query frequency to once an hour:
Maybe this is just the way Sentinel works, but I feel like I'm missing something and there is a more efficient way of solving this.
Marek- AndrewBlumhardt
The Sentinel rule settings override your query lookback (where TimeGenerated).
It is not documented but I suspect that putting your SAM lookup table into a let table first will prevent the rule from overriding.
So run your rule every 10 min if that is your preferred frequency with a reasonable lookback like 10-15 minutes. Create a lookup table first.
let SamLookup = IdentityInfo
| where TimeGenerated > ago(14d)
| summarize arg_max(TimeGenerated, *) by SAMAccountName;
Log_source
| project-rename SAMAccountName=sourceProcessUsername
| join SamLookup on SAMAccountName
Hard to help without more transparency on your query but I would set the rule to 14 days, run a 14 day lookback query on IdentityInfo using arg_max, put that into a let variable, then run a 10 min query on the target table, and join the results. You might also look at building this as an XDR rule. Worst case setup a logic app to setup a reference watchlist.
IdentityInfo
| summarize arg_max(TimeGenerated, *) by SAMAccountName- AndrewBlumhardt
EH?
