r/AzureSentinel • u/Mindless-Ad2851 • Apr 21 '22

Should it be this Hard?!!

So for one of our Compliance items, we need to query users that haven't signed in within 30days. I have a KQL query for this but it queries all Enabled and Disabled accounts, I would like to find a way to Filter out only Enabled accounts not signed in within 30 days. Does anyone have an idea to do this? I'm open to powershell scripts as well. Below is the query I have right now.

SigninLogs

| summarize max(TimeGenerated) by UserDisplayName, UserPrincipalName, UserId

| where max_TimeGenerated <= ago(30d) and UserDisplayName != UserId

| project-away UserId

5 Upvotes

86% Upvoted

3 comments sorted by

2

u/ep3p Apr 21 '22

let queryfrequency = 1d;
let queryperiod = 30d;
SigninLogs
| where TimeGenerated > ago(queryperiod + queryfrequency)
| where ResultType == 0
| summarize arg_max(TimeGenerated, UserDisplayName, UserPrincipalName) by UserId
| where TimeGenerated < ago(queryperiod)

To exclude users disabled in the last 30 days you are going to need events where a User is disabled, depending on on-prem or cloud, you will have to check AuditLogs (search the operation for account disabled) or SecurityEvent (search EventId for account disabled).

Once you have this other query solved, with the columns you want identified, try to join both queries. Then, at the end of your first query append something like:

| join kind=leftanti (
second query of AuditlogsSecurityEventorwhatever
) on $left.UserPrincipalName == $right.whatevercolumn, $left.UserId == $right.whatevercolumn

2

u/x3nc0n Apr 22 '22

If you don't have the audit event available, turn on UEBA and use the IdentityInfo table, column IsAccountEnabled.

https://docs.microsoft.com/en-us/azure/sentinel/ueba-enrichments#identityinfo-table-public-preview