Docs

Appearance

KQL / Log Analytics Query Reference ​

Overview ​

Common KQL queries for security investigation and daily operations against the central Log Analytics workspace (cirius-logging-law-central, workspace ID 5d76d1f2).

Run queries in Azure Portal → Log Analytics workspace → cirius-logging-law-central → Logs, or via CLI:

bash
az monitor log-analytics query --workspace 5d76d1f2 \
  --analytics-query "<query here>" --output table

All queries default to ago(24h) — adjust the time range to match what you're investigating.

Identity and Authentication ​

Failed Sign-Ins (All Users, Last 24h) ​

kql
SignInLogs
| where TimeGenerated > ago(24h)
| where ResultType != 0
| summarize Failures = count() by UserPrincipalName, ResultDescription, IPAddress
| sort by Failures desc

Failed Sign-Ins for a Specific User ​

kql
SignInLogs
| where TimeGenerated > ago(7d)
| where UserPrincipalName == "user@ciriusgroup.com"
| project TimeGenerated, ResultType, ResultDescription, IPAddress, Location, AppDisplayName, DeviceDetail
| sort by TimeGenerated desc

Successful Sign-Ins From New Countries (Possible Impossible Travel) ​

kql
SignInLogs
| where TimeGenerated > ago(7d)
| where ResultType == 0
| where Location !in ("US", "")
| project TimeGenerated, UserPrincipalName, Location, IPAddress, AppDisplayName
| sort by TimeGenerated desc

MFA Failures or Interrupts ​

kql
SignInLogs
| where TimeGenerated > ago(24h)
| where AuthenticationRequirement == "multiFactorAuthentication"
| where ResultType in (50074, 50076, 500121, 500133)
| project TimeGenerated, UserPrincipalName, ResultDescription, IPAddress, Location

Privileged Role Activations (PIM) ​

kql
AuditLogs
| where TimeGenerated > ago(7d)
| where OperationName has "Add member to role"
| where Category == "RoleManagement"
| project TimeGenerated, InitiatedBy = InitiatedBy.user.userPrincipalName,
    TargetUser = tostring(TargetResources[0].userPrincipalName),
    Role = tostring(TargetResources[0].displayName)
| sort by TimeGenerated desc

Break-Glass Account Activity (Any Login = Alert) ​

kql
SignInLogs
| where TimeGenerated > ago(30d)
| where UserPrincipalName in ("cirius-breakglass@ciriusgroup.com", "cirius-breakglass@ciriusdde.com")
| project TimeGenerated, UserPrincipalName, ResultType, IPAddress, Location, AppDisplayName
| sort by TimeGenerated desc

Users with Most Successful Sign-Ins (Baseline / Anomaly Detection) ​

kql
SignInLogs
| where TimeGenerated > ago(7d)
| where ResultType == 0
| summarize SignIns = count() by UserPrincipalName
| sort by SignIns desc
| take 20

Endpoint and Process (Windows Security Events) ​

Process Creations — Suspicious Parent-Child Relationships ​

kql
SecurityEvent
| where TimeGenerated > ago(24h)
| where EventID == 4688
| where ParentProcessName has_any ("winword.exe", "excel.exe", "outlook.exe", "powerpnt.exe")
    and NewProcessName has_any ("powershell.exe", "cmd.exe", "wscript.exe", "mshta.exe", "rundll32.exe")
| project TimeGenerated, Computer, Account, ParentProcessName, NewProcessName, CommandLine
| sort by TimeGenerated desc

PowerShell Script Block Logging (EventID 4104) ​

kql
SecurityEvent
| where TimeGenerated > ago(24h)
| where EventID == 4104
| where EventData has_any ("Invoke-Mimikatz", "IEX", "Invoke-Expression",
    "Net.WebClient", "DownloadString", "FromBase64String", "bypass")
| project TimeGenerated, Computer, Account, EventData
| sort by TimeGenerated desc

New Services Installed (Persistence — EventID 7045) ​

