This blog post is about another use of KQL that will definitely help your organisation and make you very popular.
First of all check out this video here which can help you learn stuff about KQL:
https://www.microsoft.com/en-us/videoplayer/embed/RWRwfJ?postJsllMsg=true
I have been talking about KQL and using it with Log Analytics and also Application Insights.
We use Log analytics for all our infrastructure things and combine with Azure Monitor to “do” things with our captured data:
But just as equally important is Advanced Hunting – where we can use KQL and start looking at what security events are occurring within our Azure Subscription.
https://security.microsoft.com/v2/advanced-hunting
(NOTE: you do need an Azure subscription to run this)
Advanced hunting is based on the Kusto query language. We can use Kusto operators and statements to construct queries that locate information in a specialized schema.
Here is an example that we can run:
// Finds PowerShell execution events that could involve a download union DeviceProcessEvents, DeviceNetworkEvents
| where Timestamp > ago(7d)
// Pivoting on PowerShell processes
| where FileName in~ ("powershell.exe", "powershell_ise.exe")
// Suspicious commands
| where ProcessCommandLine has_any
("WebClient",
"DownloadFile",
"DownloadData",
"DownloadString",
"WebRequest",
"Shellcode",
"http",
"https")
| project Timestamp, DeviceName, InitiatingProcessFileName, InitiatingProcessCommandLine, FileName, ProcessCommandLine, RemoteIP, RemoteUrl, RemotePort, RemoteIPType
| top 100 by Timestamp
Let’s break this query down a bit
A short comment has been added to the beginning of the query to describe what it is for. This comment helps if you later decide to save the query and share it with others in your organization.
// Finds PowerShell execution events that could involve a download
The query itself will typically start with a table name followed by several elements that start with a pipe (|). In this example, we start by creating a union of two tables, DeviceProcessEvents and DeviceNetworkEvents, and add piped elements as needed.
union DeviceProcessEvents, DeviceNetworkEvents
Set the time range
The first piped element is a time filter scoped to the previous seven days. Limiting the time range helps ensure that queries perform well, return manageable results, and don’t time out.
| where Timestamp > ago(7d)
Check specific processes
The time range is immediately followed by a search for process file names representing the PowerShell application.
// Pivoting on PowerShell processes
| where FileName in~ ("powershell.exe", "powershell_ise.exe")
Search for specific command strings
Afterwards, the query looks for strings in command lines that are typically used to download files using PowerShell.
// Suspicious commands
| where ProcessCommandLine has_any("WebClient",
"DownloadFile",
"DownloadData",
"DownloadString",
"WebRequest",
"Shellcode",
"http",
"https")
Customize result columns and length
Now that your query clearly identifies the data you want to locate, you can define what the results look like. project returns specific columns, and top limits the number of results. These operators help ensure the results are well-formatted and reasonably large and easy to process.
| project Timestamp, DeviceName, InitiatingProcessFileName, InitiatingProcessCommandLine,
FileName, ProcessCommandLine, RemoteIP, RemoteUrl, RemotePort, RemoteIPType
| top 100 by Timestamp
Select Run query to see the results.
This truly an amazing usage of KQL in my opinion!!
You can read more about Advanced hunting (Formerly known as Advanced Threat Protection) here: