Trouble Shooting

Review the Defender Advanced Hunting examples for DMARC, SPF and DKIM failures, including the recorded SendGrid and Oliver investigation notes. The queries are diagnostic references, not confirmed incident findings.

Link: Advanced hunting - Microsoft Defender

Using Advanced Hunting Kusto Queries to Find Issues

NOTE: If "ago"(Example: where Timestamp >= ago(14d) ) is used the timeframe used by the query window will be set to "Set in query"

Screenshot retained for review

DMARC FAILS**: WHERE SPF PASS AND DKIM PASS**

• Typically happens with mass emailing systems. Possible that DKIM for the mass emailing system has not been added to the school domain you are trying to send from.
• To check go to Find Schools Missing DKIM for SendGrid

EmailEvents

| where Timestamp >= ago(2d)

| extend AuthDetails = parse_json(AuthenticationDetails)

| extend DMARCTestResult = tostring(AuthDetails.DMARC)

| extend SPFTestResult = tostring(AuthDetails.SPF)

| extend DKIMTestResult = tostring(AuthDetails.DKIM)

| where SPFTestResult == "pass" and DKIMTestResult == "pass"

| where DMARCTestResult == "fail"

| extend SenderDomain = tostring(split(SenderFromAddress, "@")[1])

| where SenderDomain endswith "edu.au"

| extend RecipientDomain = tostring(split(RecipientEmailAddress, "@")[1])

| extend IPv4 = tostring(SenderIPv4)

| summarize EmailCount = count() by DMARCTestResult,SenderDomain,SPFTestResult,DKIMTestResult,IPv4,SenderMailFromAddress

| sort by DMARCTestResult,EmailCount

SPF FAIL FOR OLIVER: Count of Pass/Fails

Getting the list of allow IP senders for Oliver Oliver has 32 IP addresses that are using up

You can use this Write-Host line to create OliverIpAddresses variable that is used at the start of the Kusto Query

Write-Host "let OliverIpAddresses = dynamic([((Get-XSPFIPAddresses -domain "spf.softlinkhosting.com.au" | foreach {Get-XIpRange $_ -quiet -rangeOnly}) -join '", "')])"

Related guidance

• Topic overview
• Source review status