Query to find true last login\last activity for on premise AD, Azure AD, and o365

%3CLINGO-SUB%20id%3D%22lingo-sub-1358387%22%20slang%3D%22en-US%22%3EQuery%20to%20find%20true%20last%20login%5Clast%20activity%20for%20on%20premise%20AD%2C%20Azure%20AD%2C%20and%20o365%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1358387%22%20slang%3D%22en-US%22%3E%3CP%3ENeed%20assistance%20with%20a%20query%20in%20finding%20true%20last%20activity%20and%20login%20for%20on%20premise%20AD%2C%20Azure%20AD%2C%20and%20o365%20for%20a%20project%20for%20cleaning%20up%20AD%20inactive%20accounts.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAn%20output%20i'd%20like%20to%20see%20is%20display%20last%20login%20greater%20then%20x%20days.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1358387%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Log%20Analytics%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1358412%22%20slang%3D%22en-US%22%3ERe%3A%20Query%20to%20find%20true%20last%20login%5Clast%20activity%20for%20on%20premise%20AD%2C%20Azure%20AD%2C%20and%20o365%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1358412%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F647216%22%20target%3D%22_blank%22%3E%40LeeMed%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20can%20add%20other%20Tables%20to%20this%2C%20but%20for%20AAD%20%3CSTRONG%3ESigninLogs%3C%2FSTRONG%3E%20it%20would%20be%2C%20something%20like%20this.%26nbsp%3B%20Look%20back%2030d%20and%20show%20me%20where%20a%20user%20hasn't%20logged%20in%20in%20the%20past%2012days%20(or%20whatever%2C%201h%2C%202h...1d%2C%202d%20etc...)%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-cpp%22%3E%3CCODE%3ESigninLogs%0A%7C%20where%20TimeGenerated%20%20%26gt%3B%20ago(30d)%0A%7C%20summarize%20LastCall%20%3D%20max(TimeGenerated)%20by%20UserDisplayName%0A%7C%20where%20LastCall%20%26lt%3B%20ago(12d)%0A%7C%20order%20by%20LastCall%20asc%20%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1364541%22%20slang%3D%22en-US%22%3ERe%3A%20Query%20to%20find%20true%20last%20login%5Clast%20activity%20for%20on%20premise%20AD%2C%20Azure%20AD%2C%20and%20o365%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1364541%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F647216%22%20target%3D%22_blank%22%3E%40LeeMed%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELast%20AD%20logon%20is%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-cpp%22%3E%3CCODE%3ESecurityEvent%0A%7C%20where%20TimeGenerated%20%26gt%3B%20ago(9d)%0A%7C%20where%20EventID%20in%20(4624)%20%20%2F%2F%20%20An%20account%20was%20successfully%20logged%20on.%0A%7C%20summarize%20count()%2C%20arg_max(TimeGenerated%2C%20*)%20%20by%20EventID%2C%20Activity%20%2C%20Computer%2C%20LogonTypeName%2C%20Account%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20may%20have%20time%20to%20merge%20them%20later....unless%20you%20can%3F%26nbsp%3B%20%3A)%3C%2Fimg%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1364795%22%20slang%3D%22en-US%22%3ERe%3A%20Query%20to%20find%20true%20last%20login%5Clast%20activity%20for%20on%20premise%20AD%2C%20Azure%20AD%2C%20and%20o365%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1364795%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F647216%22%20target%3D%22_blank%22%3E%40LeeMed%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-cpp%22%3E%3CCODE%3E%2F%2F%20%20Also%20for%20Logon%20and%20Logoff%0A%20%20SecurityEvent%0A%20%20%7C%20where%20TimeGenerated%20%26gt%3B%20startofday(ago(1d))%0A%20%20%7C%20where%20EventID%20in%20(4624%2C%204625)%0A%20%20%7C%20where%20AccountType%20%3D%3D%20'User'%20%0A%20%20%7C%20summarize%20StartTime%20%3D%20min(TimeGenerated)%2C%20EndTimec%20%3D%20max(TimeGenerated)%2C%20count()%20by%20LogonTypeName%2C%20Account%2C%20Activity%0A%20%20%7C%20extend%20timestamp%20%3D%20StartTime%0A%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHowever%20want%20I%20think%20you%20need%20is%20something%20like%20this%20(assuming%20you%20have%20both%20the%20%3CSTRONG%3ESigninLogs%3C%2FSTRONG%3E%20and%20%3CSTRONG%3ESecurityEvent%3C%2FSTRONG%3E%20tables%20and%20data)%3F%26nbsp%3B%20Feel%20free%20to%20modify%20as%20you%20require.%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-cpp%22%3E%3CCODE%3Eunion%20isfuzzy%3Dtrue%20%0A(%0A%20%20SigninLogs%0A%20%20%7C%20where%20TimeGenerated%20%20%26gt%3B%20startofday(ago(1d))%0A%20%20%7C%20summarize%20LastCall%20%3D%20max(TimeGenerated)%20by%20Account%20%3D%20UserDisplayName%0A%20%20%7C%20where%20LastCall%20%26lt%3B%20ago(1h)%0A%20%20%7C%20order%20by%20LastCall%20asc%0A%20%20%7C%20project%20Account%20%2C%20LastCall%20%2C%20Table%20%3D%20%22AD%22%0A)%2C%0A(%0A%20%20SecurityEvent%0A%20%20%7C%20where%20TimeGenerated%20%26gt%3B%20startofday(ago(1d))%0A%20%20%7C%20where%20EventID%20in%20(4624)%20%2F%2F%2C%204625)%0A%20%20%7C%20where%20AccountType%20%3D%3D%20'User'%20%0A%20%20%7C%20summarize%20StartTime%20%3D%20min(TimeGenerated)%2C%20LastCall%20%3D%20max(TimeGenerated)%2C%20count()%20by%20LogonTypeName%2C%20Account%2C%20Activity%0A%20%20%7C%20where%20LastCall%20%26lt%3B%20ago%20(1h)%0A%20%20%7C%20project%20Account%20%2C%20LastCall%20%2C%20Table%20%3D%20%22AAD%22%0A)%20%0A%7C%20summarize%20by%20Account%2C%20LastCall%2C%20Table%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Regular Visitor

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 last login greater then x days.

 

 

 

 

 

 

5 Replies

@LeeMed 

 

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 

 

@LeeMed 

 

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?  :) 

@LeeMed 

 

//  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 = StartTime

 

However 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 desc

 You 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