KQL Search

Query Details

HomeAI ToolsDevice Query

Weekly Email Security Analysis

Query

let lookbackPeriod = 365d; // Define lookback period of 365 days
let startDate = ago(lookbackPeriod);
EmailEvents
| where EmailDirection == "Inbound"
| where TimeGenerated >= startDate
| summarize arg_min(TimeGenerated, *) by NetworkMessageId // Deduplicate based on NetworkMessageId
| extend EmailTimeGenerated = TimeGenerated
| join kind=leftouter (
EmailPostDeliveryEvents
| where TimeGenerated >= startDate
| summarize arg_min(TimeGenerated, *) by NetworkMessageId
| extend FirstUserDetectedThreatTimeGenerated = TimeGenerated
) on NetworkMessageId
| project-away TimeGenerated, TimeGenerated1
| extend WeekStart = startofweek(EmailTimeGenerated), // Ensure this aligns with your week start preference
TimeDeltaUserReported = iif(ActionResult == "UserTriaged", datetime_diff('minute', EmailTimeGenerated, FirstUserDetectedThreatTimeGenerated), int(null)),
TimeDeltaMSDetected = iif(ActionResult == "Success", datetime_diff('minute', EmailTimeGenerated, FirstUserDetectedThreatTimeGenerated), int(null))
| summarize TotalReceived = count(),
Blocked = countif(DeliveryAction == "Blocked" or DeliveryAction == "Junked"),
Allowed = countif(DeliveryAction == "Delivered"),
ThreatDetectedPostDeliveryTotal = countif(ActionResult == "Success" or ActionResult == "UserTriaged"),
ThreatDetectedPostDeliveryUserDetected = countif(ActionResult == "UserTriaged"),
ThreatDetectedPostDeliveryMSDetected = countif(ActionResult == "Success"),
ThreatDetectedPostDeliveryMSDetectedSpam = countif(ActionResult == "Success" and ThreatTypes1 == "Spam"),
ThreatDetectedPostDeliveryMSDetectedPhish = countif(ActionResult == "Success" and ThreatTypes1 == "Phish"),
ThreatDetectedPostDeliveryUserDetectedSpam = countif(ActionResult == "UserTriaged" and ThreatTypes1 == "Spam"),
ThreatDetectedPostDeliveryUserDetectedPhish = countif(ActionResult == "UserTriaged" and ThreatTypes1 == "Phish"),
AverageTimeDeltaUserReported = avg(TimeDeltaUserReported),
AverageTimeDeltaMSDetected = avg(TimeDeltaMSDetected)
by WeekStart
| order by WeekStart asc

Explanation

This query looks at email events from the past year, specifically focusing on inbound emails. It removes duplicate events based on NetworkMessageId and joins them with post-delivery events. It calculates various metrics such as total received emails, blocked emails, allowed emails, and time differences between email detection and user reporting or system detection. It also counts different types of threats detected post-delivery. The results are grouped by week and ordered by the start of each week.

Details

Trevor Hawthorn profile picture

Trevor Hawthorn

Released: June 7, 2024

Tables

EmailEventsEmailPostDeliveryEvents

Keywords

EmailEvents,EmailDirection,TimeGenerated,NetworkMessageId,EmailTimeGenerated,EmailPostDeliveryEvents,FirstUserDetectedThreatTimeGenerated,ActionResult,DeliveryAction,ThreatTypes1,WeekStart,TimeDeltaUserReported,TimeDeltaMSDetected,TotalReceived,Blocked,Allowed,ThreatDetectedPostDeliveryTotal,ThreatDetectedPostDeliveryUserDetected,ThreatDetectedPostDeliveryMSDetected,ThreatDetectedPostDeliveryMSDetectedSpam,ThreatDetectedPostDeliveryMSDetectedPhish,ThreatDetectedPostDeliveryUserDetectedSpam,ThreatDetectedPostDeliveryUserDetectedPhish,AverageTimeDeltaUserReported,AverageTimeDeltaMSDetected.

Operators

letwheresummarizeextendjoinproject-awaycountcountifiifdatetime_diffstartofweekorder by

Actions