#logtype = "microsoft365"
| #category = "AdvancedHunting-EmailUrlInfo"
| ioc:lookup(["properties.Url"], type="url", strict="true", confidenceThreshold=?threshold)
| $"microsoft/microsoft365:IOC age and confidence presentation"()
| groupBy([properties.Url, properties.NetworkMessageId], function=[selectLast([@timestamp, "Malicious confidence", "IOC age"])])
| join({
    #logtype = "microsoft365"
    | #category = "AdvancedHunting-EmailEvents"
    | properties.DeliveryAction = "Delivered"
    | "Any delivery?" := "Yes"
}, field=[properties.NetworkMessageId], include=["Any delivery?"], mode=left)
| default(value="No", field=["Any delivery?"])
| groupBy([properties.Url], function=[
    selectLast([@timestamp, "Malicious confidence", "IOC age"]),
    count(properties.NetworkMessageId, distinct=true, as="No. of emails"),
    { "Any delivery?" = "Yes" | count() | _count match { 0 => "Any delivery?" := "No"; * => "Any delivery?" := "Yes" } | drop(_count) }
])
| "Any delivery?" = ?anyDelivery
| "Latest email" := formatTime("%b %d %T %Z", field=@timestamp)
| URL := rename(properties.Url)
| table([URL, "Malicious confidence", "No. of emails", "Any delivery?", "Latest email", "IOC age"])

#logtype = "microsoft365"
| #category = "AdvancedHunting-EmailEvents"
| tenantId=?{tenantId=*}
| case {
    test(?anyDelivery == "*");
	test(?anyDelivery == "Yes") | properties.DeliveryAction = "Delivered";
    test(?anyDelivery == "No") | properties.DeliveryAction != "Delivered";
}
| ioc:lookup([properties.SenderIPv4, properties.SenderIPv6], type="ip_address", strict="true", confidenceThreshold=?threshold)
| timechart(ioc[0].malicious_confidence)

#logtype = "microsoft365"
| #category = "AdvancedHunting-EmailEvents"
| tenantId=?{tenantId=*}
// We only check for IOC matches against SenderFromDomain here, although we could also do so for SenderMailFromDomain.
// We've chosen not to do so, as SenderMailFromDomain seems unlikely to have valuable information, as it is easily spoofed.
| ioc:lookup(["properties.SenderFromDomain"], type="domain", strict="true", confidenceThreshold=?threshold)
| groupBy(["properties.SenderFromDomain"], function=[
    count(properties.NetworkMessageId, distinct=true, as="No. of emails"),
    selectLast([@timestamp, ioc[0].malicious_confidence, ioc[0].last_updated]),
    { properties.DeliveryAction = "Delivered" | count() | _count match { 0 => "Any delivery?" := "No"; * => "Any delivery?" := "Yes" } }
])
| "Any delivery?" = ?anyDelivery
| $"microsoft/microsoft365:IOC age and confidence presentation"()
| "Latest email" := formatTime("%b %d %T %Z", field=@timestamp)
| Domain := rename(properties.SenderFromDomain)
| table(["Domain", "Malicious confidence", "No. of emails", "Any delivery?", "Latest email", "IOC age"])

#logtype = "microsoft365"
| #category = "AdvancedHunting-EmailEvents"
| tenantId=?{tenantId=*}
| ioc:lookup([properties.SenderIPv4, properties.SenderIPv6], type="ip_address", strict="true", confidenceThreshold=?threshold)
| case {
    properties.SenderIPv4 != "null" | "Sender IP" := properties.SenderIPv4;
    "Sender IP" := properties.SenderIPv6
}
| groupBy(["Sender IP"], function=[
    count(properties.NetworkMessageId, distinct=true, as="No. of emails"),
    selectLast([@timestamp, ioc[0].malicious_confidence, ioc[0].last_updated]),
    { properties.DeliveryAction = "Delivered" | count() | _count match { 0 => "Any delivery?" := "No"; * => "Any delivery?" := "Yes" } }
])
| "Any delivery?" = ?anyDelivery
| $"microsoft/microsoft365:IOC age and confidence presentation"()
| "Latest email" := formatTime("%b %d %T %Z", field=@timestamp)
| table(["Sender IP", "Malicious confidence", "No. of emails", "Any delivery?", "Latest email", "IOC age"])

#logtype = "microsoft365"
| #category = "AdvancedHunting-EmailUrlInfo"
| tenantId=?{tenantId=*}
| ioc:lookup(["properties.UrlDomain"], type="domain", strict="true", confidenceThreshold=?threshold)
| join({
    #logtype = "microsoft365"
    | #category = "AdvancedHunting-EmailEvents"
    | properties.DeliveryAction = "Delivered"
    | "Any delivery?" := "Yes"
}, field=[properties.NetworkMessageId], include=["Any delivery?"], mode=left)
| default(value="No", field=["Any delivery?"])
| "Any delivery?" = ?anyDelivery
| timechart(ioc[0].malicious_confidence)

#logtype = "microsoft365"
| #category = "AdvancedHunting-EmailEvents"
| tenantId=?{tenantId=*}
| case {
    test(?anyDelivery == "*");
	test(?anyDelivery == "Yes") | properties.DeliveryAction = "Delivered";
    test(?anyDelivery == "No") | properties.DeliveryAction != "Delivered";
}
// We only check for IOC matches against SenderFromDomain here, although we could also do so for SenderMailFromDomain.
// We've chosen not to do so, as SenderMailFromDomain seems unlikely to have valuable information, as it is easily spoofed.
| ioc:lookup(["properties.SenderFromDomain"], type="domain", strict="true", confidenceThreshold=?threshold)
| split(ioc)
| timechart(ioc.malicious_confidence)

#logtype = "microsoft365"
| #category = "AdvancedHunting-EmailUrlInfo"
| tenantId=?{tenantId=*}
| ioc:lookup(["properties.Url"], type="url", strict="true", confidenceThreshold=?threshold)
| join({
    #logtype = "microsoft365"
    | #category = "AdvancedHunting-EmailEvents"
    | properties.DeliveryAction = "Delivered"
    | "Any delivery?" := "Yes"
}, field=[properties.NetworkMessageId], include=["Any delivery?"], mode=left)
| default(value="No", field=["Any delivery?"])
| "Any delivery?" = ?anyDelivery
| timechart(ioc[0].malicious_confidence)

#logtype = "microsoft365"
| #category = "AdvancedHunting-EmailUrlInfo"
| ioc:lookup(["properties.UrlDomain"], type="domain", strict="true", confidenceThreshold=?threshold)
| $"microsoft/microsoft365:IOC age and confidence presentation"()
| groupBy([properties.UrlDomain, properties.NetworkMessageId], function=[selectLast([@timestamp, "Malicious confidence", "IOC age"])])
| join({
    #logtype = "microsoft365"
    | #category = "AdvancedHunting-EmailEvents"
    | properties.DeliveryAction = "Delivered"
    | "Any delivery?" := "Yes"
}, field=[properties.NetworkMessageId], include=["Any delivery?"], mode=left)
| default(value="No", field=["Any delivery?"])
| groupBy([properties.UrlDomain], function=[
    selectLast([@timestamp, "Malicious confidence", "IOC age"]),
    count(properties.NetworkMessageId, distinct=true, as="No. of emails"),
    { "Any delivery?" = "Yes" | count() | _count match { 0 => "Any delivery?" := "No"; * => "Any delivery?" := "Yes" } | drop(_count) }
])
| "Any delivery?" = ?anyDelivery
| "Latest email" := formatTime("%b %d %T %Z", field=@timestamp)
| Domain := rename(properties.UrlDomain)
| table([Domain, "Malicious confidence", "No. of emails", "Any delivery?", "Latest email", "IOC age"])

