KQL Search

  let LocalSID = "S-1-5-32-5[0-9][0-9]$";
  let GroupSID = "S-1-5-21-[0-9]*-[0-9]*-[0-9]*-5[0-9][0-9]$|S-1-5-21-[0-9]*-[0-9]*-[0-9]*-1102$|S-1-5-21-[0-9]*-[0-9]*-[0-9]*-1103$";
  let timeframe = 8d;
  let p_Accounts = SecurityEvent
  | where TimeGenerated > ago(timeframe)
  | where EventID in ("4728", "4732", "4756") and AccountType == "User" and MemberName == "-"
  // Exclude Remote Desktop Users group: S-1-5-32-555 and IIS Users group S-1-5-32-568
  | where TargetSid !in ("S-1-5-32-555", "S-1-5-32-568")
  | where TargetSid matches regex LocalSID or TargetSid matches regex GroupSID
  | summarize by DomainSlashAccount = tolower(SubjectAccount), NtDomain = SubjectDomainName,
  AccountAtDomain = tolower(strcat(SubjectUserName,"@",SubjectDomainName)), AccountName = tolower(SubjectUserName);
  // Build custom high value account list
  let cust_Accounts = datatable(Account:string, NtDomain:string, Domain:string)[
  "john", "Contoso", "contoso.com",  "greg", "Contoso", "contoso.com",  "larry", "Domain", "contoso.com"];
  let c_Accounts = cust_Accounts
  | extend AccountAtDomain = tolower(strcat(Account,"@",Domain)), AccountName = tolower(Account),
  DomainSlashAccount = tolower(strcat(NtDomain,"\\",Account));
  let AccountFormat = p_Accounts | union c_Accounts | project AccountName, AccountAtDomain, DomainSlashAccount;
  // Normalize activity from diverse sources into common schema using a function
  let activity = view (a_StartTime:datetime, a_EndTime:datetime) {
  (union isfuzzy=true  
  (AccountFormat | join kind=inner
  (SigninLogs
  | where TimeGenerated >= a_StartTime and TimeGenerated <= a_EndTime
  | extend AccountName = tolower(split(UserPrincipalName, "@")[0]), WinSecEventDomain = "-"
  | project-rename EventType = strcat(OperationName, "-", ResultType, "-", ResultDescription), ServiceOrSystem = AppDisplayName, ClientIP = IPAddress)
  on AccountName),
  (AccountFormat | join kind=inner
  (OfficeActivity
  | where TimeGenerated >= a_StartTime and TimeGenerated <= a_EndTime
  | extend AccountName = tolower(split(UserId, "@")[0]), WinSecEventDomain = "-"
  | project-rename EventType = strcat(Operation, "-", ResultStatus), ServiceOrSystem = OfficeWorkload)
  on AccountName),
  (AccountFormat | join kind=inner
  (SecurityEvent
  | where TimeGenerated >= a_StartTime and TimeGenerated <= a_EndTime
  | where EventID in (4624, 4625) 
  | extend ClientIP = "-"
  | extend AccountName = tolower(split(Account,"\\")[1]), Domain = tolower(split(Account,"\\")[0])
  | project-rename EventType = Activity, ServiceOrSystem = Computer, WinSecEventDomain = Domain)
  on AccountName),
  (AccountFormat | join kind=inner
  (W3CIISLog
  | where TimeGenerated >= a_StartTime and TimeGenerated <= a_EndTime
  | where csUserName != "-" and isnotempty(csUserName)
  | extend AccountName = tolower(csUserName), WinSecEventDomain = "-"
  | project-rename EventType = csMethod, ServiceOrSystem = sSiteName, ClientIP = cIP)
  on AccountName),
  (AccountFormat | join kind=inner
  (W3CIISLog
  | where TimeGenerated >= a_StartTime and TimeGenerated <= a_EndTime
  | where csUserName != "-" and isnotempty(csUserName)
  | extend AccountAtDomain = tolower(csUserName), WinSecEventDomain = "-"
  | project-rename EventType = csMethod, ServiceOrSystem = sSiteName, ClientIP = cIP)
  on AccountAtDomain));
  };
  // Rare activity today versus prior week
  let LastDay = startofday(ago(1d));
  let PrevDay = endofday(ago(2d));
  let Prev7Day = startofday(ago(8d));
  let ra_LastDay = activity(LastDay, now())
  | summarize ra_StartTime = min(TimeGenerated), ra_EndTime = max(TimeGenerated),
  ra_Count = count() by Type, AccountName, EventType, ClientIP, ServiceOrSystem, WinSecEventDomain;
  let a_7day = activity(Prev7Day, PrevDay)
  | summarize ha_Count = count() by Type, AccountName, EventType, ClientIP, ServiceOrSystem, WinSecEventDomain;
  let ra_Today = ra_LastDay | join kind=leftanti (a_7day) on Type, AccountName, ServiceOrSystem
  | extend RareServiceOrSystem = ServiceOrSystem;
  // Retrieve related activity as context
  let a_Related =
  (union isfuzzy=true
  (// Make sure we at least publish the unusual activity we identified above - even if no related context activity is found in the subsequent union
  ra_Today),
  // Remaining elements of the union look for related activity
  (ra_Today | join kind=inner
  (OfficeActivity
  | where TimeGenerated > LastDay
  | summarize rel_StartTime = min(TimeGenerated), rel_EndTime = max(TimeGenerated), rel_ServiceOrSystemCount = dcount(OfficeWorkload),
  rel_ServiceOrSystemSet = makeset(OfficeWorkload), rel_ClientIPSet = makeset(ClientIP),
  rel_Count = count() by AccountName = tolower(UserId), rel_EventType = Operation, Type
  ) on AccountName),
  (ra_Today | join kind=inner
  (SecurityEvent | where TimeGenerated > LastDay
  | where EventID in (4624, 4625)
  | where AccountType == "User"
  | summarize rel_StartTime = min(TimeGenerated), rel_EndTime = max(TimeGenerated), rel_ServiceOrSystemCount = dcount(Computer),
  rel_ServiceOrSystemSet = makeset(Computer), rel_ClientIPSet = makeset("-"),
  rel_Count = count() by DomainSlashAccount = tolower(Account), rel_EventType = Activity, Type
  ) on DomainSlashAccount),
  (ra_Today | join kind=inner
  (Event | where TimeGenerated > LastDay
  // 7045: A service was installed in the system
  | where EventID == 7045
  | summarize rel_StartTime = min(TimeGenerated), rel_EndTime = max(TimeGenerated), rel_ServiceOrSystemCount = dcount(Computer),
  rel_ServiceOrSystemSet = makeset(Computer), rel_ClientIPSet = makeset("-"),
  rel_Count = count() by DomainSlashAccount = tolower(UserName), rel_EventType = strcat(EventID, "-", tostring(split(RenderedDescription,".")[0])), Type
  ) on DomainSlashAccount),
  (ra_Today | join kind=inner
  (SecurityEvent | where TimeGenerated > LastDay
  // 4720: Account created, 4726: Account deleted
  | where EventID in (4720,4726)
  | summarize rel_StartTime = min(TimeGenerated), rel_EndTime = max(TimeGenerated), rel_ServiceOrSystemCount = dcount(UserPrincipalName),
  rel_ServiceOrSystemSet = makeset(UserPrincipalName), rel_ClientIPSet = makeset("-"),
  rel_Count = count() by DomainSlashAccount = tolower(Account), rel_EventType = Activity, Type
  ) on DomainSlashAccount),
  (ra_Today | join kind=inner
  (SigninLogs | where TimeGenerated > LastDay
  | extend RemoteHost = tolower(tostring(parsejson(DeviceDetail.["displayName"])))
  | extend OS = DeviceDetail.operatingSystem, Browser = DeviceDetail.browser, StatusCode = tostring(Status.errorCode),
  StatusDetails = tostring(Status.additionalDetails), State = tostring(LocationDetails.state)
  | summarize rel_StartTime = min(TimeGenerated), rel_EndTime = max(TimeGenerated), a_RelatedRemoteHostSet = makeset(RemoteHost),
  rel_ServiceOrSystemSet = makeset(AppDisplayName), rel_ServiceOrSystemCount = dcount(AppDisplayName), rel_ClientIPSet = makeset(IPAddress),
  rel_StateSet = makeset(State),
  rel_Count = count() by AccountAtDomain = tolower(UserPrincipalName), rel_EventType = iff(isnotempty(ResultDescription), ResultDescription, StatusDetails), Type
  ) on AccountAtDomain),
  (ra_Today | join kind=inner
  (SecurityAlert | where TimeGenerated > LastDay
  | extend ExtProps=parsejson(ExtendedProperties)
  | extend AccountName = tostring(ExtProps.["user name"])
  | summarize rel_StartTime = min(TimeGenerated), rel_EndTime = max(TimeGenerated), rel_ServiceOrSystemCount = dcount(AlertType),
  rel_ServiceOrSystemSet = makeset(AlertType), 
  rel_Count = count() by DomainSlashAccount = tolower(AccountName), rel_EventType = ProductName, Type
  ) on DomainSlashAccount)
  );
  a_Related
  | project Type, RareActivtyStartTimeUtc = ra_StartTime, RareActivityEndTimeUtc = ra_EndTime, RareActivityCount = ra_Count,
  AccountName, WinSecEventDomain, EventType, RareServiceOrSystem, RelatedActivityStartTimeUtc = rel_StartTime,
  RelatedActivityEndTimeUtc = rel_EndTime, RelatedActivityEventType = rel_EventType, RelatedActivityClientIPSet = rel_ClientIPSet,
  RelatedActivityServiceOrSystemCount = rel_ServiceOrSystemCount, RelatedActivityServiceOrSystemSet = rel_ServiceOrSystemSet, RelatedActivityCount = rel_Count
  | extend timestamp = RareActivtyStartTimeUtc, AccountCustomEntity = AccountName