kql
SecurityEvent
| where TimeGenerated > ago(24h)
| where EventID == 7045
| project TimeGenerated, Computer, ServiceName = extract(@"Service Name:\s+(.+)", 1, EventData),
    ServiceType = extract(@"Service Type:\s+(.+)", 1, EventData),
    ServiceFile = extract(@"Service File Name:\s+(.+)", 1, EventData)
| sort by TimeGenerated desc

Scheduled Task Creation (Persistence — EventID 4698) ​

kql
SecurityEvent
| where TimeGenerated > ago(24h)
| where EventID == 4698
| project TimeGenerated, Computer, Account, TaskName = extract(@"Task Name:\s+(.+)", 1, EventData)
| sort by TimeGenerated desc

Audit Log Cleared (Defense Evasion — EventID 1102) ​

kql
SecurityEvent
| where TimeGenerated > ago(7d)
| where EventID == 1102
| project TimeGenerated, Computer, Account
| sort by TimeGenerated desc

Admin Share Access (Lateral Movement — EventID 5140) ​

kql
SecurityEvent
| where TimeGenerated > ago(24h)
| where EventID == 5140
| where ShareName in ("\\\\*\\C$", "\\\\*\\ADMIN$", "\\\\*\\IPC$")
| project TimeGenerated, Computer, Account, IPAddress, ShareName
| sort by TimeGenerated desc

Logon Events — Single Account, Multiple Machines ​

kql
SecurityEvent
| where TimeGenerated > ago(4h)
| where EventID == 4624
| where LogonType == 3
| summarize Machines = dcount(Computer), List = make_set(Computer) by Account
| where Machines > 3
| sort by Machines desc

Palo Alto Firewall Logs ​

DNS Security Blocks (C2 Callback Domains) ​

kql
CommonSecurityLog
| where TimeGenerated > ago(24h)
| where DeviceVendor == "Palo Alto Networks"
| where Activity == "THREAT"
| where DeviceEventClassID has "dns"
| project TimeGenerated, SourceIP, DestinationHostName, DestinationIP, Message
| sort by TimeGenerated desc

Threat Prevention Alerts (IPS) ​

kql
CommonSecurityLog
| where TimeGenerated > ago(24h)
| where DeviceVendor == "Palo Alto Networks"
| where Activity == "THREAT"
| where LogSeverity in ("high", "critical")
| project TimeGenerated, SourceIP, DestinationIP, Message, DeviceAction
| sort by TimeGenerated desc

Denied Traffic (What's Being Blocked) ​

kql
CommonSecurityLog
| where TimeGenerated > ago(1h)
| where DeviceVendor == "Palo Alto Networks"
| where Activity == "TRAFFIC"
| where DeviceAction == "deny"
| summarize Count = count() by SourceIP, DestinationIP, DestinationPort
| sort by Count desc
| take 20

Top Talkers (Outbound Traffic Volume) ​

kql
CommonSecurityLog
| where TimeGenerated > ago(24h)
| where DeviceVendor == "Palo Alto Networks"
| where Activity == "TRAFFIC"
| where DeviceAction == "allow"
| summarize TotalBytes = sum(SentBytes + ReceivedBytes) by SourceIP
| sort by TotalBytes desc
| take 20

Cloud and Infrastructure ​

Azure Activity Log — Resource Changes ​

kql
AzureActivity
| where TimeGenerated > ago(24h)
| where ActivityStatusValue == "Success"
| where OperationNameValue !has "GET" and OperationNameValue !has "LIST"
| project TimeGenerated, Caller, OperationNameValue, ResourceGroup, Resource
| sort by TimeGenerated desc

Azure Activity Log — Failed Operations ​

kql
AzureActivity
| where TimeGenerated > ago(24h)
| where ActivityStatusValue == "Failure"
| project TimeGenerated, Caller, OperationNameValue, ResourceGroup, Properties
| sort by TimeGenerated desc

Container App Errors (SecOps Platform) ​

kql
ContainerAppConsoleLogs_CL
| where TimeGenerated > ago(1h)
| where ContainerName_s == "ca-secops-prod"
| where Log_s has_any ("ERROR", "CRITICAL", "Exception", "Traceback")
| project TimeGenerated, Log_s
| sort by TimeGenerated desc

Key Vault Access Log ​

kql
AzureDiagnostics
| where TimeGenerated > ago(24h)
| where ResourceType == "VAULTS"
| where OperationName == "SecretGet"
| project TimeGenerated, CallerIPAddress, identity_claim_oid_g, requestUri_s
| sort by TimeGenerated desc

AWS (CloudTrail via Log Analytics) ​

CloudTrail logs from the logging account are forwarded to the central LAW workspace.

Root Account Activity ​

kql
AWSCloudTrail
| where TimeGenerated > ago(30d)
| where UserIdentityType == "Root"
| project TimeGenerated, EventName, SourceIpAddress, UserAgent, AWSRegion
| sort by TimeGenerated desc

IAM Changes ​

kql
AWSCloudTrail
| where TimeGenerated > ago(7d)
| where EventSource == "iam.amazonaws.com"
| where EventName in ("CreateUser", "DeleteUser", "AttachUserPolicy", "CreateAccessKey",
    "DeleteAccessKey", "CreateRole", "AttachRolePolicy", "PutRolePolicy")
| project TimeGenerated, EventName, UserIdentityArn, RequestParameters
| sort by TimeGenerated desc

Console Sign-Ins ​

kql
AWSCloudTrail
| where TimeGenerated > ago(7d)
| where EventName == "ConsoleLogin"
| project TimeGenerated, UserIdentityArn, SourceIpAddress,
    Result = tostring(ResponseElements.ConsoleLogin)
| sort by TimeGenerated desc

Operational Health ​

Agent Heartbeat (Are All VMs Checking In?) ​

kql
Heartbeat
| where TimeGenerated > ago(24h)
| summarize LastHeartbeat = max(TimeGenerated) by Computer
| extend MinutesSinceLast = datetime_diff('minute', now(), LastHeartbeat)
| where MinutesSinceLast > 60
| sort by MinutesSinceLast desc

Log Ingestion Volume by Table (Cost and Anomaly Check) ​

kql
Usage
| where TimeGenerated > ago(7d)
| where IsBillable == true
| summarize GB = sum(Quantity) / 1000 by DataType
| sort by GB desc

Workspace Ingestion Spikes (Last 7 Days) ​

kql
Usage
| where TimeGenerated > ago(7d)
| where IsBillable == true
| summarize DailyGB = sum(Quantity) / 1000 by bin(TimeGenerated, 1d)
| sort by TimeGenerated asc
| render timechart

Investigation Helpers ​

Find All Events for a Specific IP Address ​

kql
let TargetIP = "1.2.3.4";
union SignInLogs, CommonSecurityLog, AzureActivity, AWSCloudTrail
| where TimeGenerated > ago(24h)
| where * has TargetIP
| project TimeGenerated, Type, SourceSystem, tostring(pack_all())
| sort by TimeGenerated desc

Normalize Hostnames (FQDN to Short Name) ​

kql
// Use this pattern when joining event sources that use different hostname formats
SecurityEvent
| extend ShortName = toupper(tostring(split(Computer, ".")[0]))
| join kind=leftouter (
    Heartbeat | summarize LastSeen = max(TimeGenerated) by Computer
    | extend ShortName = toupper(tostring(split(Computer, ".")[0]))
) on ShortName

Time Window Filter Pattern ​

kql
// Narrow to exact incident window
let StartTime = datetime(2026-05-24 14:00:00);
let EndTime = datetime(2026-05-24 16:00:00);
SecurityEvent
| where TimeGenerated between (StartTime .. EndTime)
| where EventID == 4624

Document History ​

DateChangeAuthor
May 2026Initial draft — identity, endpoint, firewall, cloud, AWS, operational health, and investigation helper queries.Rory

Internal use only — Cirius Group