Azure/KqlKustoLogs

Examples of Microsoft Azure KQL/Kusto log queries

Azure/KqlKustoLogs/Nginx

Between two spcific local times

let min_t = datetime_local_to_utc(datetime(2023-06-20 07:26:00), 'Pacific/Auckland');
let max_t = datetime_local_to_utc(datetime(2023-06-20 07:36:00), 'Pacific/Auckland');
f_table
| where TimeGenerated  between (min_t .. max_t)

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

Join old and new Computer table

//join InsightsMetrics and Perf
let view_insight = view() {
InsightsMetrics
| where Namespace == "Memory"
| where Name == "AvailableMB"
| extend Tags = parse_json(Tags)
| extend memAvailableMB_ = Val
| extend memAvailableSizeMB_ = tostring(Tags.["vm.azm.ms/memorySizeMB"])
| extend memPercentage = toreal(memAvailableMB_) / toreal(memAvailableSizeMB_) * 100.0
| project TimeGenerated, memPercentage, Computer
| summarize max(memPercentage) by bin(TimeGenerated, 1m), Computer
};
let view_perf = view() {
    Perf
    | where ObjectName == "Memory" and CounterName == "% Used Memory"
    | extend memPercentage = toreal(CounterValue)
    | project TimeGenerated, memPercentage, Computer
    | summarize max(memPercentage) by bin(TimeGenerated, 1m), Computer
    };
union withsource="unionTable" view_insight, view_perf
| project Computer, max_memPercentage, TimeGenerated
| order by TimeGenerated asc
| 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