SkyKick response: choose the records you collect

Customer telemetry refresh / October 29, 2026

Choose your source

SOURCE GUIDE / SKYKICK CUSTOMER REFRESH / 29 OCTOBER 2026

Start here: identify your customer tenant, source, UTC start, recorded end, and retention. Verify one known service-principal sign-in and one directory change against the original source. Missing records are not a negative finding. These are read-only hunt starters; no tenant or vendor engine execution is claimed.

1. Microsoft Sentinel or Log Analytics: queries/skykick-hunt-starters.kql has five blocks: 01A, 01B, 02, 03, and 04. Requires AADServicePrincipalSignInLogs and AuditLogs ingested through configured Microsoft Entra diagnostic settings, the relevant categories, workspace access, and any applicable Entra/Azure licensing and ingestion charges. The workspace's TenantId is not the customer's AADTenantId. Set TenantFilter to the customer Entra tenant GUID; an empty filter searches every visible tenant. KQL here belongs in Sentinel/Log Analytics, not automatically Defender Advanced Hunting. Set the portal picker to include the full explicit query interval. Table existence is not proof of continuous collection.

2. Splunk, LogScale/Falcon Next-Gen SIEM, and Sumo Logic: the new graph-directoryaudit files parse Microsoft's documented Graph v1.0 directoryAudit JSON directly. They are audit-only routes. They do not supply or infer service-principal sign-ins. GS/GL/GU01 selects five broad directory categories; GS/GL/GU02 selects a listed application as the recorded initiating application. The actor route is not a target application search, self-assignment finding, or maliciousness verdict. Missing actor fields remain in the broad category route. User actors, empty target arrays, failed results, and unknown results are not filtered out by that broad route.

Use real retained Graph directoryAudit exports already collected, or configure an authorized Microsoft Graph collection workflow. GET https://graph.microsoft.com/v1.0/auditLogs/directoryAudits supports AuditLog.Read.All as the least privileged API permission; delegated access also needs a supported directory role. Follow @odata.nextLink until all pages are retrieved. Export the individual objects from value[] as UTF-8 JSON lines. Preserve every property, nested initiatedBy, targetResources, modifiedProperties, original event ID, collection time, and customer identity in protected collection metadata. Never save bearer tokens in event content. This package has no collector and does not create historical data.

Each SIEM event must be one original directoryAudit object, not a wrapper, value[] response, Event Hub envelope, Azure Monitor uppercase-schema row, or Microsoft 365 Management Activity record. If your integration supplies another event format, use its documented parser and corresponding query. Do not silently treat that envelope as Graph JSON. These searches parse real Microsoft keys; their few output aliases are direct query-local extractions, not a new event schema.

Splunk: ingest the JSON lines using your managed file/HEC route; configure event boundaries and _time from activityDateTime, retaining _raw. Replace both index and sourcetype with the verified, customer-scoped source. SPL spath explicitly extracts each key. Check extraction limits on long events, field collisions with automatic extractions, and inspect the original record for every result. The shipped block is SPL, not SPL2.

LogScale: ingest the same JSON objects into an authorized repository or view with a JSON parser and @timestamp parsed from activityDateTime. Select that exact customer source in the UI before running GL01 or GL02. parseJson(field=@raw) extracts the original keys at query time; no dc_* normalization is used. Retain @raw and view complete events. These blocks deliberately omit select() so that a default projection does not hide nested evidence; verify result/export limits for a full timeline. A Falcon endpoint sensor alone does not collect this cloud feed.

Sumo: ingest those objects using an existing documented Local File or HTTP Logs and Metrics Source route. Replace _sourceCategory with the configured customer-scoped source category; this literal is a selector, not a built-in feed or table. Configure one JSON object per message and event time from activityDateTime. Explicit json ... nodrop extraction preserves missing keys. Inspect the Messages view and raw record, including all nested targets. The HTTP transport alone is not telemetry: Graph records must actually be collected, paginated, and delivered first. No custom Deception Check event producer is required or implied.

For all three Graph routes, verify customer attribution from the configured source and protected export manifest. directoryAudit does not provide a universal tenantId field. Never substitute a workspace, repository, or organization identifier for a source tenant. Select October 13, 2026 00:00 UTC to a recorded end for the historical indicator-led investigation; extend to October 1 only where retained history permits. When old exports are reingested, ensure event-time parsing is correct and widen the picker if ingest time and event time differ. Treat the seven IDs and three IPs as the October 19 advisory snapshot, not a refreshed current indicator list. Confirm current vendor guidance before action.

