Forum Discussion

LeeMed's avatar
LeeMed
Copper Contributor
May 04, 2020

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

 

 

 

 

 

 

Azure Log Analytics
azure monitor
  • CliveWatson's avatar
    CliveWatson
    Icon for Microsoft rankMicrosoft
    May 04, 2020

    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

     

    • CliveWatson's avatar
      CliveWatson
      Icon for Microsoft rankMicrosoft
      May 06, 2020

      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?  🙂 

      • CliveWatson's avatar
        CliveWatson
        Icon for Microsoft rankMicrosoft
        May 06, 2020

        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

         

Resources