Created
November 5, 2025 16:41
-
-
Save azurekid/9b1d105bc747e5a957aa5283c5cfd5f2 to your computer and use it in GitHub Desktop.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| // Alerts in last 24h | |
| let notJunkAlerts = | |
| AlertInfo | |
| | where Title == "Email reported by user as not junk" | |
| and TimeGenerated >= ago(1h) | |
| | project AlertId; | |
| let evidence = | |
| AlertEvidence | |
| | where AlertId in (notJunkAlerts) | |
| and isnotempty(NetworkMessageId) | |
| | project AlertId, NetworkMessageId; | |
| let currentEmails = | |
| EmailEvents | |
| | where NetworkMessageId in (evidence | project NetworkMessageId) | |
| | join kind=inner (evidence) on NetworkMessageId | |
| | extend SenderDomain = tostring(split(SenderFromAddress, "@")[1]) | |
| | project | |
| AlertId | |
| , NetworkMessageId | |
| , CurrentTime=TimeGenerated | |
| , CurrentThreat=ThreatTypes | |
| , CurrentDelivery=DeliveryAction | |
| , SenderFromAddress | |
| , SenderDomain | |
| , RecipientEmailAddress | |
| , Subject | |
| ; | |
| let lookback = | |
| EmailEvents | |
| | where TimeGenerated >= ago(30d) | |
| and ThreatTypes has_any ("Phish","Spam","Malware") | |
| | extend SenderDomain = tostring(split(SenderFromAddress, "@")[1]) | |
| | where | |
| NetworkMessageId in (evidence | project NetworkMessageId) | |
| or | |
| SenderDomain in (currentEmails | project SenderDomain) | |
| | summarize | |
| HasPrevThreat = any(isnotempty(ThreatTypes)) | |
| , FirstSeen=min(TimeGenerated) | |
| , LastSeen=max(TimeGenerated) | |
| by NetworkMessageId, SenderDomain | |
| ; | |
| currentEmails | |
| | join kind=leftouter (lookback) on NetworkMessageId, SenderDomain | |
| | extend | |
| CurrentThreatNorm = iff(isempty(CurrentThreat) or CurrentThreat == "", "", CurrentThreat) | |
| | extend HasPrevThreat = iff(isnull(HasPrevThreat), false, HasPrevThreat) | |
| | extend RiskLevel = case( | |
| HasPrevThreat == true, "HighRisk", | |
| CurrentThreatNorm == "" and HasPrevThreat == false, "SafeToClose", | |
| "NeedsReview") | |
| | summarize arg_max(CurrentTime, *) by AlertId, NetworkMessageId | |
| | project | |
| AlertId | |
| , NetworkMessageId | |
| , Subject | |
| , SenderFromAddress | |
| , SenderDomain | |
| , RecipientEmailAddress | |
| , CurrentTime | |
| , CurrentThreatNorm | |
| , CurrentDelivery | |
| , HasPrevThreat, FirstSeen, LastSeen, RiskLevel | |
| | order by CurrentTime desc |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment