Can someone take a look at my queries and see if they can find any errors please?
My original query below provides as output all disabled accounts for the previous month and includes the admin who took the action, the disabled user along with their information and the time the account was disabled.
//All User account that were disabled the previous month
let lastmonth = getmonth(datetime(now)) - 1;
let year = getyear(datetime(now));
let monthEnd = endofmonth(datetime(now), -1);
SecurityEvent
| where TimeGenerated >= make_datetime(year, lastmonth, 01) and TimeGenerated <= monthEnd
| extend Disabled_EST = datetime_utc_to_local(TimeGenerated, "US/Eastern")
| where EventID == "4725"
| where AccountType == "User"
|join IdentityInfo on $left.TargetSid== $right.AccountSID
| summarize by TimeGenerated, Disabled_EST, Account, Activity, MemberName, TargetAccount, Computer, AccountDisplayName, GivenName, Surname
| order by Disabled_EST asc
Now the auditors want to also see when the disabled account was last signed-in so I need to add another column to the above, however I could not find any values from the IdentityInfo, the SecurityEvent and the SigninLogs tables that can be used to join the tables.
So what I did was to start from scratch and use the slit function to create a field that I could use as a key for the join operation. The query below works as expected although I believe the builtin datetime_utc_to_local() function no longer works? As I'm still getting the UTC time it appears.
SigninLogs
|extend LastLoginTimeEST = datetime_utc_to_local(TimeGenerated, "US/Eastern")
| extend NetAccount_ = tostring(split(AlternateSignInName, "@")[0])
| project-away AlternateSignInName
| summarize max(LastLoginTimeEST) by NetAccount_, OperationName, AuthenticationRequirement
So then the original query was modified to include the above query. Now I'm able to get as output the required columns and the last sign-in of the user. However, as mentioned earlier, the results appear to be incorrect as the date/time in this query does not match the output of the standalone SigninLogs query above.
//Working but incorrect results shown in lastLogin_EST column
let lastmonth = getmonth(datetime(now)) - 1;
let year = getyear(datetime(now));
let monthEnd = endofmonth(datetime(now), -1);
let SecurityEvents =
SecurityEvent
| where TimeGenerated >= make_datetime(year, lastmonth, 01) and TimeGenerated <= monthEnd
//| extend EST_Disabled = datetime_utc_to_local(TimeGenerated, "US/Eastern")
| where EventID == "4725"
| where AccountType == "User"
| join kind=leftouter (IdentityInfo | project AccountName, AccountDisplayName, GivenName, Surname) on $left.TargetUserName == $right.AccountName;
let LastSigninLogs =
SigninLogs
//| extend LastLogin_EST = datetime_utc_to_local(TimeGenerated, "US/Eastern")
| extend IdName=split(AlternateSignInName,"@", 0)
| extend NetAccount_ = tostring(IdName[0])
| project-away IdName
| summarize LastLogin_EST = max(TimeGenerated) by NetAccount_, OperationName, AuthenticationRequirement;
SecurityEvents
//|where Surname == "xyz" //use a lastname to reduce output for verification
| join kind=leftouter LastSigninLogs on $left.TargetUserName == $right.NetAccount_
| summarize max(TimeGenerated)by TimeGenerated,Account, Activity, TargetAccount, Computer, AccountDisplayName, GivenName, Surname, LastLogin_EST, OperationName, AuthenticationRequirement
//|extend DisabledEST= datetime_utc_to_local(max_TimeGenerated, "US/Eastern")
Can someone take a look at help me find the bug or is this actually correct?
