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

 

 

 

 

 

 

3 Replies
Highlighted

@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 

 

Highlighted

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

Highlighted

@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