1

We wanted to remove the users who are inactive in azure portal beyond 90 days for security reasons. We had setup azure ad diagnostics setting push data to this log analytics workspace. Now we are able to run the following KQL query.But the query is not accurate since it gets the users before 90 days but some of the users also logged-in with-in last 90 days also.

SigninLogs
|extend d =parse_json(AuthenticationDetails)
|where  todatetime(d[0].authenticationStepDateTime)  < ago(90d) 
and TimeGenerated  < ago(90d) and AppDisplayName == "Azure Portal" 
and OperationName =="Sign-in activity" and isnotempty(AlternateSignInName) 
|project Identity,Location,AlternateSignInName, authenticationStep= d[0].authenticationStepDateTime
|distinct  AlternateSignInName, Identity

Update: I updated the query to the following based on answer. But it picks up the date range from log analytics default timerange instead of the query timerange. Any idea how to fix this timerange?

let SigninUsersBeyond90Days = SigninLogs
| extend d = parse_json(AuthenticationDetails)
| extend LoginTimestamp = todatetime(d[0].authenticationStepDateTime)
| where AppDisplayName == "Azure Portal" and OperationName == "Sign-in activity" and isnotempty(AlternateSignInName) 
| summarize max(LoginTimestamp) by AlternateSignInName, Identity
| where max_LoginTimestamp > ago(90d)
| distinct AlternateSignInName;

SigninLogs
| extend d = parse_json(AuthenticationDetails)
| extend LoginTimestamp = todatetime(d[0].authenticationStepDateTime)
| where AppDisplayName == "Azure Portal" and OperationName == "Sign-in activity" and isnotempty(AlternateSignInName)  
and AlternateSignInName  !in(SigninUsersBeyond90Days)
| summarize max(LoginTimestamp) by AlternateSignInName, Identity
| where max_LoginTimestamp < ago(90d)
| distinct AlternateSignInName, Identity

KQL Query Window

Updated the query to pull users who did not signin for past 90 days in azure portal as below.This also fixes my above log analytics filter issue as suggested.

let SigninUsersWithin90Days = SigninLogs
| extend d = parse_json(AuthenticationDetails)
| extend LoginTimestamp = todatetime(d[0].authenticationStepDateTime)
| where AppDisplayName == "Azure Portal" and OperationName == "Sign-in activity" and isnotempty(AlternateSignInName) 
| summarize max(LoginTimestamp) by AlternateSignInName, Identity
| where max_LoginTimestamp < ago(90d) 
| distinct AlternateSignInName;

SigninLogs
| extend d = parse_json(AuthenticationDetails)
| extend LoginTimestamp = todatetime(d[0].authenticationStepDateTime)
| where AppDisplayName == "Azure Portal" and OperationName == "Sign-in activity" and isnotempty(AlternateSignInName)  and TimeGenerated > ago(90d)
and AlternateSignInName  !in(SigninUsersWithin90Days)
| summarize max(LoginTimestamp) by AlternateSignInName, Identity
| where max_LoginTimestamp > ago(90d) 
| distinct AlternateSignInName, Identity
Marcer
  • 839
  • 1
  • 12
  • 25
  • 1
    I think you need to find the max authenticationStepDateTime to be more than 90 days ago. Something like `|where max(todatetime(d[0].authenticationStepDateTime)) < ago(90d)`. – Gaurav Mantri Jul 25 '20 at 15:22
  • 1
    Regarding "Update: I updated the query to the following based on answer. But it picks up the date range from log analytics default timerange instead of the query timerange. Any idea how to fix this timerange?" - if you add a filter on a datetime column in your table, LogAnalytics will use whatever you provided instead of the default filter. – Slavik N Jul 26 '20 at 13:33
  • What is your purpose in filtering out users with empty AlternateDisplayNames (`isnotempty(AlternateSignInName`)? – SvenAelterman Jan 03 '22 at 22:46

1 Answers1

3

You need to use the max aggregation function, something like this:

SigninLogs
| extend d = parse_json(AuthenticationDetails)
| extend LoginTimestamp = todatetime(d[0].authenticationStepDateTime)
| where AppDisplayName == "Azure Portal" and OperationName == "Sign-in activity" and isnotempty(AlternateSignInName) 
| summarize max(LoginTimestamp) by AlternateSignInName, Identity
| where max_LoginTimestamp < ago(90d)
| distinct AlternateSignInName, Identity
Slavik N
  • 4,705
  • 17
  • 23