KQL Cafe - April 2024

Recording and Presentation

Hosts

Gianni
Alex

Guests

Henning Rauch

News

Introduction to KQL for Security Analysis
Hands-On KQL for Security Analysts
Slim's Elite KQL Detection & Cyber Defense Tips
The Definitive Guide to KQL: Using Kusto Query Language for Operations, Defending and Threat Hunting
Strategies to monitor and prevent vulnerable driver attacksUseful MDE queries
Defender for Cloud (CSPM) (Jarkko Kinnunen) KQL for Pricing
CloudAppEvents New columns LastSeenForUser - Shows how many days back the attribute was recently in use by the user in days (i.e. ISP, ActionType etc.) UncommonForUser - Lists the attributes in the event that are uncommon for the user, using this data to help rule out false positives and find out anomalies

Our Guest

Learn KQL - Series

SigninLogs
| make-series Logons=count() default=0 on TimeGenerated from ago(14d) to now() step 1h
| extend series_sum=series_sum(Logons)

SigninLogs
| make-series Logons=count() default=0 on TimeGenerated from ago(14d) to now() step 1h
| project series_stats(Logons)

SigninLogs
| make-series Logons=count() default=0 on TimeGenerated from ago(14d) to now() step 1h
| extend series_decompose(Logons)

SigninLogs
| make-series Logons=count() default=0 on TimeGenerated from ago(14d) to now() step 1h
| extend series_decompose(Logons)
| render timechart

SigninLogs
| make-series Logons=count() default=0 on TimeGenerated from ago(14d) to now() step 1h
| extend outliers=series_outliers(Logons)
| render timechart

SigninLogs
| where TimeGenerated > ago(14d)
| make-series Logons=count() default=0 on TimeGenerated from ago(14d) to now() step 1h
| extend series_decompose_anomalies(Logons)

SigninLogs
| make-series Logons=count() default=0 on TimeGenerated from ago(14d) to now() step 1h
| extend series_decompose(Logons)
| render timechart

SigninLogs
| where TimeGenerated > ago(14d)
| make-series Logons=count() default=0 on TimeGenerated from ago(14d) to now() step 1h
| extend series_decompose_anomalies(Logons)

SigninLogs
| where TimeGenerated > ago(14d)
| make-series Logons=count() default=0 on TimeGenerated from ago(14d) to now() step 1h
| extend (AnomaliesDetected, AnomaliesScore, AnomaliesBaseline) = series_decompose_anomalies(Logons)
| mv-expand Logons to typeof(double), TimeGenerated to typeof(datetime), AnomaliesDetected to typeof(double), AnomaliesScore to typeof(double), AnomaliesBaseline to typeof(long)
| render timechart

SigninLogs
| where TimeGenerated > ago(14d)
| make-series Logons=count() default=0 on TimeGenerated from ago(14d) to now() step 1h
| extend (AnomaliesDetected, AnomaliesScore, AnomaliesBaseline) = series_decompose_anomalies(Logons)
| mv-expand Logons to typeof(double), TimeGenerated to typeof(datetime), AnomaliesDetected to typeof(double), AnomaliesScore to typeof(double), AnomaliesBaseline to typeof(long)
| extend AnomaliesDetected = AnomaliesDetected * (AnomaliesBaseline*2)
| render timechart

SigninLogs
| where TimeGenerated > ago(14d)
| make-series Logons=count() default=0 on TimeGenerated from ago(14d) to now() step 1h
| extend (AnomaliesDetected, AnomaliesScore, AnomaliesBaseline) = series_decompose_anomalies(Logons, 1.5, 24, "linefit",0,"tukey")
| mv-expand Logons to typeof(double), TimeGenerated to typeof(datetime), AnomaliesDetected to typeof(double), AnomaliesScore to typeof(double), AnomaliesBaseline to typeof(long)
| extend AnomaliesDetected = AnomaliesDetected * (AnomaliesBaseline*2)
| render timechart

SigninLogs
| where TimeGenerated > ago(14d)
| make-series Logons=count() default=0 on TimeGenerated from ago(14d) to now() step 1h
| extend (AnomaliesDetected, AnomaliesScore, AnomaliesBaseline) = series_decompose_anomalies(Logons, 1.5, 24, "linefit",0,"ctukey",0.6)
| mv-expand Logons to typeof(double), TimeGenerated to typeof(datetime), AnomaliesDetected to typeof(double), AnomaliesScore to typeof(double), AnomaliesBaseline to typeof(long)
| extend AnomaliesDetected = AnomaliesDetected * (AnomaliesBaseline*2)
| render timechart

What did you do with KQL this month

Azure Files

StorageFileLogs
| where Uri contains “SuspiciousFilename.txt"
| where Category == "StorageWrite" and
    OperationName == "Write" and 
    StatusCode == "0"
| project-reorder TimeGenerated, LastModifiedTime, SmbPrimarySID, CallerIpAddress

StorageFileLogs
| where TimeGenerated > ago(90d)
| where _IsBillable == true
| summarize TotalVolumeGBLog = round(sum(_BilledSize/1024/1024/1024),2)  by bin(TimeGenerated, 1d) 
// Sum all
| summarize sum(TotalVolumeGBLog)