Azure Sentinel Log Query Scripts Collection (Kusto Query Language)

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.

 

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

Draw a Diagram

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 

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

References

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