Chcieliśmy usunąć użytkowników, którzy są nieaktywne w Lazure Portal poza 90 dni ze względów bezpieczeństwa. Ustawiliśmy ustawienie danych diagnostyki Azure Ad Diagnostics Push do tego obszaru roboczego analityki dziennika. Teraz jesteśmy w stanie uruchomić następujące kwery KQL

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

Aktualizacja: Zaktualizowałem zapytanie do następujących na podstawie odpowiedzi. Ale odbiera zakres daty od domyślnego rozkładu Log Analytics zamiast timerarem zapytania. Jakiś pomysł, jak naprawić ten 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

Zaktualizowano zapytanie, aby wyciągnąć użytkowników, którzy nie zalogowali się przez ostatnie 90 dni w Lazurowym Portalu, jak poniżej

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
0
John 25 lipiec 2020, 18:09

1 odpowiedź

Najlepsza odpowiedź

Musisz użyć Max Funkcja agregacji, coś takiego:

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
2
Slavik N 25 lipiec 2020, 17:48