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...
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