Unifi_SiteManager_Hosts_CL
| where TimeGenerated > ago(30d)
| mv-expand member = UserData.consoleGroupMembers
| summarize MembersByDay = make_set(tostring(member.mac)) by bin(TimeGenerated, 1d), Id, HostName = tostring(ReportedState.name)
| extend MemberCount = array_length(MembersByDay)
| order by Id, TimeGenerated asc
| extend Prev = prev(MembersByDay), PrevId = prev(Id)
| where Id == PrevId and array_length(set_difference(MembersByDay, coalesce(Prev, dynamic([])))) > 0
| project Date=TimeGenerated, HostName, ['New members'] = set_difference(MembersByDay, coalesce(Prev, dynamic([]))), MemberCount
description: |
Tracks members of `userData.consoleGroupMembers` over time. Additions indicate new admin access; removals indicate revoked access. Both warrant review - additions are an account-creation signal, removals can be a sign of attacker cleanup.
id: 8e22eb19-51df-37f7-468f-9d112fff9098
version: 1.0.0
tactics:
- Persistence
name: 'UniFi Site Manager: Console group membership churn'
OriginalUri: https://github.com/Azure/Azure-Sentinel/blob/master/Solutions/UniFi Site Manager (CCF)/Hunting Queries/UniFiCloudConsoleGroupChurn.yaml
kind: HuntingQuery
query: |
Unifi_SiteManager_Hosts_CL
| where TimeGenerated > ago(30d)
| mv-expand member = UserData.consoleGroupMembers
| summarize MembersByDay = make_set(tostring(member.mac)) by bin(TimeGenerated, 1d), Id, HostName = tostring(ReportedState.name)
| extend MemberCount = array_length(MembersByDay)
| order by Id, TimeGenerated asc
| extend Prev = prev(MembersByDay), PrevId = prev(Id)
| where Id == PrevId and array_length(set_difference(MembersByDay, coalesce(Prev, dynamic([])))) > 0
| project Date=TimeGenerated, HostName, ['New members'] = set_difference(MembersByDay, coalesce(Prev, dynamic([]))), MemberCount
relevantTechniques:
- T1098
requiredDataConnectors:
- dataTypes:
- Unifi_SiteManager_Hosts_CL
connectorId: UniFiSiteManagerConnectorDefinition
entityMappings:
- fieldMappings:
- columnName: HostName
identifier: HostName
entityType: Host
