Microsoft Entra Suite Tech Accelerator
Aug 14 2024, 07:00 AM - 09:30 AM (PDT)
Microsoft Tech Community
SOLVED

Advanced hunting Query to get unique Email Sender IP details

Copper Contributor

Hello All, Am trying to get unique SenderIPv4 (under email and collaboration) information but am unable to find any query for that ! Anyone can help me in this ?

 

3 Replies
Hi , you need to get the IP address of an external sender ?
I need to get all the unique IPs of sender from particular domain
best response confirmed by dhilipan (Copper Contributor)
Solution

@dhilipan

 

Well, getting the IPs tabularised is simplicity itself:

 

EmailEvents
| where SenderFromDomain == "suspect.tld"
| where Timestamp > ago(30d)
| project SenderIPv4
| summarize count() by SenderIPv4
| sort by SenderIPv4 asc

 

This only covers you for IP v4, but I see that there is now a schema entry for IP v6. If the domain is sending you any IPv6 then you can experiment. If you don't know, find a domain that does send IPv6 to you then try SenderIPv6 in place of SenderIPv4.

 

Once you have some likely ranges, you may want to see who else is using them. Kudos to forum members who recognise the following ranges, and no it's not the entirety of Spam Enabling Services:

 

let timeframe = ago(7d);
let ip_data = datatable(network:string)
[
"158.247.16.0/20",
"143.244.80.0/24",
"139.180.17.0/24",
"54.174.56.0/21",
"54.174.52.0/21",
"3.210.190.0/24",
];
EmailEvents
| where Timestamp > timeframe
| where EmailDirection == "Inbound"
| project SenderIPv4, SenderFromAddress, SenderFromDomain, SenderMailFromDomain, Subject
| evaluate ipv4_lookup(ip_data, SenderIPv4, network)
| summarize count () by network, SenderMailFromDomain, SenderFromDomain
| sort by count_ desc

 

Note that the first query covers the maximum of 30 days (which is still a limitation, and if you need more then you are going to have to run a message trace on the domain instead) but the second query only covers 7 days. Remember that Advanced Hunting queries are limited by memory, and if you ask for too much then they may give incomplete answers rather than fail outright.

1 best response

Accepted Solutions
best response confirmed by dhilipan (Copper Contributor)
Solution

@dhilipan

 

Well, getting the IPs tabularised is simplicity itself:

 

EmailEvents
| where SenderFromDomain == "suspect.tld"
| where Timestamp > ago(30d)
| project SenderIPv4
| summarize count() by SenderIPv4
| sort by SenderIPv4 asc

 

This only covers you for IP v4, but I see that there is now a schema entry for IP v6. If the domain is sending you any IPv6 then you can experiment. If you don't know, find a domain that does send IPv6 to you then try SenderIPv6 in place of SenderIPv4.

 

Once you have some likely ranges, you may want to see who else is using them. Kudos to forum members who recognise the following ranges, and no it's not the entirety of Spam Enabling Services:

 

let timeframe = ago(7d);
let ip_data = datatable(network:string)
[
"158.247.16.0/20",
"143.244.80.0/24",
"139.180.17.0/24",
"54.174.56.0/21",
"54.174.52.0/21",
"3.210.190.0/24",
];
EmailEvents
| where Timestamp > timeframe
| where EmailDirection == "Inbound"
| project SenderIPv4, SenderFromAddress, SenderFromDomain, SenderMailFromDomain, Subject
| evaluate ipv4_lookup(ip_data, SenderIPv4, network)
| summarize count () by network, SenderMailFromDomain, SenderFromDomain
| sort by count_ desc

 

Note that the first query covers the maximum of 30 days (which is still a limitation, and if you need more then you are going to have to run a message trace on the domain instead) but the second query only covers 7 days. Remember that Advanced Hunting queries are limited by memory, and if you ask for too much then they may give incomplete answers rather than fail outright.

View solution in original post