Defender for Cloud KQL

RGE-KQL-DoC.md

Azure Defender for Cloud KQL Queries

These are the queries that I wrote and found useful to extract data using Azure Resource Graph Explorer

Security Alerts

Fetch all the active suspicious authentication activities

SecurityResources
| where type == "microsoft.security/locations/alerts" and properties.AlertDisplayName=="Suspicious authentication activity" and properties.Status=="Active"

Fetch all the active suspicious authentication activities (Only Start Time and Hostname)

SecurityResources
| where type == "microsoft.security/locations/alerts" and properties.AlertDisplayName=="Suspicious authentication activity" and properties.Status=="Active"
| extend startTime=properties.StartTimeUtc,
     HostName=properties.Entities[0].HostName
| project startTime,HostName

SQL Vulnerabilities

All High Severity SQL Vulnerabilities (Databases & Servers inclusive)

SecurityResources
| where type == "microsoft.security/assessments/subassessments" and properties.additionalData.assessedResourceType=="SqlServerVulnerability" or properties.additionalData.assessedResourceType=="SqlVirtualMachineVulnerability" and properties.status.severity=="High"  and properties.status.code == "Unhealthy"
| extend vulnerability=properties.displayName,
    description=properties.description,
    severity=properties.status.severity,
    threat=properties.additionalData.threat,
    impact=properties.impact,
    fix=properties.remediation,
    vulnId=properties.id
| project id,vulnId,vulnerability,severity,description,threat,impact,fix

All High Severity Vulnerabilities on SQL Servers

The following query covers 'SQL Servers on machine should have vulnerability findings resolved' recommendation in Azure Security Center

SecurityResources
| where type == "microsoft.security/assessments/subassessments" and properties.additionalData.assessedResourceType=="SqlVirtualMachineVulnerability" and properties.status.severity=="High"  and properties.status.code == "Unhealthy"
| extend vulnerability=properties.displayName,
    description=properties.description,
    severity=properties.status.severity,
    threat=properties.additionalData.threat,
    impact=properties.impact,
    fix=properties.remediation,
    vulnId=properties.id
| project id,vulnId,vulnerability,severity,description,threat,impact,fix

Vulnerabilities on Servers or VMs

All vulnerabilities on virtual machines with machine name and date of generation

SecurityResources
| where type == "microsoft.security/assessments/subassessments" and properties.additionalData.assessedResourceType == "ServerVulnerability" or properties.additionalData.assessedResourceType == "ServerVulnerabilityTvm"  and properties.status.code == "Unhealthy"
| extend Vulnerability=properties.displayName,
    Description=properties.description,
    Severity=properties.status.severity,
    Threat=properties.additionalData.threat,
    Impact=properties.impact,
    Fix=properties.remediation,
    VulnId=properties.id,
    Date=format_datetime(todatetime(properties.timeGenerated),'yyyy-MM-dd'),
    UUID=name,
    VM=split(id,'/')[8]
| project UUID,VM,Vulnerability,Date,Severity,Description,Threat,Impact,Fix,VulnId