kqlmicrosoft365-defender

Defender Advance Query

I was looking for some advice or help on my query. Basically I am trying to create a query to detect possible anomalies against emails attachment from sender rending it into a linechart. For some reason it's not letting me render it (| render linechart). Can't figure for the life of me, but the resulted fine without chart. Thanks a head of time.

let interval = 12h;
// Anomaly threshold
let AnomalyThreshold = 1;
//Distinct Sender Threshold
let SenderThreshold = 5;
EmailAttachmentInfo
// Make a series based on Distinct Mail by file types
| make-series DistinctMailCount = count() on Timestamp from ago(10d) to now() step interval by FileType , SenderFromAddress
// Do anomaly detection on DistinctMailCount 
| extend (AnomaliesDetected, AnomaliesScore, AnomaliesBaseline) = series_decompose_anomalies(DistinctMailCount, AnomalyThreshold, -1, 'linefit')
// Place all the items on a single line
| mv-expand DistinctMailCount to typeof(double), Timestamp to typeof(datetime), AnomaliesDetected to typeof(double), AnomaliesScore to typeof(double), AnomaliesBaseline to typeof(long)
// Show all rows with a detected anomaly and where the threshold is higher than DeviceThreshold
| where AnomaliesDetected == 1 and DistinctMailCount >= SenderThreshold
| summarize count() by SenderFromAddress, bin(Timestamp, interval), FileType
Solution

  • Maybe this helps:

    let interval = 1h;
let AnomalyThreshold = 1;
let SenderThreshold = 1;
let EmailAttachmentInfo = datatable (SenderFromAddress:string, Timestamp:datetime, FileType:string, DistinctMailCount:long)
[
    'sender1@example.com', datetime(2024-07-01T00:00:00Z), 'pdf', 6,
    'sender2@example.com', datetime(2024-07-01T12:00:00Z), 'docx', 8,
    'sender3@example.com', datetime(2024-07-02T00:00:00Z), 'xlsx', 7,
    'sender1@example.com', datetime(2024-07-02T12:00:00Z), 'pdf', 10,
    'sender2@example.com', datetime(2024-07-03T00:00:00Z), 'docx', 15,
    'sender3@example.com', datetime(2024-07-03T12:00:00Z), 'xlsx', 12,
    'sender4@example.com', datetime(2024-07-04T00:00:00Z), 'pptx', 6,
    'sender5@example.com', datetime(202a4-07-04T12:00:00Z), 'jpg', 9,
    'sender1@example.com', datetime(2024-07-05T00:00:00Z), 'pdf', 11,
    'sender2@example.com', datetime(2024-07-05T12:00:00Z), 'docx', 14
];
EmailAttachmentInfo
| where SenderFromAddress !has "lowes.com"
| make-series DistinctMailCount = count() on Timestamp from ago(20d) to now() step interval by FileType, SenderFromAddress
| extend (AnomaliesDetected, AnomaliesScore, AnomaliesBaseline) = series_decompose_anomalies(DistinctMailCount, AnomalyThreshold, -1, 'linefit')
| mv-expand DistinctMailCount to typeof(double), Timestamp to typeof(datetime), AnomaliesDetected to typeof(double), AnomaliesScore to typeof(double), AnomaliesBaseline to typeof(long)
| where AnomaliesDetected == 1 and DistinctMailCount >= SenderThreshold
| summarize count() by SenderFromAddress, bin(Timestamp, interval), FileType
| render columnchart

    I think columnchart looks better.

    enter image description here

    Sample code can be found here.