51 Security

Learning, Sharing, Creating

SIEM

Azure Sentinel Log Query Scripts Collection (Kusto Query Language)

Bynetsec

Jul 15, 2024

Kusto Query Language is the language you will use to work with and manipulate data in Microsoft Sentinel. The logs you feed into your workspace aren’t worth much if you can’t analyze them and get the important information hidden in all that data. Kusto Query Language has not only the power and flexibility to get that information, but the simplicity to help you get started quickly. If you have a background in scripting or working with databases, a lot of the content of this article will feel very familiar. If not, don’t worry, as the intuitive nature of the language quickly enables you to start writing your own queries and driving value for your organization.

Basic KQL

AzureActivity
| limit 100

Kusto Query Language is the language used across Azure Monitor, Azure Data Explorer and Azure Log Analytics (what Microsoft Sentinel uses under the hood). I have always found this visualization regarding KQL useful –

KQL visualized

We want to use KQL to create accurate and efficient queries to find threats, detections, patterns and anomalies from within our larger data set.

Take the below query as an example

SigninLogs
| where TimeGenerated > ago(14d)
| where UserPrincipalName == "[email protected]"
| where ResultType == "0"
| where AppDisplayName == "Microsoft Teams"
| project TimeGenerated, Location, IPAddress, UserAgent

When we run a query like this the first line tells Microsoft Sentinel which table to look for data in, so in this case we want to search the SigninLogs table, which is where Azure AD sign in data is sent to. You can see a list of tables here.

Microsoft Sentinel will then run through your query sequentially, so it will run each line one by one until it hits the end, or you have an error. So to breakdown our query line by line.

SigninLogs

So first we have chosen our SigninLogs table.

SigninLogs
| where TimeGenerated > ago(14d)

Next we tell Sentinel to look back at the last 14 days worth of data in this table.

SigninLogs
| where TimeGenerated > ago(14d)
| where UserPrincipalName == "[email protected]"

Next we ask Sentinel to only find logs where UserPrincipalName is equal to “[email protected]“

SigninLogs
| where TimeGenerated > ago(14d)
| where UserPrincipalName == "[email protected]"
| where ResultType == "0"

Then we look for only logs where the ResultType == 0, which is a successful logon to Azure AD.

SigninLogs
| where TimeGenerated > ago(14d)
| where UserPrincipalName == "[email protected]"
| where ResultType == "0"
| where AppDisplayName == "Microsoft Teams"

Next we look for only signins to Microsoft Teams.

SigninLogs
| where TimeGenerated > ago(14d)
| where UserPrincipalName == "[email protected]"
| where ResultType == "0"
| where AppDisplayName == "Microsoft Teams"
| project TimeGenerated, Location, IPAddress, UserAgent

Our last line uses the project operator, to return only 4 fields from our logs, so we will only see the TimeGenerated, Location, IPAddress and UserAgent returned from our SigninLogs data.

System Query

Print out time and timezone

By default, it is UTC time zone: 
  •  let Now = now(); print Now

Search Keywords in Tables

search in (CommonSecurityLog) “172.17.20.10”

search in (Syslog) “172.17.20.10”

CommonSecurityLog
| where DeviceVendor contains “Palo Alto Networks”
| where DeviceCustomString6 contains “LogForward”

Syslog
| where Computer !contains “10”
Syslog
| summarize count() by Computer
CommonSecurityLog
| where DeviceVendor =~ ‘SonicWall’
| summarize count() by Activity

SecurityEvent
| where Computer has_any (“AD01″,”AD02”)
| where EventID == “4662”
| summarize count() by Account

Count Logs in a Table

Check Last 5 logs

SigninLogs
| sort by TimeGenerated desc
| take 5
SecurityEvent
| top 50 by TimeGenerated desc
// Recent Azure Activity logs 
// Display all Azure Activity logs from the last hour. 
AzureActivity 
| where Level == “Error” or Level == “Warning”
| project TimeGenerated, Level, ResourceProvider, ActivityStatus, Caller, Category, Properties, CorrelationId

