Azure Sentinel rule to identify if user has not produced any events in 60 days

[Marc_Jacquard]

I am working on a rule that uses a watchlist of elevated accounts. What I am trying to create is a rule that will tell me if one of these elevated accounts has not been used in over 60 days so we can mark it for removal from the list.  I am looking at the SecurityEvent table for any events these users generate. I can not seem to find a way to a "last event" type comparison. I can get a list of things they have done with starttime and endtime, but nothing I do seems to show a user that has not done any activity in 60 days or more. If anyone has done this, could you please share your rule/wisdom?

[#2]

@Marc_Jacquard I would use the SignInLogs to determine if the user has signed in during your time frame in question.  You could use the UserPrincipalName or UserDisplayName fields to compare against your Watchlist.

[#3]

Gary, I tried that initially, but I wanted to see any activity. Below is my current logic. I just can not seem to get the time comparison correct. No matter which log source I use, I always have people who who have been active in the last 7 days even though I know some have not been. I switch between in~ and !in~ for testing. It appears I just do not have the time comparison logic correct.
let adminlist = (_GetWatchlist("Elevated_accounts")|project UserName);
let starttime = 31d;
//let midtime = 30d;
//let endtime =10m;
SecurityEvent
| where TimeGenerated > ago(32d)
|extend Account = trim_start(@"^.*\\", Account)
| where Account in~ (adminlist)
//|where EventID == "4624"
//|where Account !endswith "$"
| summarize StartTimeUtc = min(TimeGenerated), EndTimeUtc = max(TimeGenerated) by Account, EventID
//|where EndTimeUtc - StartTimeUtc > starttime

[#4]

@Marc_Jacquard 

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=azuredataexplor... rightanti should return results from the right (your watchlist) that aren't in the left (your query for active users)

[#5]

@Marc_Jacquard I would recommend at least combining the two tables if you want to get the best picture of user activity.   My admin account has logged into my Azure Sentinel instance quite a bit in the last week but has not performed any activity that would show up in SecurityEvent (which shows information from the Windows machines so I am not sure that is the correct table.  AzureActivity may be better)

[#6]

@m_zorich 

Thank you. I will give this a try and see how it goes.

[#7]

@m_zorich 

If I use SignIn logs it seems to work fine. Then I look up the user the query said has been inactive in SecurityEvent table and it shows activity within the last 7 days.

[#8]

Can you share your query? If you query your SecurityEvent table without the watchlist lookup are you getting activity from the users, i.e are they definitely not triggering any security events?

[#9]

This is what I have been playing with. I tried to do some union stuff, but that just got ugly fast. I did one query for each table: SignIn logs and SecurityEvent logs. I think the SignIn logs one is working properly, but I definitely know the SecurityEvent one is not. There are certain accounts I know for a fact are used daily. They come up in the not active query, but then I just do a serach as flows and they show up active today.
24 hour timeframe
SecurityEvent
|serach "Accountname"
SignIn logs Query
let adminlist = (_GetWatchlist("Elevated_accounts")|project UserName);
let starttime = 91d;
//let midtime = 30d;
let endtime = 1d;
SigninLogs
// historical successful sign-in
|where TimeGenerated > ago(61d)
|extend UserPrincipalName = trim_end(@"@(.*)", UserPrincipalName)
| where UserPrincipalName in~ (adminlist)
|distinct UserPrincipalName
//| where TimeGenerated between (ago(starttime) .. ago(endtime) )
//| summarize by UserPrincipalName, Identity, TimeGenerated
|join kind = rightanti (
SigninLogs
|extend UserPrincipalName = trim_end(@"@(.*)", UserPrincipalName)
| where UserPrincipalName in~ (adminlist)
) on UserPrincipalName


SecurityEvents Query
let adminlist = (_GetWatchlist("Elevated_accounts")|project UserName);
//let starttime = 91d;
//let midtime = 30d;
let endtime = 1d;
SecurityEvent
// historical successful sign-in
|where TimeGenerated > ago(61d)
|extend Account = trim_end(@"@(.*)", UserPrincipalName)
| where Account in~ (adminlist)
|distinct Account
//|summarize by Account, TimeGenerated
|join kind = rightanti (
SecurityEvent
// historical successful sign-in
//|where TimeGenerated > ago(31d)
|extend Account = trim_start(@"^.*\\", Account)
| where Account in~ (adminlist)
//| summarize by Account, TimeGenerated
) on Account

[#10]

Sure no worries, and what format are your accounts, so UserPrincipalName = bobsmith@yourdomain.com and then account is just bobsmith? And is your watchlist just a list of userprincipalnames?

[#11 · Accepted solution]

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

[#12]

I will give it a try and see what happens