3. Sophos: queries/skykick-sophos-searches.json contains six query strings for Fusion Advanced Search (C00-C04, N01); it is not a policy import. Configure the Microsoft Entra Activity Reports integration with ServicePrincipalSignInLogs and AuditLogs when using Event Hubs. For Graph-based integration, verify service-principal login collection explicitly; interactive sign-ins and directory audits are defaults in the documented integration setup. Confirm current Sophos entitlement, integration health, customer scope, and source categories on a known event. auth/cloudaudit are documented Sophos logical schemas, not a package-defined feed. C00 counts alone do not prove the right auth source. C02-C04 inspect original text; GUID roles require manual confirmation. Sophos documents a 20-day raw access limit: older raw-dependent searches may be unavailable even when some normalized data remains. Use retained exports when needed.

Sophos Data Lake SQL: skykick-sophos-m365-datalake.sql has L00-L02 over the documented xdr_identity_o365 table from the Microsoft 365 Management Activity integration. This is a distinct audit feed, not dedicated service-principal sign-in history. Verify organization_id/customer scope and query_name partitions. Endpoint SQL: skykick-sophos-endpoint.sql has E01-E03 for authorized Windows Live Discover endpoints. Network/process journals provide supporting local history; sophos_pid is not the Windows numeric PID. Endpoint snapshots are not a tenant-wide cloud ledger. Run the exact dialect in its named editor.

4. Sigma: two experimental Azure auditlogs rules keep standard source fields and Deception Check in author. Nested InitiatedBy.app field extraction needs a verified backend mapping. App-initiated does not mean self-assigned, successful, or unauthorized. Preserve outcomes. YAML parsing, source fixtures, and successful backend conversion are different checks; no backend or tenant execution is claimed for this refresh.

5. ADVANCED: retained October 19 dc_* mapping, normalized SPL/CQL, full portable pseudocode, and explanation. This is engineering reference for teams deliberately maintaining that mapping. It is not the primary customer route. Do not deploy these as stock vendor-field searches.

Interpret findings: keep listed app OR reported address paths independent; retain IPv6, failed, and unknown outcomes. Examine credential, owner, role, and consent changes across all applications. Follow verified identities and key IDs into available Microsoft Graph and Microsoft 365 resource records. Compare event time with the specific customer's containment time. A recorded attempt is not proof of success; a permission change is not proof of data access. No validated malicious file or memory sample supports a YARA signature here.

Primary references (checked 29 October 2026 for new JSON routes; prior incident snapshot retained):
https://learn.microsoft.com/en-us/graph/api/resources/directoryaudit?view=graph-rest-1.0
https://learn.microsoft.com/en-us/graph/api/directoryaudit-list?view=graph-rest-1.0
https://learn.microsoft.com/en-us/azure/azure-monitor/reference/tables/aadserviceprincipalsigninlogs
https://learn.microsoft.com/en-us/azure/azure-monitor/reference/tables/auditlogs
https://learn.microsoft.com/en-us/entra/identity/monitoring-health/howto-configure-diagnostic-settings
https://help.splunk.com/en/splunk-enterprise/spl-search-reference/9.4/search-commands/spath
https://library.humio.com/crowdstrike-query-language/functions-parsejson.html
https://library.humio.com/crowdstrike-query-language/functions-in.html
https://help.sumologic.com/docs/search/search-query-language/parse-operators/parse-json-formatted-logs/
https://help.sumologic.com/docs/send-data/installed-collectors/sources/local-file-source/
https://help.sumologic.com/docs/send-data/hosted-collectors/http-source/logs-metrics/
https://docs.sophos.com/fusion/customer/help/en-us/ManageYourProducts/XDRSecOps/Integrations/Products/MicrosoftEntra/ActivityReports/Setup/index.html
https://docs.sophos.com/fusion/customer/help/en-us/ManageYourProducts/XDRSecOps/Search/DataLakeSearch/QueryLanguage/index.html
https://docs.sophos.com/fusion/customer/help/en-us/ManageYourProducts/XDRSecOps/Integrations/Products/Microsoft365/ManagementAPI/index.html
https://docs.sophos.com/central/References/schemas/schemas/xdr_identity_o365_schema_docs.json
https://docs.sophos.com/central/References/schemas/schemas/endpoint_schema.json
https://www.connectwise.com/company/trust/advisories

Native queries and standard rules

Replace the named source selector with the source you actually collect. Review prerequisites before running a hunt.

skykick-entra-app-credential-events.yml

Download source

title: Entra ID Credential Related Audit Event with Application Actor
id: c90ce13e-ae7b-463a-a89f-4054cf18bc70
status: experimental
description: >-
  Review lead for credential-related audit events with a nonempty app actor ID.
  Includes removal/rotation and all results; does not prove a credential was added
  or abuse occurred. Read skykick-base-logic.txt for mapping and coverage limits.
references:
  - https://sigmahq.io/sigma-specification/specification/sigma-appendix-taxonomy.html
  - https://learn.microsoft.com/en-us/azure/azure-monitor/reference/tables/auditlogs
author: Deception Check
date: '2026-10-19'
logsource:
    product: azure
    service: auditlogs
    definition: >-
      Microsoft AuditLogs spelling; conversion must extract nested InitiatedBy.app
      fields as strings. Dotted field names require a validated backend mapping.
detection:
    selection_operation:
        OperationName|contains:
            - 'Certificates and secrets'
            - 'service principal credentials'
    selection_actor:
        - InitiatedBy.app.appId: '?*'
        - InitiatedBy.app.servicePrincipalId: '?*'
    condition: selection_operation and selection_actor
fields: [TimeGenerated, AADTenantId, Id, CorrelationId, OperationName, Result,
    ResultReason, InitiatedBy, TargetResources]
falsepositives:
    - Approved application credential rotation by CI/CD or infrastructure automation
    - Authorized vendor onboarding, credential removal, or remediation
    - System-initiated activity represented by an application identity
level: medium
tags: [attack.persistence, attack.t1098.001]

skykick-entra-app-role-events.yml

Download source

title: Entra ID Role Addition Related Audit Event with Application Actor
id: 7d6f9b4e-4976-41d6-a12e-f1b7f90c4813
status: experimental
description: >-
  Review lead for role-addition-related audit events with a nonempty app actor ID.
  Includes requests, cancellations, and all results; does not prove self-assignment
  or compromise. Read skykick-base-logic.txt for mapping and coverage limits.
references:
  - https://sigmahq.io/sigma-specification/specification/sigma-appendix-taxonomy.html
  - https://learn.microsoft.com/en-us/azure/azure-monitor/reference/tables/auditlogs
author: Deception Check
date: '2026-10-19'
logsource:
    product: azure
    service: auditlogs
    definition: >-
      Microsoft AuditLogs spelling; conversion must extract nested InitiatedBy.app
      fields as strings. Dotted field names require a validated backend mapping.
detection:
    selection_operation:
        OperationName|contains: 'member to role'
    selection_actor:
        - InitiatedBy.app.appId: '?*'
        - InitiatedBy.app.servicePrincipalId: '?*'
    condition: selection_operation and selection_actor
fields: [TimeGenerated, AADTenantId, Id, CorrelationId, OperationName, Result,
    ResultReason, InitiatedBy, TargetResources]
falsepositives:
    - Authorized vendor onboarding and application automation that assigns roles
    - Legitimate PIM requests, eligibility changes, approvals, and cancellations
    - System-initiated activity represented by an application identity
level: medium
tags: [attack.persistence, attack.privilege_escalation, attack.t1098.003]

skykick-graph-directoryaudit.cql

Download source

// DECEPTION CHECK / SKYKICK / 29 OCTOBER 2026
// Direct Microsoft Graph v1.0 directoryAudit JSON route. Hunt starters, not deployed alerts.
// Read SOURCE-GUIDE.txt first. Run one block in the named editor.
// Requires existing real directoryAudit records, one object per event, not an entire Graph response.
// No dc_* mapping. This route covers directory changes, not service-principal sign-ins.
// Choose an explicitly scoped customer source and UTC time range. Preserve raw events and all outcomes.
// Placeholder index/sourceCategory values are local source selectors, not supplied telemetry.
// No SIEM engine or tenant execution was performed. Test a known source event before use.

// GL01 / Broad access-change review; all actors and results.
parseJson(field=@raw)
| in(field=category, values=["ApplicationManagement", "RoleManagement", "Policy", "UserManagement", "Audit"], ignoreCase=true)

// GL02 / Listed application is the recorded actor; all operations and results.
parseJson(field=@raw)
| in(field="initiatedBy.app.appId", values=[
    "506e08f9-6e9a-4da4-942b-a099a03644ae",
    "2399d300-6fca-4e6a-a686-399fefe75b83",
    "24205314-2b89-4e07-a2a4-579e898a5640",
    "b78f6a77-d4e5-46fb-87e5-d1648bd02519",
    "0de14c77-b9b0-4137-9360-34878a929827",
    "412dcf96-176d-4b3a-a1b0-4981ad66971e",
    "b2b55f5b-7758-4719-8d8f-88ae172e6bdb"], ignoreCase=true)

skykick-graph-directoryaudit.spl

Download source

index=REPLACE_WITH_CUSTOMER_INDEX sourcetype=REPLACE_WITH_GRAPH_DIRECTORYAUDIT_SOURCE
``` GS01 / Broad access-change review; all actors and results. Read SOURCE-GUIDE.txt. ```
| spath input=_raw path=id output=id
| spath input=_raw path=activityDateTime output=activityDateTime
| spath input=_raw path=activityDisplayName output=activityDisplayName
| spath input=_raw path=category output=category
| spath input=_raw path=initiatedBy.app.appId output=actorAppId
| spath input=_raw path=result output=result
| where in(lower(category), "applicationmanagement", "rolemanagement", "policy", "usermanagement", "audit")
| fields _time, id, activityDateTime, activityDisplayName, category, actorAppId, result, _raw

index=REPLACE_WITH_CUSTOMER_INDEX sourcetype=REPLACE_WITH_GRAPH_DIRECTORYAUDIT_SOURCE
``` GS02 / Listed application is the recorded actor; all operations and results. ```
| spath input=_raw path=id output=id
| spath input=_raw path=activityDateTime output=activityDateTime
| spath input=_raw path=activityDisplayName output=activityDisplayName
| spath input=_raw path=category output=category
| spath input=_raw path=initiatedBy.app.appId output=actorAppId
| spath input=_raw path=result output=result
| where in(lower(actorAppId),
    "506e08f9-6e9a-4da4-942b-a099a03644ae",
    "2399d300-6fca-4e6a-a686-399fefe75b83",
    "24205314-2b89-4e07-a2a4-579e898a5640",
    "b78f6a77-d4e5-46fb-87e5-d1648bd02519",
    "0de14c77-b9b0-4137-9360-34878a929827",
    "412dcf96-176d-4b3a-a1b0-4981ad66971e",
    "b2b55f5b-7758-4719-8d8f-88ae172e6bdb")
| fields _time, id, activityDateTime, activityDisplayName, category, actorAppId, result, _raw

skykick-graph-directoryaudit.sumo

Download source

// DECEPTION CHECK / SKYKICK / 29 OCTOBER 2026
// Direct Microsoft Graph v1.0 directoryAudit JSON route. Hunt starters, not deployed alerts.
// Read SOURCE-GUIDE.txt first. Run one block in the named editor.
// Requires existing real directoryAudit records, one object per event, not an entire Graph response.
// No dc_* mapping. This route covers directory changes, not service-principal sign-ins.
// Choose an explicitly scoped customer source and UTC time range. Preserve raw events and all outcomes.
// Placeholder index/sourceCategory values are local source selectors, not supplied telemetry.
// No SIEM engine or tenant execution was performed. Test a known source event before use.

// GU01 / Broad access-change review; all actors and results.
_sourceCategory="REPLACE_WITH_CUSTOMER_GRAPH_DIRECTORYAUDIT_SOURCE"
| json "id", "activityDateTime", "activityDisplayName", "category", "initiatedBy.app.appId", "result"
  as eventId, activityDateTime, activityDisplayName, category, actorAppId, result nodrop
| where category in ("ApplicationManagement", "RoleManagement", "Policy", "UserManagement", "Audit")

// GU02 / Listed application is the recorded actor; all operations and results.
_sourceCategory="REPLACE_WITH_CUSTOMER_GRAPH_DIRECTORYAUDIT_SOURCE"
| json "id", "activityDateTime", "activityDisplayName", "category", "initiatedBy.app.appId", "result"
  as eventId, activityDateTime, activityDisplayName, category, actorAppId, result nodrop
| if(isNull(actorAppId), "", toLowerCase(actorAppId)) as actorAppId
| where actorAppId in (
    "506e08f9-6e9a-4da4-942b-a099a03644ae",
    "2399d300-6fca-4e6a-a686-399fefe75b83",
    "24205314-2b89-4e07-a2a4-579e898a5640",
    "b78f6a77-d4e5-46fb-87e5-d1648bd02519",
    "0de14c77-b9b0-4137-9360-34878a929827",
    "412dcf96-176d-4b3a-a1b0-4981ad66971e",
    "b2b55f5b-7758-4719-8d8f-88ae172e6bdb")

skykick-hunt-starters.kql

Download source

// DECEPTION CHECK / SKYKICK TENANT RESPONSE / 2026-10-19
// Read-only Log Analytics / Sentinel hunt starters, not deployed detections.
// Run ONE numbered block at a time. Read the Detection and Response Companion PDF first.
// Independent schema, offline Kusto.Language parser/binding, and fixture checks completed.
// No live-tenant execution or Kusto service/engine execution test.
// Required: AADServicePrincipalSignInLogs and AuditLogs ingested and accessible.
// Use the portal time selector to include the entire explicit UTC window.
// TenantFilter = "" intentionally searches ALL tenants visible in this workspace.
// For one tenant, insert its Entra tenant GUID, NOT the Log Analytics workspace ID.
// Default window starts October 13. Extend to October 1 where history permits.
// A zero result, missing table, or missing field is not a clearance verdict.

// 01A / Available service-principal records by tenant and UTC day.
// Missing days do not appear as zero rows. Check ingestion and retention separately.
let StartTime = datetime(2026-10-13T00:00:00Z);
let EndTime = now();
let TenantFilter = "";
AADServicePrincipalSignInLogs
| where TimeGenerated >= StartTime and TimeGenerated < EndTime
| where isempty(TenantFilter) or AADTenantId =~ TenantFilter
| summarize Records=count(), FirstRecord=min(TimeGenerated), LastRecord=max(TimeGenerated)
    by AADTenantId, DayUTC=bin(TimeGenerated, 1d)
| order by AADTenantId asc, DayUTC asc

// 01B / Available directory audit records. Run separately from 01A.
let StartTime = datetime(2026-10-13T00:00:00Z);
let EndTime = now();
let TenantFilter = "";
AuditLogs
| where TimeGenerated >= StartTime and TimeGenerated < EndTime
| where isempty(TenantFilter) or AADTenantId =~ TenantFilter
| summarize Records=count(), FirstRecord=min(TimeGenerated), LastRecord=max(TimeGenerated)
    by AADTenantId, DayUTC=bin(TimeGenerated, 1d)
| order by AADTenantId asc, DayUTC asc

// 02 / Listed applications OR reported source addresses. Preserve failures and IPv6.
// Public response indicators below are from ConnectWise, reviewed October 19.
let StartTime = datetime(2026-10-13T00:00:00Z);
let EndTime = now();
let TenantFilter = "";
let AppIds = dynamic([
    "506e08f9-6e9a-4da4-942b-a099a03644ae",
    "2399d300-6fca-4e6a-a686-399fefe75b83",
    "24205314-2b89-4e07-a2a4-579e898a5640",
    "b78f6a77-d4e5-46fb-87e5-d1648bd02519",
    "0de14c77-b9b0-4137-9360-34878a929827",
    "412dcf96-176d-4b3a-a1b0-4981ad66971e",
    "b2b55f5b-7758-4719-8d8f-88ae172e6bdb"
]);
let ReportedIPs = dynamic(["144.172.64.128", "144.172.64.99", "144.172.64.60"]);
AADServicePrincipalSignInLogs
| where TimeGenerated >= StartTime and TimeGenerated < EndTime
| where isempty(TenantFilter) or AADTenantId =~ TenantFilter
| extend ListedApp = AppId in~ (AppIds), ReportedIP = IPAddress in (ReportedIPs)
| where ListedApp or ReportedIP
| extend MatchBasis=case(ListedApp and ReportedIP, "app and IP", ListedApp, "app", "IP")
// Preserve raw result fields. Verify your tenant's values before classifying them.
| project TimeGenerated, AADTenantId, AppId, ServicePrincipalId, ServicePrincipalName,
    ResourceDisplayName, IPAddress, MatchBasis, ResultType, ResultSignature,
    ResultDescription, ClientCredentialType, ServicePrincipalCredentialKeyId,
    ServicePrincipalCredentialThumbprint, UserAgent, CorrelationId, Id
| order by TimeGenerated asc

// 03 / Broad access-change review, all actors and target identities in scope.
// Intentional review queue, NOT a high-confidence alert. Authorized changes match.
// Keep failed and unknown results. TargetResources contains per-target properties.
// Category fallback retains relevant changes even if an operation name changes.
let StartTime = datetime(2026-10-13T00:00:00Z);
let EndTime = now();
let TenantFilter = "";
AuditLogs
| where TimeGenerated >= StartTime and TimeGenerated < EndTime
| where isempty(TenantFilter) or AADTenantId =~ TenantFilter
| where Category in~ ("ApplicationManagement", "RoleManagement", "Policy", "UserManagement", "Audit")
    or OperationName contains "credential"
    or OperationName contains "Certificates and secrets"
    or OperationName contains "consent"
    or OperationName contains "owner"
    or OperationName contains "member to role"
| extend ActorAppId=tostring(InitiatedBy.app.appId),
    ActorServicePrincipalId=tostring(InitiatedBy.app.servicePrincipalId),
    ActorAppName=tostring(InitiatedBy.app.displayName),
    ActorUserId=tostring(InitiatedBy.user.id),
    ActorUPN=tostring(InitiatedBy.user.userPrincipalName),
    ActorIP=coalesce(tostring(InitiatedBy.user.ipAddress), tostring(InitiatedBy.app.ipAddress)),
    AllTargets=TargetResources
// Preserve the parent event when targets are absent or an empty array.
| extend TargetsForExpansion=iff(coalesce(array_length(TargetResources), 0) == 0,
    dynamic([{}]), TargetResources)
| mv-expand Target=TargetsForExpansion
| extend TargetObjectId=tostring(Target.id), TargetType=tostring(Target.type),
    TargetName=tostring(Target.displayName), ModifiedProperties=Target.modifiedProperties
| project TimeGenerated, AADTenantId, Id, CorrelationId, Category, OperationName,
    Result, ResultReason, ActorAppId, ActorServicePrincipalId, ActorAppName,
    ActorUserId, ActorUPN, ActorIP, TargetObjectId, TargetType, TargetName,
    ModifiedProperties, AllTargets
| order by TimeGenerated asc

// 04 / Credential-use pivot AFTER confirming the target identity and key metadata.
// Replace all three REPLACE values. These are keys/IDs, never secret values.
// Query deliberately returns nothing with placeholders; that is NOT a negative hunt.
// Credential IDs may be absent. Tokens can be reused without a new sign-in.
// Failed attempts are retained. Check the recorded outcome before inferring use.
let StartTime = datetime(2026-10-13T00:00:00Z);
let EndTime = now();
let TargetTenantId = "REPLACE_WITH_ENTRA_TENANT_GUID";
let TargetAppId = "REPLACE_WITH_APPLICATION_CLIENT_ID";
let TargetKeyId = "REPLACE_WITH_CREDENTIAL_KEY_ID";
AADServicePrincipalSignInLogs
| where TimeGenerated >= StartTime and TimeGenerated < EndTime
| where AADTenantId =~ TargetTenantId and AppId =~ TargetAppId
| where ServicePrincipalCredentialKeyId =~ TargetKeyId
| project TimeGenerated, AADTenantId, AppId, ServicePrincipalId, IPAddress,
    ResourceDisplayName, ResultType, ResultDescription, ClientCredentialType,
    ServicePrincipalCredentialKeyId, ServicePrincipalCredentialThumbprint, CorrelationId, Id
| order by TimeGenerated asc

skykick-sophos-endpoint.sql

Download source

-- DECEPTION CHECK / SKYKICK / SOPHOS WINDOWS ENDPOINT / 2026-10-19
-- Read-only Live Discover / Live Endpoint Search SQL. Run ONE block at a time.
-- Not Data Lake Search syntax. Read the Detection and Response Companion PDF first.
-- Current official schema uses snake_case fields. Verify the selected endpoint schema.
-- These searches supplement cloud identity investigation; they do not detect all app-only abuse.
-- Default network window: October 13, 2026 00:00 UTC through query execution.
-- Extend to October 1 where retained history permits. Record run time and device scope.
-- No customer tenant or Sophos engine execution has been performed.

-- E01 / Available IP-journal records within the window, by UTC day.
-- Missing days are absent, not zero-filled. Coverage must be checked separately.
SELECT date(time, 'unixepoch') AS day_utc,
       count(*) AS records,
       datetime(min(time), 'unixepoch') AS first_record_utc,
       datetime(max(time), 'unixepoch') AS last_record_utc
FROM sophos_ip_journal
WHERE time >= CAST(strftime('%s', '2026-10-13T00:00:00Z') AS INTEGER)
  AND time < CAST(strftime('%s', 'now') AS INTEGER)
GROUP BY date(time, 'unixepoch')
ORDER BY day_utc;

-- E02 / Reported IP on either side of a recorded endpoint connection event.
-- No process JOIN: retain events whose process context is unavailable.
-- A journal row is not proof of successful application traffic or compromise.
SELECT datetime(time, 'unixepoch') AS event_utc, time,
       pid, sophos_pid, process_start_time,
       source, source_port, destination, destination_port, protocol,
       CASE
         WHEN source IN ('144.172.64.128','144.172.64.99','144.172.64.60')
          AND destination IN ('144.172.64.128','144.172.64.99','144.172.64.60') THEN 'both'
         WHEN source IN ('144.172.64.128','144.172.64.99','144.172.64.60') THEN 'source'
         ELSE 'destination'
       END AS indicator_side
FROM sophos_ip_journal
WHERE time >= CAST(strftime('%s', '2026-10-13T00:00:00Z') AS INTEGER)
  AND time < CAST(strftime('%s', 'now') AS INTEGER)
  AND (source IN ('144.172.64.128','144.172.64.99','144.172.64.60')
    OR destination IN ('144.172.64.128','144.172.64.99','144.172.64.60'))
ORDER BY time;