#logtype = "microsoft365"
| #category="AdvancedHunting-CloudAppEvents"
| properties.ActionType="Set-Mailbox"
| tenantId=?{tenantId=*}
| ?{Freetext=*}
// Looking for the ForwardingSmtpAddress "parameter"
| regex(".*{\"Name\"\:\s\"ForwardingSmtpAddress\"\,\s\"Value\"\:\s\"(?<ForwardingSmtpAddress>.+?)\"\}", field=@rawstring)

#logtype = "microsoft365"
| #category="AdvancedHunting-CloudAppEvents"
| properties.ActionType="*et-InboxRule"
| tenantId=?{tenantId=*}
| ?{Freetext=*}
// Looking for two "parameters":
//  1. the name of the rule (that the user gave their rule)
//  2. the address to forward to
| regex(".*{\"Name\"\:\s\"Name\"\,\s\"Value\"\:\s\"(?<Rulename>.+?)\"\}", field=@rawstring)
| regex(".*{\"Name\"\:\s\"SentTo\"\,\s\"Value\"\:\s\"(?<SentTo>.+?)\"\}", field=@rawstring)
| split(properties.RawEventData.Parameters)
| case {
    in(properties.RawEventData.Parameters.Name, values=["ForwardTo", "ForwardAsAttachmentTo", "RedirectTo"])
        | Method := properties.RawEventData.Parameters.Name
        | Email := properties.RawEventData.Parameters.Value;
}

#logtype = "microsoft365"
| #category="AdvancedHunting-CloudAppEvents"
| properties.ActionType="Set-TransportRule"
| tenantId=?{tenantId=*}
| ?{Freetext=*}
// Looking for two "parameters":
//  1. the name of the rule (that the user gave their rule)
//  2. the address to forward to
| regex(".*{\"Name\"\:\s\"Name\"\,\s\"Value\"\:\s\"(?<Rulename>.+?)\"\}", field=@rawstring)
| regex(".*{\"Name\"\:\s\"SentTo\"\,\s\"Value\"\:\s\"(?<SentTo>.+?)\"\}", field=@rawstring)
| split(properties.RawEventData.Parameters)
| case {
    in(properties.RawEventData.Parameters.Name, values=["ModerateMessageByUser", "RedirectMessageTo", "AddToRecipients"])
        | Method := properties.RawEventData.Parameters.Name
        | Email := properties.RawEventData.Parameters.Value;
}

#logtype = "microsoft365"
| #category="AdvancedHunting-CloudAppEvents"
| properties.ActionType="New-TransportRule"
| tenantId=?{tenantId=*}
| ?{Freetext=*}
// Looking for two "parameters":
//  1. the name of the rule (that the user gave their rule)
//  2. the address to forward to
| regex(".*{\"Name\"\:\s\"Name\"\,\s\"Value\"\:\s\"(?<Rulename>.+?)\"\}", field=@rawstring)
| regex(".*{\"Name\"\:\s\"SentTo\"\,\s\"Value\"\:\s\"(?<SentTo>.+?)\"\}", field=@rawstring)
| split(properties.RawEventData.Parameters)
| case {
    in(properties.RawEventData.Parameters.Name, values=["ModerateMessageByUser", "RedirectMessageTo", "AddToRecipients"])
        | Method := properties.RawEventData.Parameters.Name
        | Email := properties.RawEventData.Parameters.Value;
}

#logtype = "microsoft365"
| #category="AdvancedHunting-CloudAppEvents"
| properties.ActionType="New-InboxRule"
| tenantId=?{tenantId=*}
| ?{Freetext=*}
// Looking for two "parameters":
//  1. the name of the rule (that the user gave their rule)
//  2. the address to forward to
| regex(".*{\"Name\"\:\s\"Name\"\,\s\"Value\"\:\s\"(?<Rulename>.+?)\"\}", field=@rawstring)
| regex(".*{\"Name\"\:\s\"SentTo\"\,\s\"Value\"\:\s\"(?<SentTo>.+?)\"\}", field=@rawstring)
| split(properties.RawEventData.Parameters)
| case {
    in(properties.RawEventData.Parameters.Name, values=["ForwardTo", "ForwardAsAttachmentTo", "RedirectTo"])
        | Method := properties.RawEventData.Parameters.Name
        | Email := properties.RawEventData.Parameters.Value;
}

