KQL Techniques For Email URL Redirect Hunting

Query

// Sergio Albea 07/12/2025 ©️
let TrackingDomains = dynamic(["click/","bit.ly","bitly.com","tinyurl.com","goo.gl","t.co","ow.ly","is.gd","buff.ly","lnk.click","lnk.to","mjt.lu","mailjet.com","list-manage.com","mailchimp.com","mcsv.net","mailgun.org","sendgrid.net","sendgrid.com","createsend.com","createsend1.com","rs6.net","campaignsapp.com","mlsend.com","sendinblue.com","sibautomation.com","mailerlite.com","hs-sites.com","hscta.net","hubspot.com","hubspotlinks.com","mkto.com","mktg.com","pardot.com","eloqua.com","eloquaservice.com","activehosted.com","acems.ac","getvero.com","iterable.com","inflection.io","bnc.lt","app.link","substack.com","substackcdn.com","getrevue.co","shop.app","shopifyemail.com","klaviyo-mail.com","klaviyo.com","knowbe4.com","kb4.io","phishme.com","cofense.com"]);
EmailEvents
| where Timestamp > ago(1d)
| extend IPSender = iff(isnotempty( SenderIPv4),SenderIPv4,SenderIPv6)
| extend GeoIPData = tostring(geo_info_from_ip_address(IPSender).country)
| join kind=inner ( EmailUrlInfo  | where isnotempty(UrlChainId)) on NetworkMessageId
// Pivot URLs by position
| summarize
    UrlPos_0   = anyif(Url, UrlChainPosition == 0),UrlPos_1= anyif(Url, UrlChainPosition == 1),UrlPos_2= anyif(Url, UrlChainPosition == 2),UrlPos_3   = anyif(Url, UrlChainPosition == 3),
    IP_Url_0   = anyif(IPSender, UrlChainPosition == 0),IP_Url_1= anyif(IPSender, UrlChainPosition == 1),IP_Url_2= anyif(IPSender, UrlChainPosition == 2),IP_Url_3= anyif(IPSender, UrlChainPosition == 3),
    C_Url_0   = anyif(GeoIPData, UrlChainPosition == 0),C_Url_1= anyif(GeoIPData, UrlChainPosition == 1),C_Url_2= anyif(GeoIPData, UrlChainPosition == 2),C_Url_3= anyif(GeoIPData, UrlChainPosition == 3),
    URL_Redirections  = dcount(UrlChainPosition),Senders=dcount(SenderFromAddress),Recipients = dcount(RecipientEmailAddress),IP_Country= make_set(strcat(SenderIPv4, " ", GeoIPData)) by UrlChainId,ThreatTypes
