Forum Discussion
Query to find true last login\last activity for on premise AD, Azure AD, and o365
Need assistance with a query in finding true last activity and login for on premise AD, Azure AD, and o365 for a project for cleaning up AD inactive accounts. An output i'd like to see is display...
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, AccountI 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?
