Forum Discussion
Query to find true last login\last activity for on premise AD, Azure AD, and o365
You can add other Tables to this, but for AAD SigninLogs it would be, something like this. Look back 30d and show me where a user hasn't logged in in the past 12days (or whatever, 1h, 2h...1d, 2d etc...)
SigninLogs
| where TimeGenerated > ago(30d)
| summarize LastCall = max(TimeGenerated) by UserDisplayName
| where LastCall < ago(12d)
| order by LastCall asc
Last AD logon is:
SecurityEvent
| where TimeGenerated > ago(9d)
| where EventID in (4624) // An account was successfully logged on.
| summarize count(), arg_max(TimeGenerated, *) by EventID, Activity , Computer, LogonTypeName, Account
I may have time to merge them later....unless you can? 🙂
// Also for Logon and Logoff SecurityEvent | where TimeGenerated > startofday(ago(1d)) | where EventID in (4624, 4625) | where AccountType == 'User' | summarize StartTime = min(TimeGenerated), EndTimec = max(TimeGenerated), count() by LogonTypeName, Account, Activity | extend timestamp = StartTimeHowever want I think you need is something like this (assuming you have both the SigninLogs and SecurityEvent tables and data)? Feel free to modify as you require.
union isfuzzy=true ( SigninLogs | where TimeGenerated > startofday(ago(1d)) | summarize LastCall = max(TimeGenerated) by Account = UserDisplayName | where LastCall < ago(1h) | order by LastCall asc | project Account , LastCall , Table = "AD" ), ( SecurityEvent | where TimeGenerated > startofday(ago(1d)) | where EventID in (4624) //, 4625) | where AccountType == 'User' | summarize StartTime = min(TimeGenerated), LastCall = max(TimeGenerated), count() by LogonTypeName, Account, Activity | where LastCall < ago (1h) | project Account , LastCall , Table = "AAD" ) | summarize by Account, LastCall, Table- Hi Clive, I'm running into a similar issue. I'm trying to get a list of ALL accounts that have NOT logged into Azure within 30 days. I want to query ADFSSignInLogs and SigninLogs. Should I use those 2 that you have uptop there to pull data, or can I use the 2 I have posted? Could I use this query to achieve that or do you recommend something else?
Airizzo I think both Tables have the User in the Identity Column (I don't have any ADFSsigninLogs data to check, but the schema says this should work, just uncomment the ADFS part in the query.
union SigninLogs //, ADFSSignInLogs | where TimeGenerated > startofday(ago(90d)) | summarize lastRec = arg_max(TimeGenerated,*) by Identity | extend lastSeen = datetime_diff("Day", now(), lastRec) | where lastSeen > 30 | order by lastSeen descYou will need more than 30days of data for this to work, I used arg_max to get the last row for each identify and then datetime_diff to turn the result into a "day" integer. The where, then filters on Identities not seen for greater than 30days and up and till the hardcoded 90days
