Azure Sentinel 5. KQL Automation + ChatGPT

We are going to create a KQL automation integration with ChatGPT

For previously ChatGPT playbook builds please check my previous blogs

• Sentinel incidents & ChatGPT integration

• Sentinel raw logs & ChatGPT integration

• Sentinel incident comments & ChatGPT integration

Create a variable for ChatGPT KQL Role

Here are the value:

You are cyber security analyst with expertise in statistical modeling. Your goal is to generate KQL (Kusto Query Language) queries to analyze cybersecurity incidents effectively. The queries must be actionable and tailored to investigate specific indicators of compromise (IoCs) and follow the recommended steps for analysis.


Structure:
'''
Include comments in the KQL queries to explain each step of the query.
Use filters for the incident's specific IoCs (e.g., IP addresses, file hashes, or domain names).
Leverage appropriate data sources and tables for the investigation.
Correlate information across tables if necessary to uncover patterns or anomalies.
'''


Data Sources: The queries can utilize the following tables (or their equivalents based on the data source):
'''
Microsoft Entra ID Logs: SigninLogs, AuditLogs
Azure Cloud: Azure Activity
Microsoft Defender: DeviceEvents, DeviceNetworkEvents, AlertInfo, CloudAppEvents,
DeviceFileEvents, DeviceImageLoadEvents, DeviceRegistryEvents, UrlClickEvents, EmailEvents
Windows Security Logs: SecurityEvent
'''


Query Requirements:
'''
Focus on identifying affected users, devices, or processes.
Search for specific IoCs provided in the incident details.
Provide summaries or visualizations where applicable (e.g., using summarize or render).
'''


Output: Ensure the queries
'''
Are optimized for performance.
Include detailed filtering and correlation logic.
Clearly indicate findings such as counts, unique entities, or unusual patterns.
Progress from simple queries to more difficult ones
'''

Create a variable for ChatGPT KQL prompt

Create a variable for ChatGPT KQL Response

Create another HTTP call

You can copy everything from previous HTTP call, but replace the variables in the "Body" below

Create an action for Parse Json

For the schema, I copied from previous Parse Json action

Assign the outputs to a variable

Add a comment section for KQL generation:

Run the playbook against an incident

i.e.

KQL Generation:

Certainly. Below are a series of progressively complex and actionable KQL queries to investigate the high-likelihood threat involving the SUNBURST campaign IoCs, particularly focusing on:

- The malicious domain: avsvmcloud.com
- The suspicious IP: 17.81.146.1
- DNS resolution events
- Lack of endpoint telemetry
- Potential timestamp anomalies

---

## 🧭 Step 1: Identify DNS Queries to Malicious Domain

```kql
// Query DNS resolution events for the known malicious domain 'avsvmcloud.com'
// This helps identify which devices or users attempted to resolve the C2 domain

DeviceNetworkEvents
| where RemoteUrl has "avsvmcloud.com" // IOC: Known SUNBURST C2 domain
| project Timestamp, DeviceName, InitiatingProcessAccountName, RemoteUrl, RemoteIP, ReportId, InitiatingProcessFileName
| order by Timestamp desc
```

---

## 🧭 Step 2: Filter for Successful DNS Resolutions (NOERROR)

```kql
// Focus on successful DNS resolutions (NOERROR) to determine actual communication attempts
// This helps confirm whether the domain was actively resolved

DeviceNetworkEvents
| where RemoteUrl has "avsvmcloud.com"
| where ActionType == "DnsQueryResponse" and DnsResponseCode == "NOERROR"
| project Timestamp, DeviceName, RemoteUrl, RemoteIP, InitiatingProcessAccountName, InitiatingProcessFileName
| summarize Count = count(), Devices = dcount(DeviceName), Users = dcount(InitiatingProcessAccountName) by RemoteIP
| order by Count desc
```

---

## 🧭 Step 3: Investigate Connections to Suspicious IP (17.81.146.1)

```kql
// Check for any direct connections to the suspicious IP used in SUNBURST redirection
// This may indicate beaconing or callback behavior

DeviceNetworkEvents
| where RemoteIP == "17.81.146.1" // IOC: Suspicious IP
| project Timestamp, DeviceName, InitiatingProcessAccountName, InitiatingProcessFileName, RemotePort, Protocol
| summarize Count = count(), Devices = dcount(DeviceName), Users = dcount(InitiatingProcessAccountName) by bin(Timestamp, 1h)
| order by Timestamp desc
```

---

## 🧭 Step 4: Correlate DNS Queries with Device and User Context

```kql
// Correlate DNS queries to 'avsvmcloud.com' with device and user identity
// Helps identify affected users and systems for containment

DeviceNetworkEvents
| where RemoteUrl has "avsvmcloud.com"
| summarize QueryCount = count(), FirstSeen = min(Timestamp), LastSeen = max(Timestamp) by DeviceName, InitiatingProcessAccountName
| order by QueryCount desc
```

---

## 🧭 Step 5: Check for SolarWinds Orion Presence on Devices

```kql
// Look for known SolarWinds Orion executables on endpoints
// This is critical to confirm if affected systems had the vulnerable software

DeviceFileEvents
| where FileName has "SolarWinds.Orion" or FolderPath has "SolarWinds"
| project Timestamp, DeviceName, FileName, FolderPath, InitiatingProcessAccountName
| summarize Count = count(), FirstSeen = min(Timestamp), LastSeen = max(Timestamp) by DeviceName
```

---

## 🧭 Step 6: Search for Process Anomalies Related to DNS Queries

```kql
// Investigate processes that initiated DNS queries to the malicious domain
// Helps identify if unusual or unexpected processes are responsible

DeviceNetworkEvents
| where RemoteUrl has "avsvmcloud.com"
| summarize Count = count() by InitiatingProcessFileName, InitiatingProcessCommandLine
| order by Count desc
```

---

## 🧭 Step 7: Audit User Sign-ins Around the Time of DNS Queries

```kql
// Check for unusual sign-in activity around the time of the DNS queries
// Useful for detecting lateral movement or compromised accounts

let suspiciousTimeRange = DeviceNetworkEvents
| where RemoteUrl has "avsvmcloud.com"
| summarize StartTime = min(Timestamp), EndTime = max(Timestamp);

SigninLogs
| where TimeGenerated between (suspiciousTimeRange.StartTime - 1h .. suspiciousTimeRange.EndTime + 1h)
| summarize Count = count(), UniqueUsers = dcount(UserPrincipalName), UniqueIPs = dcount(IPAddress) by bin(TimeGenerated, 1h)
| render timechart
```

---

## 🧭 Step 8: Look for Known SUNBURST DLLs or Hashes (if available)

```kql
// If known SUNBURST DLL hashes are available, search for them in file events
// Placeholder hash used below; replace with actual known hash if available

let sunburstHash = "b91ce2fa41029f6955bff20079468448"; // Example SHA256 hash
DeviceFileEvents
| where SHA256 == sunburstHash
| project Timestamp, DeviceName, FileName, FolderPath, InitiatingProcessAccountName
```

---

## 🧭 Step 9: Detect Timestamp Anomalies (e.g., 2019 Logs)

```kql
// Investigate if any logs related to the domain or IP are from 2019
// This could indicate clock skew or early testing by attackers

DeviceNetworkEvents
| where RemoteUrl has "avsvmcloud.com" or RemoteIP == "17.81.146.1"
| where Timestamp < datetime(2020-01-01)
| project Timestamp, DeviceName, RemoteUrl, RemoteIP, InitiatingProcessAccountName
| order by Timestamp asc
```

---

## 🧭 Step 10: Cross-Table Correlation for Threat Hunting

```kql
// Correlate DNS queries with potential alerts and file events on the same device
// Helps build a timeline of suspicious activity

let dnsEvents = DeviceNetworkEvents
| where RemoteUrl has "avsvmcloud.com"
| project DeviceName, DnsTime = Timestamp;

let fileEvents = DeviceFileEvents
| project DeviceName, FileTime = Timestamp, FileName, FolderPath;

let alerts = AlertInfo
| project DeviceName, AlertTime = Timestamp, Title;

dnsEvents
| join kind=inner (fileEvents) on DeviceName
| join kind=inner (alerts) on DeviceName
| where abs(datetime_diff('minute', DnsTime, FileTime)) < 60
| where abs(datetime_diff('minute', DnsTime, AlertTime)) < 60
| project DeviceName, DnsTime, FileTime, AlertTime, FileName, FolderPath, Title
```

---

## ✅ Summary Dashboard: Affected Devices and Users

```kql
// Summary of affected devices and users based on DNS queries to the malicious domain

DeviceNetworkEvents
| where RemoteUrl has "avsvmcloud.com"
| summarize TotalQueries = count(), UniqueDevices = dcount(DeviceName), UniqueUsers = dcount(InitiatingProcessAccountName)
```

---

## 🧠 Notes & Recommendations

- All queries are optimized with filters and summarizations to reduce unnecessary data scans.
- Replace placeholder hash with actual SUNBURST DLL hashes from threat intel.
- Consider exporting results to CSV or dashboard for incident response teams.
- If endpoint telemetry is missing, correlate with proxy, firewall, or DNS logs outside Defender if available.

Let me know if you'd like to automate these queries or integrate them into a workbook or Sentinel playbook.