Skip to content

Instantly share code, notes, and snippets.

Created September 6, 2020 08:14
Show Gist options
  • Save alexverboon/ad59c3b0f8bbace142bd3acaac5b6ad9 to your computer and use it in GitHub Desktop.
Save alexverboon/ad59c3b0f8bbace142bd3acaac5b6ad9 to your computer and use it in GitHub Desktop.
Hunting for local group membership changes
let ADAZUsers = IdentityInfo
| extend DirectoryDomain = AccountDomain
| extend DirectoryAccount = AccountName
| distinct DirectoryDomain , DirectoryAccount , OnPremSid , CloudSid, AccountUpn, GivenName, Surname;
// check for any new created or modified local accounts
let NewUsers = DeviceEvents
| where ActionType contains "UserAccountCreated" // or ActionType contains "UserAccountModified"
| extend lUserAdded = AccountName
| extend NewUserSID = AccountSid
| extend laccountdomain = AccountDomain
| distinct NewUserSID, lUserAdded,laccountdomain;
// Check for any local group changes and enrich the data with the account name obtained from the previous query
| where ActionType == 'UserAccountAddedToLocalGroup'
| extend AddedAccountSID = tostring(parse_json(AdditionalFields).MemberSid)
| extend LocalGroup = AccountName
| extend LocalGroupSID = AccountSid
| extend Actor = trim(@"[^\w]+",InitiatingProcessAccountName)
| join kind= leftouter (NewUsers)
on $left.AddedAccountSID == $right.NewUserSID
| project Timestamp, DeviceName, LocalGroup,LocalGroupSID, AddedAccountSID, lUserAdded , Actor, ActionType , laccountdomain
// limit to local administrators group
// | where LocalGroupSID contains "S-1-5-32-544"
| join kind= leftouter (ADAZUsers)
on $left.AddedAccountSID == $right.OnPremSid
| extend UserAdded = iff(isnotempty(lUserAdded),strcat(laccountdomain,"\\", lUserAdded), strcat(DirectoryDomain,"\\", DirectoryAccount))
| project Timestamp, DeviceName, LocalGroup,LocalGroupSID, AddedAccountSID, UserAdded , Actor, ActionType
| where DeviceName !contains Actor
// Provide details on actors that added users
// | summarize count() by Actor
// | join ADAZUsers
// on $left.Actor == $right.DirectoryAccount
// | render piechart
Copy link

mboppe07 commented Aug 31, 2021

Thanks for the posting this.
Really helpful!

When I try to save the query as a custome detection rule it errors out with message saying "The query does not return the following columns that are required to create a detection rule:"
ReportID has a redcross

Any idea on how we can include the ReportID

Copy link

PehDeh commented Dec 1, 2023

After Line 1 i added this line because we have Account not Synced to EntraID and so it would show guestaccounts
|where isnotempty(OnPremSid)

As not Synced Accounts have no OnPremSid they will resolve randomly to these:
IdentityInfo |where isempty(OnPremSid)

Copy link

PehDeh commented Dec 1, 2023

Also i didn´t get any local users resolved as it seems they changed the Additional Fields and the AccountSid in Line 15

| extend AddedAccountSID = tostring(parse_json(AdditionalFields).MemberSid)
| extend LocalGroupSID = tostring(parse_json(AdditionalFields).GroupSid)

and line 17
| extend LocalGroupSID = AccountSid
| extend AddedAccountSID = AccountSid

And also great thanks from me for providing this nice Query

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment