✄𐘗
text code block:
✄𐘗FROM logs-azure.activitylogs-*, logs-endpoint.events.process-* METADATA _id, _version, _index
| WHERE 
  (
    event.category == "process" AND KQL("event.action:start")
    AND process.parent.name == "powershell.exe"
    AND process.parent.command_line LIKE "powershell  -ExecutionPolicy Unrestricted -File script?.ps1"
    AND process.name != "conhost.exe"
  ) OR
  (
    KQL("event.category:process and event.action:exec and process.parent.name:(dash or bash or sh) and process.parent.args:/var/lib/waagent/run-command/download/*/script.sh")
   ) OR 
  (
    event.module == "azure"
    AND event.action == "MICROSOFT.COMPUTE/VIRTUALMACHINES/RUNCOMMAND/ACTION"
    AND NOT KQL("event.outcome:failure")
   )

// Azure hostname comes as upper-case while Endpoint event comes as lowercase
| EVAL Esql.host_name = COALESCE(
    TO_LOWER(host.name),
    TO_LOWER(azure.resource.name)
  )
| EVAL ts_runcommand = CASE(event.module == "azure", @timestamp, null)
| EVAL ts_endpoint = CASE(event.category == "process", @timestamp, null)
| EVAL is_runcommand = CASE(event.module == "azure", 1, null)
| EVAL is_endpoint = CASE(event.category == "process", 1, null)
| EVAL Esql.time_bucket = DATE_TRUNC(2 minutes, @timestamp)
| STATS
    runcommand_count = COUNT(is_runcommand),
    endpoint_count = COUNT(is_endpoint),
    user.email = VALUES(user.email),
    azure.activitylogs.identity.authorization.evidence.principal_id = VALUES(azure.activitylogs.identity.authorization.evidence.principal_id),
    azure.activitylogs.tenant_id = VALUES(azure.activitylogs.tenant_id),
    azure.subscription_id = VALUES(azure.subscription_id),
    source.ip = VALUES(source.ip),
    source.geo.country_name = VALUES(source.geo.country_name),
    source.as.number = VALUES(source.as.number),
    Esql.process_command_line_values = VALUES(process.command_line),
    first_runcommand = MIN(ts_runcommand),
    first_ps_exec = MIN(ts_endpoint),
    outcome = VALUES(event.outcome)
  BY Esql.host_name, Esql.time_bucket
| WHERE runcommand_count >= 1 AND endpoint_count >= 1
| EVAL delta_ms = TO_LONG(first_ps_exec) - TO_LONG(first_runcommand)
| EVAL delta_sec = delta_ms / 1000
| WHERE delta_sec >= 0 AND delta_sec <= 120
| KEEP
    user.email,
    azure.activitylogs.identity.authorization.evidence.principal_id,
    source.ip,
    source.as.number,
    source.geo.country_name,
    azure.activitylogs.tenant_id,
    azure.subscription_id,
    Esql.*