Forum Discussion
Azure Sentinel rule to identify if user has not produced any events in 60 days
- Assuming you have just listed your userprincipalnames in your watchlist, and your on premise account is just part before the @ then these two should work. When joining you need to have a column that matches on both sides (your query and your watchlist). For signinlogs userprincipalname is fine because that's what Azure AD uses to identify people. We will just rename userprincipalname to username to match your watchlist. I added ResultType = 0 to only get successful signins, but you can remove if you want
let adminlist = (_GetWatchlist("Elevated_accounts")|project UserName);
SigninLogs
| where TimeGenerated > ago (30d)
| extend UserName = UserPrincipalName
| where UserName in (adminlist)
| where ResultType == 0
| distinct UserName
| join kind=rightanti adminlist on UserName
For SecurityEvent we want to use TargetUserName, so we will rename it when we set our variable and trim the @yourdomain.com part out
let adminlist = _GetWatchlist("Elevated_accounts")|extend TargetUserName = trim_end(@"@(.*)", UserName)|project TargetUserName;
SecurityEvent
| where TimeGenerated > ago(30d)
| where TargetUserName in (adminlist)
| distinct TargetUserName
| join kind = rightanti adminlist on TargetUserName
Try those and let me know
Something like this should work for you. My data isn't an exact match for your formatting but you should be able to make it work in your environment. I created a test WatchList with a single column of Account, then a few rows of DOMAIN\username fields that had been active, and then some fake accounts that hadn't (fake inactive admins)
let adminlist = (_GetWatchlist("TestWatchList")|project Account);
SecurityEvent
| where TimeGenerated > ago(10d)
| where Account in (adminlist)
| distinct Account
| join kind=rightanti adminlist on Account
Join operator - https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/joinoperator?pivots=azuredataexplorer#right-anti-join-flavor rightanti should return results from the right (your watchlist) that aren't in the left (your query for active users)