#logtype = "microsoft365"
| test(?NetworkMessageId != "*")
| #category = "AdvancedHunting-EmailEvents"
| properties.NetworkMessageId = ?{NetworkMessageId=*}
| tenantId = ?{tenantId=*}
| properties.DeliveryAction = ?{DeliveryStatus=*}
| properties.RecipientEmailAddress = ?{RecipientAddress=*}
| Recipient := rename(properties.RecipientEmailAddress)
| Status := rename(properties.DeliveryAction)
| table([Status, Recipient], limit=20000)

#logtype = "microsoft365"
| #category = "AdvancedHunting-EmailEvents"
| properties.DeliveryAction = "Blocked"
| tenantId = ?{tenantId=*}
| properties.NetworkMessageId = ?{NetworkMessageId=*}
| properties.SenderFromAddress = ?{SenderAddress=*}
| properties.RecipientEmailAddress = ?{RecipientAddress=*}
| properties.EmailDirection = ?{EmailDirection=*}
| properties.Subject = ?{Subject=*}
| properties.ThreatTypes = ?{ThreatType=*}
| properties.SenderIPv4 = ?{SenderIP=*} OR properties.SenderIPv6 = ?{SenderIP=*}
| groupBy([properties.NetworkMessageId], function=[
    selectLast([@timestamp, properties.SenderFromAddress, properties.ThreatTypes, properties.DetectionMethods, properties.ConfidenceLevel]),
    count(properties.RecipientEmailAddress, as="No. of recipients", distinct=true)
])
| NetworkMessageId := rename(properties.NetworkMessageId)
| Sender := rename(properties.SenderFromAddress)
| ThreatType := rename(properties.ThreatTypes)
| "Detection method" := rename(properties.DetectionMethods)
| Confidence := rename(properties.ConfidenceLevel)
| table([@timestamp, NetworkMessageId, Sender, "No. of recipients", ThreatType, "Detection method", Confidence])

#logtype = "microsoft365"
| #category = "AdvancedHunting-EmailEvents"
| tenantId = ?{tenantId=*}
| properties.NetworkMessageId = ?{NetworkMessageId=*}
| properties.SenderFromAddress = ?{SenderAddress=*}
| properties.RecipientEmailAddress = ?{RecipientAddress=*}
| properties.EmailDirection = ?{EmailDirection=*}
| properties.DeliveryAction = ?{DeliveryStatus=*}
| properties.Subject = ?{Subject=*}
| properties.ThreatTypes = ?{ThreatType=*}
| properties.SenderIPv4 = ?{SenderIP=*} OR properties.SenderIPv6 = ?{SenderIP=*}
| groupBy([properties.NetworkMessageId], function=[
    selectLast([@timestamp, properties.SenderFromAddress, properties.Subject, properties.EmailDirection, properties.SenderIPv4, properties.SenderIPv6]),
    count(properties.RecipientEmailAddress, as="No. of recipients", distinct=true),
    { properties.DeliveryAction = "Delivered" | count() | _count match { 0 => "Any delivery?" := "No"; * => "Any delivery?" := "Yes" } }
])
| case {
    properties.SenderIPv4 != "null" | "Sender IP" := properties.SenderIPv4;
    "Sender IP" := properties.SenderIPv6
}
| join({
    #logtype = "microsoft365"
    | #category = "AdvancedHunting-UrlClickEvents"
    | "Any URL clicked?" := "Yes"
}, field=[properties.NetworkMessageId], include=["Any URL clicked?"], mode=left)
| default(field="Any URL clicked?", value="No")
| NetworkMessageId := rename(properties.NetworkMessageId)
| From := rename(properties.SenderFromAddress)
| Subject := rename(properties.Subject)
| Direction := rename(properties.EmailDirection)
| table([@timestamp, NetworkMessageId, From, "Sender IP", Subject, "No. of recipients", Direction, "Any delivery?", "Any URL clicked?"])

