SHIFT

--- Sjoerd Hooft's InFormation Technology ---

User Tools

Site Tools


Sidebar

Recently Changed Pages:

View All Pages


View All Tags


LinkedIn




WIKI Disclaimer: As with most other things on the Internet, the content on this wiki is not supported. It was contributed by me and is published “as is”. It has worked for me, and might work for you.
Also note that any view or statement expressed anywhere on this site are strictly mine and not the opinions or views of my employer.


Pages with comments

View All Comments

azurekusto

Kusto Queries in Azure

This is an overview of Kusto Queries I use in Azure.

Structure

Expression Description Example
== Check equality (case-sensitive) Level == 8
=~ Check equality (case-insensitive) EventSourceName =~ "microsoft-windows-security-auditing"
!=, <> Check inequality (both expressions are identical) Level != 4
and or Required between conditions Level == 16 or CommandLine != ""
Command Description Example
take *n* Ideally suited to small result sets. Take returns n rows from the result set in no particular order. AuditLogs | Take 10
top *n* by *field* Use this filter command to return the top n rows, sorted by the nominated field. AuditLogs | Top 10 by timeGenerated
sort by *field* (desc) If you want to sort only the result set, you can use the sort command. You need to specify the field to sort on, and then you can optionally add the desc instruction to specify a descending sort pattern. AuditLogs | Sort by timeGenerated desc
Where field (expression) value The principal filtering command. You nominate the field, expression, and comparator value. You can stack multiple where commands, each separated by a pipe. AuditLogs | where CreatedDateTime >= ago(2d)
project fields If you want to restrict the result set to display only nominated fields or columns, you can use the project command with a comma-separated list of the fields. AuditLogs | project timeGenerated, OperationName, ResourceGroup, Result

Other pages with Kusto

Log Analytics

Go to Azure → Log Analytics → Choose Log Analytics Workspace → Logs → Enter query

Failed Login Events VM

SecurityEvent
| where EventID = 4625
| summarize count() by TargetAccount

Users Flagged as Risky

See how many of your users were flagged as risky in the last 14 days:

SigninLogs
| where CreatedDateTime >= ago(14d)
| where isRisky = true

Errors from Server Eventlog

Event
| where TimeGenerated > ago(24h) and EventLevel == 1 and EventLog == 'Application'
| where Computer contains 'SRV01.SHIFT.LOCAL'
| limit 100

Errors from Server Eventlog - Search for service errors in time range

Event 
| where TimeGenerated > startofday(datetime("2022-01-02")) and TimeGenerated < endofday(datetime("2022-01-04")) and EventLevel == 1 and EventLog == 'Application'
| where Computer contains 'appprd01.shift'
| where RenderedDescription contains "shift.ServiceName"

With eventlevel 1 or 2

Event
| where TimeGenerated between (datetime('2022-02-17 11:25')..datetime('2022-02-17 11:31'))
| where EventLevel <= 2

Searching for Crashed Service

Event
| where RenderedDescription  contains "coreServiceShell.exe"
| summarize count() by bin(TimeGenerated, 1d), Computer
| order by TimeGenerated

RabbitMQ Missed Heartbeats

RabbitMQ_CL
| where RawData contains "missed heartbeats"
| summarize count() by Computer, bin(TimeGenerated, 1d)
| order by TimeGenerated

RabbitMQ Errors

RabbitMQ_CL
| where LogType_CF == '[error]'

In Timeframe:

RabbitMQ_CL
| where DateTime_CF between (datetime('2022-07-10 10:25')..datetime('2022-07-11 12:31')) and LogType_CF == '[error]'

RabbitMQ Errors per Server per Day

RabbitMQ_CL
| where LogType_CF == '[error]'
| summarize count() by Computer, bin(TimeGenerated, 1d)
| order by TimeGenerated

Application Insights

Go to Azure → Application Insights → Choose AppInsights Scope → Logs → Enter query

Find all errors of cloud_rolename

union exceptions, traces
| where cloud_RoleName == "Shift.Service"
| order by timestamp desc

Azure DevOps Audit Stream

yaml and classic release that were not successful

AzureDevOpsAuditing
| where OperationName == "Pipelines.DeploymentJobCompleted" or OperationName == "Release.DeploymentCompleted"
| where Data.DeploymentResult != "Succeeded"
| extend deployment = strcat(Data.PipelineName, "-", Data.StageName)
| project TimeGenerated, deployment
| summarize count() by bin(TimeGenerated, 10m), deployment
| render columnchart

yaml releases to production that were successful

AzureDevOpsAuditing
| where OperationName == "Pipelines.DeploymentJobCompleted" and Data.DeploymentResult == "Succeeded" and Data.StageName contains "Production"
| extend deployment = strcat(Data.PipelineName, "-", Data.StageName)
| project TimeGenerated, deployment
| summarize count() by bin(TimeGenerated, 1d), deployment
| render columnchart

classis releases that were not successful

AzureDevOpsAuditing
| where OperationName == "Release.DeploymentCompleted" and Data.DeploymentResult != "Succeeded"
| extend deployment = strcat(Data.PipelineName, "-", Data.StageName)
| project TimeGenerated, deployment
| summarize count() by bin(TimeGenerated, 1d), deployment
| render columnchart
You could leave a comment if you were logged in.
azurekusto.txt · Last modified: 2023/08/09 21:35 by sjoerd