| where isnotempty(UrlPos_0)
| extend Tracking_Marketing_URL0 = (iff(UrlPos_0 has_any (TrackingDomains),"Yes","Not")),Tracking_Marketing_URL1 =(iff(UrlPos_1 has_any (TrackingDomains),"Yes","Not")),Tracking_Marketing_URL2 = (iff(UrlPos_2 has_any (TrackingDomains),"Yes","Not")),Tracking_Marketing_URL3 = (iff(UrlPos_3 has_any (TrackingDomains),"Yes","Not"))
| summarize  by Senders,URL_Redirections,UrlPos_0,IP_Url_0,C_Url_0,Tracking_Marketing_URL0, UrlPos_1,IP_Url_1,C_Url_1,Tracking_Marketing_URL1, UrlPos_2,IP_Url_2,C_Url_2,Tracking_Marketing_URL2, UrlPos_3,IP_Url_3,C_Url_3,Tracking_Marketing_URL3,Recipients,tostring(IP_Country),UrlChainId,ThreatTypes
| extend Domain_URL0 = tostring(parse_url(UrlPos_0).Host), Domain_URL1 = tostring(parse_url(UrlPos_1).Host), Domain_URL2 = tostring(parse_url(UrlPos_2).Host), Domain_URL3 = tostring(parse_url(UrlPos_3).Host)
| extend Info="ℹ️",0_ = "0️⃣",1_ = "1️⃣",2_ = "2️⃣",3_ ="3️⃣" //Icons
//Search for specific URL | extend URL = "office" | where UrlPos_0 contains URL or UrlPos_1 contains URL or UrlPos_2 contains URL or UrlPos_3 contains URL
//Search for URL on distinct countries | where (C_Url_0 !has C_Url_1 or C_Url_0 !has C_Url_2 or C_Url_0 !has C_Url_3 or C_Url_1 !has C_Url_2 or C_Url_1 !has C_Url_3 or C_Url_2 !has C_Url_3) 
//Search for URL that contains specific countries | where   (C_Url_0  !in ("Switzerland","Germany")) or (C_Url_1 in ("Russia","Singapore"))
//Search for URL on distinct domains | where (Domain_URL0 !has Domain_URL1 or Domain_URL0 !has Domain_URL1 or Domain_URL0 !has Domain_URL3 or Domain_URL1 !has Domain_URL2 or Domain_URL1 !has Domain_URL3 or Domain_URL2 !has Domain_URL3) 
//Search for number of redirections | where URL_Redirections > 2
| project  Info,URL_Redirections,ThreatTypes,Senders, Recipients,0_,Domain_URL0,UrlPos_0, IP_Url_0,C_Url_0, Tracking_Marketing_URL0,1_,Domain_URL1,UrlPos_1,IP_Url_1,C_Url_1,Tracking_Marketing_URL1,2_,Domain_URL2,UrlPos_2,IP_Url_2,C_Url_2, Tracking_Marketing_URL2,3_,Domain_URL3,UrlPos_3,IP_Url_3,C_Url_3,Tracking_Marketing_URL3
| order by ThreatTypes

About this query

Explanation

This KQL (Kusto Query Language) query is designed to analyze email URLs and their redirection paths to identify potential phishing attempts, specifically spearphishing links. Here's a simplified breakdown of what the query does:

Purpose: The query helps track and analyze URL redirections in emails to identify suspicious activities, such as phishing attempts. It focuses on URLs that redirect through multiple steps before reaching their final destination.
URL Chain Analysis:
- It uses a unique identifier, UrlChainId, to group URLs that belong to the same redirection chain.
- Each URL in the chain is assigned a position, starting from 0 for the original URL and increasing for each subsequent redirection.
Classification: The query categorizes the URLs into four main insights:
- Tracking Domains: Identifies if the URLs are associated with known tracking or marketing domains.
- Redirecting to Different Countries: Checks if the redirections lead to different countries.
- Redirecting to Different Domains: Analyzes if the redirections lead to different domains.
- Number of Redirections: Counts how many redirections occur in the chain.
Data Extraction:
- It extracts information such as sender IP, country, and email addresses.
- It summarizes the data by URL chain, showing the original URL and each redirection step, along with associated metadata like sender and recipient details.
Filtering and Searching:
- The query includes commented-out sections that allow for specific searches, such as looking for URLs containing certain keywords, URLs redirecting to specific countries, or chains with a high number of redirections.
Output: The final output is a structured table displaying the analyzed data, including the number of redirections, threat types, sender and recipient information, and details about each URL in the chain.

Overall, this query is a tool for cybersecurity analysts to detect and investigate potential phishing threats by examining the behavior of URLs in emails.

Details

Sergio Albea

Released: July 21, 2026

Tables

EmailEventsEmailUrlInfo

Keywords

EmailUrlRedirectionsTrackingDomainsGeoIPDataThreatTypesSendersRecipientsUrlChainIdUrlChainPositionUrlChainInfo

Operators

letdynamicagoiffisnotemptytostringgeo_info_from_ip_addressjoinsummarizeanyifdcountmake_setstrcathas_anyparse_urlextendprojectorder by

MITRE Techniques

T1566.002

Actions

GitHub

KQL Search