Showing last 7 days log trending

 let Now = now();
        (range TimeGenerated from ago(7d) to Now-1d step 1d
                | extend Count = 0
                | union isfuzzy=true
                (SecurityEvent
                | summarize Count = count() by bin_at(TimeGenerated, 1d, Now))
                | summarize Count=max(Count) by bin_at(TimeGenerated, 1d, Now)
                | sort by TimeGenerated
                | project Value = iff(isnull(Count), 0, Count), Time = TimeGenerated, Legend = “SecurityEvents”) | render timechart 

Check certain table’s raw logs in last 1 hour:

meraki_CL
| where TimeGenerated > ago(1h)
| sort by TimeGenerated desc

Which Windows machine is sending logs through Azure Monitor Agemt?

Heartbeat | where OSType == ‘Windows’ | where Category != ‘Azure Monitor Agent’| summarize arg_max(TimeGenerated, *) by SourceComputerId | sort by Computer | render table

Check Subscription ID

AzureActivity
| summarize by SubscriptionId

Rendor Operator to Draw a Diagram 

Use the summarize operator to creat time series

Top 10 Pie Chart 

Syslog
| where TimeGenerated > ago(7d)
| summarize Count=count() by Computer
| top 10 by Count
| render piechart

Bar Chart

SecurityAlert
| where TimeGenerated >= ago(30d)
| summarize count() by ProviderName
| render barchart 

Time series visualizations

Syslog
| where TimeGenerated > ago(30d)
| summarize Count=count() by bin(TimeGenerated, 1d)
| render timechart 
CommonSecurityLog
| where TimeGenerated > ago(30d)
| where Computer contains “FG”
| summarize Count=count() by bin(TimeGenerated, 1d)
| render timechart 
Or add 
| where DeviceVendor == “Fortinet”
| where DeviceProduct startswith “Fortigate”

This will show the log counts based on DeviceVendor type for last 30 days in a timechart.

SecurityEvent
| where TimeGenerated >ago (60d)
| summarize count() by Activity,bin(TimeGenerated,1d)
| render timechart 
CommonSecurityLog
| where TimeGenerated > ago(30d)
| summarize Count=count() by DeviceVendor, bin(TimeGenerated, 1d)
| render timechart 

 let Now = now();
(range TimeGenerated from ago(7d) to Now – 1d step 1d
| extend Count = 0
| union isfuzzy=true
    (CommonSecurityLog
    | where DeviceVendor == “Fortinet”
    | where DeviceProduct startswith “Fortigate”
    | summarize Count = count() by bin_at(TimeGenerated, 1d, Now))
| summarize Count=max(Count) by bin_at(TimeGenerated, 1d, Now)
| sort by TimeGenerated
| project Value = iff(isnull(Count), 0, Count), Time = TimeGenerated, Legend = “Fortinet”)
| render timechart 
CommonSecurityLog
| summarize event_count=count() by DeviceVendor
| project DeviceVendor, event_count
| render piechart 
Usage
| where IsBillable == true
| summarize TotalVolumeGB = sum(Quantity) / 1024 by bin(TimeGenerated, 1d), DataType
| render timechart 
This will create a timechart showing the total volume of data ingested per day for the last 30 days, separated by data type.

Barchart

CommonSecurityLog
| where DeviceVendor contains “Forti”
| summarize TotalActivity = count() by Activity, bin(TimeGenerated,1d)
| render timechart 

Extend , Order by, project, join Operator


Extend to create a column
Order by : sort
Project: what you want to see, 
Project-away : what you do not want to see
Project-keep
Project-rename
Project-reorder
join : merges the rows of two tables to form a new table by matching the specified columns’ values from each table

Price Related

Check Table Sizes and if Billable

union withsource=TableName1 *
| where TimeGenerated > ago(1d)
| summarize Entries = count(), Size = sum(_BilledSize), last_log = datetime_diff("second",now(), max(TimeGenerated)), estimate  = sumif(_BilledSize, _IsBillable==true)  by TableName1, _IsBillable
| project ['Table Name'] = TableName1, ['Table Entries'] = Entries, ['Table Size'] = Size,
         ['Size per Entry'] = 1.0 * Size / Entries, ['IsBillable'] = _IsBillable, ['Last Record Received'] =  last_log , ['Estimated Table Price'] =  (estimate/(1024*1024*1024)) * 0.0
