Azure/KqlKustoLogs

• Examples of Microsoft Azure KQL/Kusto log queries

  • Azure/KqlKustoLogs/Nginx

  • Regex extract var(err) and bin for chart

    | extend err = extract("(WSREP( [[:alpha:]]+)+)", 1, Message)
    | summarize AggregatedValue = count() by bin(TimeGenerated, 1s), err

• Join two metrics

AzureDiagnostics 
| where TimeGenerated > ago(1day)
| where Category == "ApplicationGatewayAccessLog"
//| where httpStatus_d == 429
| summarize count_apgw_1m=count() by bin(TimeGenerated, 1m) , "AppGW"
| order by TimeGenerated asc
| join kind=inner
    (
    f_services_CL
| where tag_s == "loadavg" and hostname_s contains "tm-prd-db"
| extend LoadAvg = todecimal(loadavg_s)
| summarize load=avg(LoadAvg)*10000 by bin (TimeGenerated, 1m), hostname_s
| order by TimeGenerated asc
    ) on TimeGenerated
| project TimeGenerated, count_apgw_1m, load
| render timechart

let timeOffset = 7d;
let discardEventId = 4688;
SecurityEvent
| where TimeGenerated > ago(timeOffset*2) and TimeGenerated < ago(timeOffset)
| where EventID != discardEventId

let suspiciousAccounts = datatable(account: string) [
    @"\administrator", 
    @"NT AUTHORITY\SYSTEM"
];
SecurityEvent | where Account in (suspiciousAccounts)

let LowActivityAccounts =
    SecurityEvent 
    | summarize cnt = count() by Account 
    | where cnt < 10;
LowActivityAccounts | where Account contains "Mal"

search "err"

search in (SecurityEvent,SecurityAlert,A*) "err"

SecurityEvent
| where TimeGenerated > ago(1h)
| where EventID == 4624
| where AccountType =~ "user"

SecurityAlert
| where TimeGenerated > ago(7d)
| extend severityOrder = case (
    AlertSeverity == "High", 3,
    AlertSeverity == "Medium", 2, 
    AlertSeverity == "Low", 1,
    AlertSeverity == "Informational", 0,
    -1)

let timeframe = 1d;
let DomainList = dynamic(["tor2web.org", "tor2web.com"]);
Syslog
| where TimeGenerated >= ago(timeframe)
| where ProcessName contains "squid"
| extend 
  HTTP_Status_Code = extract("(TCP_(([A-Z]+)…-9]{3}))",8,SyslogMessage),    
  Domain = extract("(([A-Z]+ [a-z]{4…Z]+ )([^ :\\/]*))",3,SyslogMessage)
| where HTTP_Status_Code == "200"
| where Domain contains "."
| where Domain has_any (DomainList)

SecurityAlert
| where TimeGenerated > ago(7d)
| extend severityOrder = case (
    AlertSeverity == "High", 3,
    AlertSeverity == "Medium", 2, 
    AlertSeverity == "Low", 1,
    AlertSeverity == "Informational", 0,
    -1)
| order by severityOrder desc

SecurityEvent
| where EventID == "4688"
| summarize cnt=count() by Process, Computer

• count uniq ip's

SecurityEvent
| summarize dcount(IpAddress)

• failed logins to disabled accounts, summarisze by User

let timeframe = 1d;
let threshold = 3;
SigninLogs
| where TimeGenerated >= ago(timeframe)
| where ResultType == "50057"
| where ResultDescription =~ "User account is disabled. The account has been disabled by an administrator."
| summarize applicationCount = dcount(AppDisplayName) by UserPrincipalName, IPAddress
| where applicationCount >= threshold

SecurityEvent
| where EventID == "4624"
| summarize make_list(Account) by Computer

SecurityEvent 
| summarize count() by Account
| render barchart

SecurityEvent 
| summarize count() by bin(TimeGenerated, 1h) 
| render timechart

SecurityEvent 
| union (SecurityAlert | summarize count()) 
| project count_

SecurityEvent | union (SecurityAlert | summarize count()) | project count_

let SQlData = Event
| where Source has "MSSQL"
;
let Sqlactivity = SQlData
| where RenderedDescription !has "LGIS" and RenderedDescription !has "LGIF"
| parse RenderedDescription with * "action_id:" Action:string 
                                    " " * 
| parse RenderedDescription with * "client_ip:" ClientIP:string
" permission" * 
| parse RenderedDescription with * "session_server_principal_name:" CurrentUser:string
" " * 
| parse RenderedDescription with * "database_name:" DatabaseName:string
"schema_name:" Temp:string
"object_name:" ObjectName:string
"statement:" Statement:string
"." *
;
let FailedLogon = SQlData
| where EventLevelName has "error"
| where RenderedDescription startswith "Login"
| parse kind=regex RenderedDescription with "Login" LogonResult:string
                                            "for user '" CurrentUser:string 
                                            "'. Reason:" Reason:string 
                                            "provided" *
| parse kind=regex RenderedDescription with * "CLIENT" * ":" ClientIP:string 
                                            "]" *
;
let dbfailedLogon = SQlData
| where RenderedDescription has " Failed to open the explicitly specified database" 
| parse kind=regex RenderedDescription with "Login" LogonResult:string
                                            "for user '" CurrentUser:string 
                                            "'. Reason:" Reason:string 
                                            " '" DatabaseName:string
                                            "'" *
| parse kind=regex RenderedDescription with * "CLIENT" * ":" ClientIP:string 
                                            "]" *
;
let successLogon = SQlData
| where RenderedDescription has "LGIS"
| parse RenderedDescription with * "action_id:" Action:string 
                                    " " LogonResult:string 
                                    ":" Temp2:string
                                    "session_server_principal_name:" CurrentUser:string
                                    " " *
| parse RenderedDescription with * "client_ip:" ClientIP:string 
                                    " " *
;
(union isfuzzy=true
Sqlactivity, FailedLogon, dbfailedLogon, successLogon )
| project TimeGenerated, Computer, EventID, Action, ClientIP, LogonResult, CurrentUser, Reason, DatabaseName, ObjectName, Statement

• Extract fields from log_s msg, also use parse to grab end of log_s select statement

  //# https://github.com/google/re2/wiki/Syntax
  f_mariadb
  | where TimeGenerated > ago(24h)
  //| where hostname_s == "prd-db01"
  | extend Fscan = extract("# Full_scan: ([^ ]+) ",1,log_s)
  | extend QT = round( todecimal( extract("# Query_time: ([0-9]+.[0-9]+) ",1,log_s) ), 1)
  | extend Rows_sent = extract(" Rows_sent: ([^ ]+) ",1,log_s)
  | extend Rows_examined = extract(" Rows_examined: ([^ ]+) ",1,log_s)
  | parse kind=regex flags=s log_s with * "(SELECT|select) " SQL:string
  | summarize Count=count(), TTotal = sum(QT) , TAvg = round(avg(QT),1) by SQL , Fscan, hostname_s
  | sort by Count, TAvg
  | limit 50
  | project Count, Fscan, TAvg, SQL , hostname_s

• kql parse mariadb wsrep logs

  | extend wsrep_Target = iff( wsrep_Target == "", extract(" tcp://([^ ]+)",1,SyslogMessage) ,wsrep_Target)

• kql query size of tables in GB

  Usage 
  | where TimeGenerated > ago(92d)
  | where StartTime >= now(-91d) and EndTime < now()
  | where IsBillable == true
  | summarize BillableDataGB = round(sum(Quantity) / 1000.,2) by DataType, Solution
  | sort by BillableDataGB desc

CategoryLogging