#logtype = "microsoft365"
| test(?URL != "*")
| #category = "AdvancedHunting-UrlClickEvents"
| tenantId = ?{tenantId=*}
| properties.NetworkMessageId = ?{NetworkMessageId=*}
| properties.AccountUpn = ?{AccountUpn=*}
| properties.Url=?{URL=*}

#logtype = "microsoft365"
| test(?URL != "*")
| #category = "AdvancedHunting-EmailEvents"
| tenantId = ?{tenantId=*}
| properties.NetworkMessageId = ?{NetworkMessageId=*}
| properties.SenderFromAddress = ?{SenderAddress=*}
| properties.RecipientEmailAddress = ?{RecipientAddress=*}
| properties.DeliveryAction = ?{DeliveryStatus=*}
| properties.SenderIPv4 = ?{SenderIP=*} OR properties.SenderIPv6 = ?{SenderIP=*}
| join({#logtype = "microsoft365" | #category = "AdvancedHunting-EmailUrlInfo" | properties.Url=?{URL=*}}, field=properties.NetworkMessageId, include=properties.Url)

#logtype = "microsoft365"
| test(?NetworkMessageId != "*")
| #category = "AdvancedHunting-EmailEvents"
| properties.NetworkMessageId = ?{NetworkMessageId=*}
| tenantId = ?{tenantId=*}
| [
    selectLast([properties.SenderFromAddress, properties.SenderMailFromAddress, properties.Subject, @timestamp, properties.UrlCount]),
    count(properties.RecipientEmailAddress, distinct=true, as="No. of recipients")
]
// In the case where ?NetworkMessageId is not set,
// we repeat the test to clear the results from running `count` on an empty set of events.
| test(?NetworkMessageId != "*")
| From := rename(properties.SenderFromAddress)
| MailFrom := rename(properties.SenderMailFromAddress)
| Subject := rename(properties.Subject)
| Received := formatTime("%b %d %T %Z", field=@timestamp)
| table([Received, From, MailFrom, Subject, "No. of recipients"])
| transpose()
| " " := rename(column)
| "  " := rename(row[1])

#logtype = "microsoft365"
| test(?NetworkMessageId != "*")
| #category = "AdvancedHunting-EmailUrlInfo"
| properties.NetworkMessageId = ?{NetworkMessageId=*}
| tenantId = ?{tenantId=*}
| properties.Url = ?{URL=*}
| join({
    #logtype = "microsoft365"
    | #category = "AdvancedHunting-UrlClickEvents"
    | properties.AccountUpn = ?{AccountUpn=*}
    | "Was clicked?" := "Yes"
}, field=[properties.NetworkMessageId, properties.Url], include=["Was clicked?", properties.AccountUpn], mode=left)
// If AccountUpn parameter is not set, we want the join to be a left join,
// but if the parameter IS set, then we want it to be an inner join.
// We cannot do that directly, but we emulate it like this.
| properties.AccountUpn = ?{AccountUpn=*}
| default(value="No", field=["Was clicked?"])
| groupBy([properties.Url], function=[selectLast(["Was clicked?"])])
| "URL" := rename(properties.Url)
| table([URL, "Was clicked?"])

#logtype="microsoft365"
| tenantId=?{tenantId=*}
| #category = "AdvancedHunting-EmailEvents"
| properties.DeliveryAction = "Delivered"
| join({
    #logtype="microsoft365"
    | tenantId=?{tenantId=*}
    | #category = "AdvancedHunting-EmailUrlInfo"
    | ioc:lookup([properties.Url], type="url", confidenceThreshold="unverified")
    | case {
        ioc.detected = "true";
        * | ioc:lookup([properties.UrlDomain], type="domain", strict=true, confidenceThreshold="unverified");
    }
}, field=properties.NetworkMessageId, mode=left)
| ioc:lookup([properties.SenderIPv4, properties.SenderIPv6], type="ip_address", confidenceThreshold="unverified")
| case {
    ioc.detected = "true";
    * | ioc:lookup([properties.SenderFromDomain], type="domain", strict=true, confidenceThreshold="unverified");
}
| timechart(function=count(field=properties.NetworkMessageId, distinct=true), span=12h)

#logtype = "microsoft365"
| #category = "AdvancedHunting-EmailEvents"
| tenantId = ?{tenantId=*}
| top(properties.SenderFromAddress, as="No. of emails sent")
| Sender := rename(properties.SenderFromAddress)
| table([Sender, "No. of emails sent"])

#logtype = "microsoft365"
| #category="AdvancedHunting-EmailEvents"
| tenantId = ?{tenantId=*}
| !in(properties.ThreatTypes, values=["null", "Spam"])
| timeChart(properties.ThreatTypes)

#logtype = "microsoft365"
| #category="AdvancedHunting-EmailEvents"
| tenantId=?{tenantId=*}
| timeChart(properties.EmailDirection) // Emails sent and received over time

#logtype = "microsoft365"
| #category = "AdvancedHunting-EmailEvents"
| tenantId = ?{tenantId=*}
| top([properties.SenderFromAddress, properties.RecipientEmailAddress], as=Count)
| Sender := rename(properties.SenderMailFromAddress)
| Recipient := rename(properties.RecipientEmailAddress)
| table([Sender, Recipient, Count])

#logtype = "microsoft365"
| #category = "AdvancedHunting-EmailEvents"
| tenantId = ?{tenantId=*}
| properties.ThreatTypes = "Spam"
| timeChart()

#logtype = "microsoft365"
| #category = "AdvancedHunting-EmailEvents"
| tenantId = ?{tenantId=*}
| top(properties.RecipientEmailAddress, as="No. of emails received")
| Receiver := rename(properties.RecipientEmailAddress)
| table([Receiver, "No. of emails received"])

#logtype = "microsoft365"
| #category="AdvancedHunting-EmailEvents"
| tenantId = ?{tenantId=*}
| properties.ThreatTypes != "null"
| timeChart(properties.ThreatTypes, limit=10)

#logtype = "microsoft365"
| #category="AdvancedHunting-EmailEvents"
| tenantId = ?{tenantId=*}
| properties.ThreatTypes != "null"
| top(properties.ThreatTypes, limit=10, rest=others)

#logtype = "microsoft365"
| #category = "AdvancedHunting-EmailEvents"
| tenantId=?{tenantId=*}
| properties.RecipientEmailAddress = ?{RecipientAddress=*}
| properties.DeliveryAction = "Blocked" OR (properties.DeliveryAction != "Blocked" AND properties.ThreatTypes != "null")
| top("properties.RecipientEmailAddress", as="No. of emails")
| Recipient := rename("properties.RecipientEmailAddress")
| table(["Recipient", "No. of emails"])

#logtype = "microsoft365"
| #category="AdvancedHunting-EmailEvents"
| properties.ThreatTypes != "null"
| tenantId = ?{tenantId=*}
| timeChart(unit="/span to /day")

#logtype = "microsoft365"
| #category = "AdvancedHunting-EmailEvents"
| properties.ThreatTypes != "null"
| tenantId = ?{tenantId=*}
| case {
    properties.SenderIPv4 != "null" | "Sender IP" := properties.SenderIPv4;
    "Sender IP" := properties.SenderIPv6;
}
| top("Sender IP", as="No. of emails from")

#logtype = "microsoft365"
| #category = "AdvancedHunting-EmailEvents"
| properties.ThreatTypes != "null"
| tenantId = ?{tenantId=*}
| top(properties.SenderFromAddress, as="Total threats")
| Sender := rename("properties.SenderFromAddress")
| table([Sender, "Total threats"])