| order by ['Table Size']  desc

Calculate Cost Per Table

let rate = 4.30;                                 //<-- Effective per GB Price in EastUS (LAW & Sentinel per GB cost combined)
SecurityEvent                                     //<-- We're querying the SecurityEvent table in this one
| where TimeGenerated >ago(30d)                  //<-- Let's look at the past month, which makes sense considering we're billed monthly
| summarize GB=sum(_BilledSize)/1024/1024/1024    //<-- Summarize billable volume in GB using the _BilledSize table column
| extend cost = GB*rate                          //<-- Multiply total GBs for the month by the effective rate (defined in first line of query)

Check Specific Activities in the Log

 

Check FortiGate priority 1, 2, 3 activities and show the counts for each activity.

let Watchlist = datatable(Priority:string, Activity:string) [
‘1’,’event:system’,
‘1’,’event:user’,
‘1’,’event:user logon’,
‘1’,’event:vpn’,
‘1’,’utm:anomaly’,
‘1’,’utm:dlp’,
‘1’,’utm:dlp dlp-docsource’,
‘1’,’utm:dns’,
‘1’,’utm:dns dns-query’,
‘1’,’utm:dns dns-response’,
‘1’,’utm:emailfilter’,
‘1’,’utm:emailfilter bannedword’,
‘1’,’utm:emailfilter spam’,
‘1’,’utm:emailfilter webmail’,
‘1’,’utm:ips’,
‘1’,’utm:ips botnet’,
‘1’,’utm:ips malicious-url’,
‘1’,’utm:ips signature’,
‘1’,’utm:ssh ssh-channel’,
‘1’,’utm:ssh ssh-command’,
‘1’,’utm:ssh ssh-hostkey’,
‘1’,’utm:waf’,
‘1’,’utm:waf waf-address-list’,
‘1’,’utm:waf waf-custom-signature’,
‘1’,’utm:waf waf-http-constraint’,
‘1’,’utm:waf waf-http-method’,
‘1’,’utm:waf waf-signature’,
‘1’,’utm:waf waf-url-access’,
‘2’,’event:cifs-auth-fail’,
‘2’,’event:endpoint’,
‘2’,’event:rest-api’,
‘2’,’event:router’,
‘2’,’event:sdwan’,
‘2’,’event:sdwan down’,
‘2’,’event:sdwan up’,
‘2’,’event:webproxy’,
‘2’,’event:wireless’,
‘2’,’traffic:forward deny’,
‘2’,’traffic:ztna’,
‘2’,’utm:app-ctrl’ ,
‘2’,’utm:app-ctrl port-violation’,
‘2’,’utm:app-ctrl protocol-violation’,
‘2’,’utm:app-ctrl signature’,
‘2’,’utm:file-filter’,
‘2’,’utm:virus’,
‘2’,’utm:virus analytics’,
‘2’,’utm:virus command-blocked’,
‘2’,’utm:virus content-disarm’,
‘2’,’utm:virus ems-threat-feed’,
‘2’,’utm:virus exempt-hash’,
‘2’,’utm:virus infected’,
‘2’,’utm:virus inline-block’,
‘2’,’utm:virus malware-list’,
‘2’,’utm:virus outbreak-prevention’,
‘2’,’utm:virus oversize’,
‘2’,’utm:voip’,
‘2’,’utm:webfilter’,
‘2’,’utm:webfilter ftgd_blk’,
‘2’,’utm:webfilter ftgd_err’,
‘2’,’utm:webfilter urlfilter’,
‘2’,’utm:webfilter webfilter_command_block’,
‘3’,’event:connector’,
‘3’,’event:fortiextender’,
‘3’,’event:ha’,
‘3’,’event:switch-controller’,
‘3’,’event:wanopt’,
‘3’,’traffic:forward’,
‘3’,’traffic:forward accept’,
‘3’,’traffic:forward client-rst’,
‘3’,’traffic:forward close’,
‘3’,’traffic:forward dns’,
‘3’,’traffic:forward ip-conn’,
‘3’,’traffic:forward server-rst’,
‘3’,’traffic:forward timeout’,
‘3’,’traffic:local’,
‘3’,’traffic:local accept’,
‘3’,’traffic:local client-rst’,
‘3’,’traffic:local close’,
‘3’,’traffic:local deny’,
‘3’,’traffic:local server-rst’,
‘3’,’traffic:local timeout’,
‘3’,’traffic:multicast’,
‘3’,’traffic:sniffer’,
‘3’,’utm:casb’,
‘3’,’utm:emailfilter email’,
‘3’,’utm:emailfilter ftgd_err’,
‘3’,’utm:forti-switch’,
‘3’,’utm:forti-switch fsw-flow’,
‘3’,’utm:gtp’,
‘3’,’utm:gtp gtp-all’,
‘3’,’utm:gtp pfcp-all’,
‘3’,’utm:icap’,
‘3’,’utm:ssl ssl-anomaly’,
‘3’,’utm:ssl ssl-exempt’,
‘3’,’utm:ssl ssl-handshake’,
‘3’,’utm:ssl ssl-negotiation’,
‘3’,’utm:ssl ssl-server-cert-info’,
‘3’,’utm:virtual-patch’,
‘3’,’utm:virus filename’,
‘3’,’utm:virus filetype-executable’,
‘3’,’utm:virus mimefragmented’,
‘3’,’utm:virus scanerror’,
‘3’,’utm:virus switchproto’,
‘3’,’utm:webfilter activexfilter’,
‘3’,’utm:webfilter antiphishing’,
‘3’,’utm:webfilter appletfilter’,
‘3’,’utm:webfilter content’,
‘3’,’utm:webfilter cookiefilter’,
‘3’,’utm:webfilter ftgd_allow’,
‘3’,’utm:webfilter ftgd_quota’,
‘3’,’utm:webfilter ftgd_quota_counting’,
‘3’,’utm:webfilter ftgd_quota_expired’,
‘3’,’utm:webfilter http_header_change’,
‘3’,’utm:webfilter scriptfilter’,
‘3’,’utm:webfilter ssl-exempt’,
‘3’,’utm:webfilter unknown-ce’,
‘3’,’utm:webfilter urlmonitor’,
‘3’,’utm:webfilter videofilter-category’,
‘3’,’utm:webfilter videofilter-channel’,
‘3’,’event:security-rating’
];
CommonSecurityLog
| where DeviceVendor contains “Forti”
| summarize TotalActivity = count() by Activity
| lookup Watchlist on Activity


