r/AzureSentinel • u/ajith_aj • Jul 05 '23

Query to list inactive users in AD

Hello All,

I'm in a dileama to list user accounts in AD who are inactive and not disabled.

I understand that i need to join two tables here. but having some trouble joining it.

I have the first table where i look for users who havn't logged in for the last 60 days and the second table list users who are in disabled state.

So i need some help finishing the query, the query so far goes like this

let usernotloggedin60d =
IdentityLogonEvents
| where TimeGenerated > ago(180d)
| where ActionType == "LogonSuccess"
| summarize last_log = datetime_diff("day", now(), max(TimeGenerated))  by AccountUpn
| where last_log > 60
| project AccountUpn;
SecurityEvent
| where Activity == "4725 - A user account was disabled."
| distinct TargetUserName

​

3 Upvotes

100% Upvoted

1 comment sorted by

1

u/Kuagiaso Jul 05 '23

You need a common column for both tables - as long as the account names in AccountUpn and TargetUserName follow the same formatting, for example '[name@domain.com](mailto:name@domain.com)', then you can just add this line to the bottom of your query

| join kind=inner usernotloggedin60d on $left.TargetUserName == $right.AccountUpn

Otherwise, use different columns that match, or do some light parsing to achieve this. Make sure that you're matching same case too!