-- E03 / Process context for an E02 hit, on the SAME endpoint.
-- Replace REPLACE_WITH_SOPHOS_PID with a nonempty sophos_pid from E02.
-- This is not the numeric Windows PID. Preserve the device identifier alongside it.
-- Explicit time bounds avoid the journal's default recent-only search.
-- Search retained process history, including starts before the incident window.
-- Start and end events are separate rows; a missing start may have aged out.
SELECT datetime(time, 'unixepoch') AS journal_utc, time, event_type,
       pid, sophos_pid, parent_sophos_pid, process_start_time,
       path, process_name, cmd_line
FROM sophos_process_journal
WHERE time >= 0
  AND time < CAST(strftime('%s', 'now') AS INTEGER)
  AND sophos_pid = 'REPLACE_WITH_SOPHOS_PID'
ORDER BY time;

skykick-sophos-m365-datalake.sql

Download source

-- DECEPTION CHECK / SKYKICK / SOPHOS LIVE DISCOVER DATA LAKE / 2026-10-19
-- Separate from Fusion Advanced Search and endpoint SQL. Run ONE block at a time.
-- Required: xdr_identity_o365 with Microsoft 365 Management Activity audit data.
-- This feed is NOT a substitute for Entra service-principal sign-in collection.
-- Read the Detection and Response Companion PDF first. Set the Data Lake selector explicitly in UTC:
-- October 13, 2026 00:00 through recorded investigation end; extend to Oct 1 if retained.
-- No event-time field is guessed here. The selector bounds the query.
-- Confirm customer tenant and organization_id from a known event before investigation.
-- Add organization_id = 'VERIFIED_TENANT_GUID' when narrowing the customer scope.
-- No Sophos Data Lake/Trino or customer-tenant execution has been performed.

-- L00 / Inventory of visible workload partitions in the selected window.
-- Counts do not prove continuous collection or dedicated SP sign-in coverage.
SELECT query_name, count(*) AS records
FROM xdr_identity_o365
GROUP BY query_name
ORDER BY query_name;

-- L01 / Listed requesting application OR reported client/actor IP in directory audit.
-- All outcomes retained. Exact IP strings can miss IP:port or alternate representations.
-- SELECT * preserves available source and metadata fields; handle exports as sensitive.
SELECT *
FROM xdr_identity_o365
WHERE query_name = 'AzureActiveDirectory'
  AND (
    lower(application_id) IN (
      '506e08f9-6e9a-4da4-942b-a099a03644ae',
      '2399d300-6fca-4e6a-a686-399fefe75b83',
      '24205314-2b89-4e07-a2a4-579e898a5640',
      'b78f6a77-d4e5-46fb-87e5-d1648bd02519',
      '0de14c77-b9b0-4137-9360-34878a929827',
      '412dcf96-176d-4b3a-a1b0-4981ad66971e',
      'b2b55f5b-7758-4719-8d8f-88ae172e6bdb'
    )
    OR client_ip IN ('144.172.64.128','144.172.64.99','144.172.64.60')
    OR actor_ip_address IN ('144.172.64.128','144.172.64.99','144.172.64.60')
  );

-- L02 / Broad access-change queue, independent of the app/IP list.
-- Strings are not parsed JSON. Inspect actor, target, result_status, and properties.
-- This keyword subset is not exhaustive; compare the wider directory audit timeline.
SELECT *
FROM xdr_identity_o365
WHERE query_name = 'AzureActiveDirectory'
  AND (
    lower(coalesce(operation, '')) LIKE '%credential%'
    OR lower(coalesce(operation, '')) LIKE '%member to role%'
    OR lower(coalesce(operation, '')) LIKE '%consent%'
    OR lower(coalesce(operation, '')) LIKE '%owner%'
    OR lower(coalesce(operation, '')) LIKE '%update application%'
    OR lower(coalesce(operation, '')) LIKE '%update service principal%'
    OR lower(coalesce(modified_properties, '')) LIKE '%passwordcredentials%'
    OR lower(coalesce(modified_properties, '')) LIKE '%keycredentials%'
    OR lower(coalesce(modified_properties, '')) LIKE '%credential%'
  );

skykick-sophos-searches.json

Download source