We can change it to timechart:

CommonSecurityLog
| where DeviceVendor contains “Forti”
| summarize TotalActivity = count() by Activity, bin(TimeGenerated,1d)
| lookup Watchlist on Activity
| render timechart

 

Resources

    Create queries in Microsoft Sentinel using Kusto

    Configure your Microsoft Sentinel environment and Connect logs to Sentinel

    Create detections and perform investigations using Microsoft Sentinel

    Perform threat hunting with Microsoft Sentinel

    Additional Resources

    References

    • https://learn.microsoft.com/en-us/azure/sentinel/kusto-overview

    By netsec

    Related Post

    SIEM

    Sentinel Lab Notes

    Dec 6, 2024 netsec
    SIEM

    How to Surpress Microsoft Sentinel Log Ingestion

    Oct 6, 2024 netsec
    SIEM

    Cisco DUO Connector Issue in Microsoft Sentinel

    Jul 15, 2024 netsec

    Leave a Reply

    You missed

    VPN

    Use V2rayN (V2ray Client) to Protect Your Online Privacy & Bypass Internet Censorship

    2024-12-06 netsec
    Fortigate

    Config Fortigate Traffic Shaping

    2024-12-06 netsec
    Cloud

    Microsoft Intune Deploy Guide and Device Onboarding Process

    2024-12-06 netsec
    Fortigate

    Fortigate NGFW Solution

    2024-12-06 netsec