May 04 2020
11:22 AM
- last edited on
Apr 08 2022
10:25 AM
by
TechCommunityAP
May 04 2020
11:22 AM
- last edited on
Apr 08 2022
10:25 AM
by
TechCommunityAP
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.
May 04 2020 11:36 AM
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
May 06 2020 10:06 AM
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? :)
May 06 2020 11:23 AM
// 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
Sep 02 2021 12:29 PM
Sep 03 2021 12:39 AM
@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