{
  "title": "SkyKick response: Sophos cloud and network hunt starters",
  "version": "2026-10-19-r1",
  "status": "REFERENCE_HUNT_STARTERS_NOT_DEPLOYED",
  "language": "Sophos Fusion Data Lake Search Advanced Search, NOT Live Discover SQL or KQL",
  "usage": "Copy ONE query value into Advanced Search. This JSON is a reference bundle, not a Sophos import format. Use the correct customer tenant and integration scope; confirm the returned source on a known event.",
  "timeWindow": "Set the UI range explicitly to October 13, 2026 00:00 UTC through the recorded investigation end. Extend to October 1 where records permit. These queries intentionally use the UI selector; do not leave its default 24-hour range.",
  "rawSearchLimit": "Sophos documents a 20-day access constraint for @raw/original_data. Check query acceptance and available raw retention before extending the window. Use normalized fields or retained exports when raw search is unavailable; an empty raw search does not establish absence.",
  "validation": "Documentation/schema and synthetic checks only. No Sophos engine or customer-tenant execution. Test against known events before operational use.",
  "queries": [
    {
      "id": "C00",
      "title": "Visible authentication and cloud-audit records",
      "query": "FROM auth, cloudaudit | aggregate count BY 1d",
      "interpretation": "Separate counts per schema, not a combined incident count or proof of Entra service-principal coverage. Confirm integration, customer tenant, event time, ingestion lag, and the selected categories. An unrelated auth source can produce a healthy-looking chart."
    },
    {
      "id": "C01",
      "title": "Reported source addresses in authentication and cloud audit",
      "query": "FROM auth, cloudaudit WHERE source_address IN ('144.172.64.128','144.172.64.99','144.172.64.60')",
      "interpretation": "Retains all outcomes. Inspect the source event, identity, resource, and original result. An IP match is a review lead, not proof of unauthorized access."
    },
    {
      "id": "C02",
      "title": "Seven listed application IDs in original event content",
      "query": "FROM auth, cloudaudit WHERE @raw CONTAINS '506e08f9-6e9a-4da4-942b-a099a03644ae' OR @raw CONTAINS '2399d300-6fca-4e6a-a686-399fefe75b83' OR @raw CONTAINS '24205314-2b89-4e07-a2a4-579e898a5640' OR @raw CONTAINS 'b78f6a77-d4e5-46fb-87e5-d1648bd02519' OR @raw CONTAINS '0de14c77-b9b0-4137-9360-34878a929827' OR @raw CONTAINS '412dcf96-176d-4b3a-a1b0-4981ad66971e' OR @raw CONTAINS 'b2b55f5b-7758-4719-8d8f-88ae172e6bdb'",
      "interpretation": "Independent of the IP search; keeps other addresses, including IPv6. Substring matches may identify an actor, target, resource, or old/new property. Inspect the original JSON before assigning a role. Not an exact AppId-field filter."
    },
    {
      "id": "C03",
      "title": "Access-change review across all applications",
      "query": "FROM cloudaudit WHERE @raw CONTAINS 'credential' OR @raw CONTAINS 'passwordCredentials' OR @raw CONTAINS 'keyCredentials' OR @raw CONTAINS 'Certificates and secrets' OR @raw CONTAINS 'member to role' OR @raw CONTAINS 'consent' OR @raw CONTAINS 'Add owner to' OR @raw CONTAINS 'Remove owner from' OR @raw CONTAINS 'Update application' OR @raw CONTAINS 'Update service principal'",
      "interpretation": "Broad triage, including legitimate changes and failed attempts. Does not restrict actors or targets to SkyKick. Inspect action, result, target object, actor, and before/after properties. Wording or parser differences can miss events; reconcile with the wider directory audit timeline."
    },
    {
      "id": "C04",
      "title": "Pivot on an investigated identity or credential key ID",
      "query": "FROM auth, cloudaudit WHERE @raw CONTAINS 'REPLACE_WITH_VERIFIED_NONEMPTY_ID'",
      "interpretation": "Replace the placeholder with a verified, nonempty target object ID, application client ID, or credential key ID, one at a time. Never use a secret value. Search matches are not a join: compare tenant, identity type, credential field, time, and result. A missing key field or reused token can leave this empty."
    },
    {
      "id": "N01",
      "title": "Supporting network pivot on either side",
      "query": "FROM netflow WHERE @ip IN ('144.172.64.128','144.172.64.99','144.172.64.60')",
      "interpretation": "Requires an ingested network source, such as configured firewall or endpoint telemetry. Inspect direction, allow/drop outcome, NAT, ports, and device context. Does not see attacker-to-Microsoft traffic that never crosses your monitored network."
    }
  ],
  "sources": [
    "https://www.connectwise.com/company/trust/advisories",
    "https://docs.sophos.com/fusion/customer/help/en-us/ManageYourProducts/XDRSecOps/Search/DataLakeSearch/QueryLanguage/index.html",
    "https://docs.sophos.com/fusion/customer/help/en-us/ManageYourProducts/XDRSecOps/Search/DataLakeSearch/SchemasLogicalTypes/LogicalTypes/index.html",
    "https://docs.sophos.com/fusion/customer/help/en-us/ManageYourProducts/XDRSecOps/Integrations/Products/MicrosoftEntra/ActivityReports/Setup/index.html"
  ]
}

Advanced normalized engineering

The prior dc_* mapping recipes remain available in the bundle under ADVANCED. They are optional integration work, not prerequisites for these native routes.