KQL Search

This KQL query is designed to filter and summarize information about service accounts from the IdentityInfo table. Here's a breakdown in simpler terms:

1. Time Filter: It selects records from the last 30 days (Timestamp > ago(30d)).

2. Type Filter: It focuses specifically on entries where the Type is "ServiceAccount".

3. Data Extension:

   • It creates a new column called "Service On-Prem Sid" using the existing OnPremObjectId field.
   • It creates another column called "Service Principal Name". This name is constructed differently depending on the environment:
     • If the environment is "OnPremises", it combines AccountName and AccountDomain, removing any trailing "$".
     • Otherwise, it uses AccountUpn.

4. Account Type Exclusion: It excludes accounts identified as 'WorkstationTrustAccount' by checking the UserAccountControl field.

5. Service Principal Exclusion: It excludes entries where the ChangeSource is "System-UserPersistence" and CloudSid is empty, which typically represents service principals.

6. Summarization: Finally, it summarizes the data to keep only the most recent entry (arg_max(Timestamp,*)) for each unique combination of AccountObjectId, CloudSid, and "Service On-Prem Sid".

In essence, this query filters and processes service account data to provide a concise summary of the most recent relevant entries, excluding certain types of accounts and service principals.