Work with Azure Security Center alert in Log Analytics

Azure Security Center allows you to specify a Log Analytics (LA) workspace to collect data. As far as I know there are two data types that are fed to the configured workspace: SecurityAlert and SecurityEvent. Microsoft recently introduced a Continuous Export which provides the ability to export ASC alert to multiple sources such as Event Hub or Log Analytics.

In this article, I would like to share some queries to help you get started with Kusto Query Language in order to work with ASC alert data in LA workspace.

Pre-requisites

You need data to be available in a LA workspace before you can write a query.

Follow this article to configure your workspace

Once you configure ASC data collection you’d need to wait until alert is generated. Microsoft has a guide here to help generate sample alerts. If you are familiar with PowerShell, you can use execute an encoded payload to trigger ASC alert. See the sample below:

$filePath = "https://testingstr.blob.core.windows.net/ctn/pw.ps1"
$localPath = "C:\Temp\pw.ps1"
$payLoad = "Invoke-WebRequest -Uri $filePath -OutFile $localPath"

$b64 = [Convert]::ToBase64String([System.Text.Encoding]::Unicode.GetBytes("'$payLoad'"))
powershell.exe -encodedCommand $b64

The code snippet above would trigger High severity with an alert named “Suspicious Powershell Activity Detected

Queries

To list all alerts for the last 24 hours, use the following query:

SecurityAlert
| where TimeGenerated >=ago(24h)

To count total number of alerts for the last 24 hours, use the following query:

SecurityAlert
| where TimeGenerated >=ago(24h)
| count

To list specific alert type (e.g, ‘Traffic from unrecommended IP addresses was detected’ which is generated from Azure Adaptive Network Hardening), use the following query:

SecurityAlert
| where TimeGenerated >=ago(24h)
| where AlertName == "Traffic from unrecommended IP addresses was detected"

To show only column you expect to see for example TimeGenerated, StartTime, EndTime and ResourceId, use the following query:

SecurityAlert 
| where TimeGenerated >=ago(24h) 
| where AlertName == "Traffic from unrecommended IP addresses was detected"
| project TimeGenerated, StartTime, EndTime, ResourceId

To count number of alerts for each of point of time and sort descending by number, use the following query:

SecurityAlert
| where TimeGenerated >=ago(24h)
| summarize count() by TimeGenerated
| sort by count_

To count number of alerts that occurs every 1 hour during the last 24 hours, use the following query:

SecurityAlert
| where TimeGenerated >=ago(24h)
| summarize count() by bin(TimeGenerated, 1h)

You can render the result with time chart by adding | render timechart

To list distinct alert for the last 24 hours, use the following query:

SecurityAlert
| where TimeGenerated >=ago(24h)
| distinct AlertName

To visualize alert type with piechart, use the following query:

SecurityAlert
| where TimeGenerated >=ago(24h)
| summarize count() by AlertName
| render piechart

To list all alerts between a time range, use the following query:

let startTime = datetime(2019-09-10);
let endTime = datetime(2019-09-12);
SecurityAlert
| where TimeGenerated between (startTime .. endTime)

To parse alert body from JSON to friendly-string format, use the following query:

SecurityAlert
| where TimeGenerated >=ago(24h)
| where AlertName == "Traffic from unrecommended IP addresses was detected"
| extend destPort = parse_json(ExtendedProperties)['Destination Port']
| extend protocol = parse_json(ExtendedProperties)['Protocol']

Most of alert types have ExtendedProperties schema. Depending on the field in this schema, your query may vary.

To correlate with another data source such as SecurityEvent for example when you see a crash dump alert you would like to find what process on which machine that causes crash dump

let processName = SecurityAlert 
| where DisplayName == "Modified system binary discovered" 
| where TimeGenerated>=ago(30d) 
| extend properties = parse_json(ExtendedProperties) 
| extend process_name = tostring(properties['ProcessName'])
| extend process_version = tostring(properties['ProcessVersion'])
| distinct process_name; 
SecurityEvent 
| where Process in (processName) 
| where TimeGenerated>=ago(30d) 
| project TimeGenerated, Computer, CommandLine, NewProcessName, ParentProcessName, SubjectAccount

I hope some queries above are helpful to your ASC operation. Please leave a comment if you would like to discuss another query requirement or use case.

This entry was posted in Azure Security Center and tagged , , . Bookmark the permalink.

4 Responses to Work with Azure Security Center alert in Log Analytics

  1. Pingback: Audit Azure Security Center in your tenant - All about security on Microsoft AzureAll about security on Microsoft Azure

  2. Pingback: An analysis of Suspicious Authentication activity from Azure Security Center - Microsoft Azure Security RandomnessMicrosoft Azure Security Randomness

  3. Pavan G says:

    Hi,

    This was nice article and really helpful. I query probably not related security center.

    I want create dashboard for MMA. Total no of resource in VM blade vs total vm’s sending heartbeat for last 24 hours.

    Similarly im looking to build the dashboard for total resources vs no of resource sending logs i.e for PaaS.

    • azsec says:

      Hi Pavan,

      Could you refer to this one https://azsec.azurewebsites.net/2020/02/08/export-virtual-machines-with-asc-monitoring-agent-issue/ ? This gives you query to find out agent state on every virtual machine that is connected to Azure Security Center. You can build a dashboard (using Azure Workbook).

      For Azure Hearbeat, here is the sample one

      //Finds computers that were active in the last day but did not send heartbeats in the last hour.
      Heartbeat
      | where TimeGenerated > ago(1d)
      | summarize LastHeartbeat = max(TimeGenerated) by Computer
      | where isnotempty(Computer)
      | where LastHeartbeat < ago(1h)

Leave a Reply