Forum Discussion

Marc_Jacquard's avatar
Marc_Jacquard
Copper Contributor
Jul 16, 2021

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

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...
  • m_zorich's avatar
    m_zorich
    Jul 20, 2021
    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
Marc_Jacquard's avatar
Marc_Jacquard
Copper Contributor
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
m_zorich's avatar
m_zorich
Iron Contributor
Jul 20, 2021

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?

Resources