Deployable detection rules

AuditLogs
  | where Category =~ "ApplicationManagement"
  | where OperationName has_any ("Update Application", "Update Service principal")
  | extend InitiatingAppName = tostring(InitiatedBy.app.displayName)
  | extend InitiatingAppServicePrincipalId = tostring(InitiatedBy.app.servicePrincipalId)
  | extend InitiatingUserPrincipalName = tostring(InitiatedBy.user.userPrincipalName)
  | extend InitiatingAadUserId = tostring(InitiatedBy.user.id)
  | extend InitiatingIPAddress = tostring(InitiatedBy.user.ipAddress)
  | extend TargetAppName = tostring(TargetResources[0].displayName)
  | extend mod_props = TargetResources[0].modifiedProperties
  | mv-expand mod_props
  | extend Action = tostring(mod_props.displayName)
  | where Action contains "Url"
  | extend UpdatedBy = iif(isnotempty(InitiatingAppName), InitiatingAppName, InitiatingUserPrincipalName)
  | extend OldURL = tostring(mod_props.oldValue)
  | extend NewURL = tostring(mod_props.newValue)
  | extend InitiatingAccountName = tostring(split(InitiatingUserPrincipalName, "@")[0]), InitiatingAccountUPNSuffix = tostring(split(InitiatingUserPrincipalName, "@")[1])
  | project-reorder TimeGenerated, InitiatingAppName, InitiatingAppServicePrincipalId, InitiatingAadUserId, InitiatingUserPrincipalName, InitiatingIPAddress, UpdatedBy

AuditLogs
  | where Category =~ "ApplicationManagement"
  | where OperationName =~ "Add owner to application"
  | extend InitiatingAppName = tostring(InitiatedBy.app.displayName)
  | extend InitiatingAppServicePrincipalId = tostring(InitiatedBy.app.servicePrincipalId)
  | extend InitiatingUserPrincipalName = tostring(InitiatedBy.user.userPrincipalName)
  | extend InitiatingAadUserId = tostring(InitiatedBy.user.id)
  | extend InitiatingIPAddress = tostring(InitiatedBy.user.ipAddress)
  | extend TargetUserPrincipalName = TargetResources[0].userPrincipalName
  | extend TargetAadUserId = tostring(TargetResources[0].id)
  | extend mod_props = TargetResources[0].modifiedProperties
  | mv-expand mod_props
  | where mod_props.displayName =~ "Application.DisplayName"
  | extend TargetAppName = tostring(parse_json(tostring(mod_props.newValue)))
  | extend AddedUser = TargetUserPrincipalName
  | extend UpdatedBy = iif(isnotempty(InitiatingAppName), InitiatingAppName, InitiatingUserPrincipalName)
  | extend InitiatingAccountName = tostring(split(InitiatingUserPrincipalName, "@")[0]), InitiatingAccountUPNSuffix = tostring(split(InitiatingUserPrincipalName, "@")[1])
  | extend TargetAccountName = tostring(split(TargetUserPrincipalName, "@")[0]), TargetAccountUPNSuffix = tostring(split(TargetUserPrincipalName, "@")[1])
  | project-reorder TimeGenerated, InitiatingAppName, InitiatingAppServicePrincipalId, InitiatingAadUserId, InitiatingUserPrincipalName, InitiatingIPAddress, TargetAppName, AddedUser, UpdatedBy

AuditLogs
  | where Category =~ "RoleManagement"
  | where OperationName =~ "Update role setting in PIM"
  | extend InitiatingUserPrincipalName = tostring(InitiatedBy.user.userPrincipalName)
  | extend InitiatingAadUserId = tostring(InitiatedBy.user.id)
  | extend InitiatingIPAddress = tostring(InitiatedBy.user.ipAddress)
  | extend InitiatingAccountName = tostring(split(InitiatingUserPrincipalName, "@")[0]), InitiatingAccountUPNSuffix = tostring(split(InitiatingUserPrincipalName, "@")[1])
  | project-reorder TimeGenerated, OperationName, ResultReason, InitiatingUserPrincipalName, InitiatingAadUserId, InitiatingIPAddress, InitiatingAccountName, InitiatingAccountUPNSuffix

let aadFunc = (tableName:string){
CommonSecurityLog
| where DeviceVendor =~ "Cisco"
| where DeviceAction =~ "denied"
| where ipv4_is_private(SourceIP) == false
| summarize count() by SourceIP
| join (
    // Successful signins from IPs blocked by the firewall solution are suspect
    // Include fully successful sign-ins, but also ones that failed only at MFA stage
    // as that supposes the password was sucessfully guessed.
  table(tableName)
  | where ResultType in ("0", "50074", "50076")
) on $left.SourceIP == $right.IPAddress
| extend AccountName = tostring(split(Account, "@")[0]), AccountUPNSuffix = tostring(split(Account, "@")[1])
};
let aadSignin = aadFunc("SigninLogs");
let aadNonInt = aadFunc("AADNonInteractiveUserSignInLogs");
union isfuzzy=true aadSignin, aadNonInt

let domain_lookBack= 14d;
let timeframe = 1d;
let top_million_list = Cisco_Umbrella
| where EventType == "proxylogs"
| where TimeGenerated > ago(domain_lookBack) and TimeGenerated < ago(timeframe)
| extend Hostname = parse_url(UrlOriginal)["Host"]
| summarize count() by tostring(Hostname)
| top 1000000 by count_
| summarize make_list(Hostname);
Cisco_Umbrella
| where EventType == "proxylogs"
| where TimeGenerated > ago(timeframe)
| extend Hostname = parse_url(UrlOriginal)["Host"]
| where Hostname !in (top_million_list)
| extend Message = "Connect to unpopular website (possible malicious payload delivery)"
| project Message, SrcIpAddr, DstIpAddr,UrlOriginal, TimeGenerated

let lbtime = 10m;
Cisco_Umbrella
| where TimeGenerated > ago(lbtime)
| where EventType == 'proxylogs'
| where DvcAction =~ 'Allowed'
| where UrlCategory has_any ('Dynamic and Residential', 'Personal VPN')
| project TimeGenerated, SrcIpAddr, Identities

let timeframe = 15m;
Cisco_Umbrella
| where EventType == "proxylogs"
| where TimeGenerated > ago(timeframe)
| where HttpUserAgentOriginal contains "XMRig" or HttpUserAgentOriginal contains "ccminer"
| extend Message = "Crypto Miner User Agent"
| project Message, SrcIpAddr, DstIpAddr, UrlOriginal, TimeGenerated,HttpUserAgentOriginal

let timeframe = 15m;
Cisco_Umbrella
| where EventType == "proxylogs"
| where TimeGenerated > ago(timeframe)
| where HttpUserAgentOriginal == ''
| extend Message = "Empty User Agent"
| project Message, SrcIpAddr, DstIpAddr, UrlOriginal, TimeGenerated

let timeframe = 15m;
let user_agents=dynamic([
                          '(hydra)',
                          ' arachni/',
                          ' BFAC ',
                          ' brutus ',
                          ' cgichk ',
                          'core-project/1.0',
                          ' crimscanner/',
                          'datacha0s',
                          'dirbuster',
                          'domino hunter',
                          'dotdotpwn',
                          'FHScan Core',
                          'floodgate',
                          'get-minimal',
                          'gootkit auto-rooter scanner',
                          'grendel-scan',
                          ' inspath ',
                          'internet ninja',
                          'jaascois',
                          ' zmeu ',
                          'masscan',
                          ' metis ',
                          'morfeus fucking scanner',
                          'n-stealth',
                          'nsauditor',
                          'pmafind',
                          'security scan',
                          'springenwerk',
                          'teh forest lobster',
                          'toata dragostea',
                          ' vega/',
                          'voideye',
                          'webshag',
                          'webvulnscan',
                          ' whcc/',
                          ' Havij',
                          'absinthe',
                          'bsqlbf',
                          'mysqloit',
                          'pangolin',
                          'sql power injector',
                          'sqlmap',
                          'sqlninja',
                          'uil2pn',
                          'ruler',
                          'Mozilla/5.0 (Windows; U; Windows NT 5.1; pt-PT; rv:1.9.1.2) Gecko/20090729 Firefox/3.5.2 (.NET CLR 3.5.30729)'
                          ]);
Cisco_Umbrella
| where EventType == "proxylogs"
| where TimeGenerated > ago(timeframe)
| where HttpUserAgentOriginal has_any (user_agents)
| extend Message = "Hack Tool User Agent"
| project Message, SrcIpAddr, DstIpAddr, UrlOriginal, TimeGenerated, HttpUserAgentOriginal

let lookBack = 14d;
let timeframe = 1d;
let user_agents_list = Cisco_Umbrella
| where EventType == "proxylogs"
| where TimeGenerated > ago(lookBack) and TimeGenerated < ago(timeframe)
| summarize count() by HttpUserAgentOriginal
| summarize make_list(HttpUserAgentOriginal);
Cisco_Umbrella
| where EventType == "proxylogs"
| where TimeGenerated > ago(timeframe)
| where HttpUserAgentOriginal !in (user_agents_list)
| extend Message = "Rare User Agent"
| project Message, SrcIpAddr, DstIpAddr, UrlOriginal, TimeGenerated, HttpUserAgentOriginal

let lbtime = 10m;
Cisco_Umbrella
| where TimeGenerated > ago(lbtime)
| where EventType == 'proxylogs'
| where DvcAction =~ 'Allowed'
| where UrlCategory contains 'Adult Themes' or
      UrlCategory contains 'Adware' or
      UrlCategory contains 'Alcohol' or
      UrlCategory contains 'Illegal Downloads' or
      UrlCategory contains 'Drugs' or
      UrlCategory contains 'Child Abuse Content' or
      UrlCategory contains 'Hate/Discrimination' or
      UrlCategory contains 'Nudity' or
      UrlCategory contains 'Pornography' or
      UrlCategory contains 'Proxy/Anonymizer' or
      UrlCategory contains 'Sexuality' or
      UrlCategory contains 'Tasteless' or
      UrlCategory contains 'Terrorism' or
      UrlCategory contains 'Web Spam' or
      UrlCategory contains 'German Youth Protection' or
      UrlCategory contains 'Illegal Activities' or
      UrlCategory contains 'Lingerie/Bikini' or
      UrlCategory contains 'Weapons'
| project TimeGenerated, SrcIpAddr, Identities

let file_ext_blocklist = dynamic(['.ps1', '.vbs', '.bat', '.scr']);
let lbtime = 10m;
Cisco_Umbrella
| where TimeGenerated > ago(lbtime)
| where EventType == 'proxylogs'
| where DvcAction =~ 'Allowed'
| extend file_ext = extract(@'.*(\.\w+)$', 1, UrlOriginal)
| extend Filename = extract(@'.*\/*\/(.*\.\w+)$', 1, UrlOriginal)
| where file_ext in (file_ext_blocklist)
| project TimeGenerated, SrcIpAddr, Identities, Filename

let lbtime = 10m;
Cisco_Umbrella
| where TimeGenerated > ago(lbtime)
| where EventType == 'proxylogs'
| where DvcAction =~ 'Allowed'
| where UrlOriginal matches regex @'\Ahttp:\/\/\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}.*'
| project TimeGenerated, SrcIpAddr, Identities

let timeframe = 15m;
Cisco_Umbrella
| where EventType == "proxylogs"
| where TimeGenerated > ago(timeframe)
| where HttpUserAgentOriginal contains "WindowsPowerShell"
| extend Message = "Windows PowerShell User Agent"
| project Message, SrcIpAddr, DstIpAddr, UrlOriginal, TimeGenerated,HttpUserAgentOriginal

let known_users = (AuditLogs
  | where TimeGenerated between(ago(14d)..ago(1d))
  | where OperationName has "conditional access policy"
  | where Result =~ "success"
  | extend InitiatingUserPrincipalName = tostring(InitiatedBy.user.userPrincipalName)
  | summarize by InitiatingUserPrincipalName);
  AuditLogs
  | where TimeGenerated > ago(1d)
  | where OperationName has "conditional access policy"
  | where Result =~ "success"
  | extend InitiatingAppName = tostring(InitiatedBy.app.displayName)
  | extend InitiatingAppId = tostring(InitiatedBy.app.appId)
  | extend InitiatingAppServicePrincipalId = tostring(InitiatedBy.app.servicePrincipalId)
  | extend InitiatingUserPrincipalName = tostring(InitiatedBy.user.userPrincipalName)
  | extend InitiatingAadUserId = tostring(InitiatedBy.user.id)
  | extend InitiatingIPAddress = tostring(InitiatedBy.user.ipAddress)
  | extend CAPolicyName = tostring(TargetResources[0].displayName)
  | where InitiatingUserPrincipalName !in (known_users)
  | extend NewPolicyValues = TargetResources[0].modifiedProperties[0].newValue
  | extend OldPolicyValues = TargetResources[0].modifiedProperties[0].oldValue
  | extend InitiatingAccountName = tostring(split(InitiatingUserPrincipalName, "@")[0]), InitiatingAccountUPNSuffix = tostring(split(InitiatingUserPrincipalName, "@")[1])
  | project-reorder TimeGenerated, OperationName, CAPolicyName, InitiatingAppId, InitiatingAppName, InitiatingAppServicePrincipalId, InitiatingUserPrincipalName, InitiatingAadUserId, InitiatingIPAddress, NewPolicyValues, OldPolicyValues

let oneDriveCalls = dynamic(['graph.microsoft.com/v1.0/me/drive/root:/Documents/data.txt:/content','graph.microsoft.com/v1.0/me/drive/root:/Documents/response.json:/content']);
let oneDriveCallsRegex = dynamic([@'graph\.microsoft\.com\/v1\.0\/me\/drive\/root\:\/Uploaded\/.*\:\/content',@'graph\.microsoft\.com\/v1\.0\/me\/drive\/root\:\/Downloaded\/.*\:\/content']);
CommonSecurityLog
| where RequestURL has_any (oneDriveCalls) or RequestURL matches regex tostring(oneDriveCallsRegex[0]) or RequestURL matches regex tostring(oneDriveCallsRegex[1])
| project TimeGenerated, DeviceVendor, DeviceProduct, DeviceAction, DestinationDnsDomain, DestinationIP, RequestURL, SourceIP, SourceHostName, RequestClientApplication

let eventsThreshold = 20;
CommonSecurityLog
| where isnotempty(RequestURL)
| project TimeGenerated, RequestURL, RequestMethod, SourceIP, SourceHostName
| evaluate sequence_detect(TimeGenerated, 5s, 8s, login=(RequestURL has "login.microsoftonline.com/consumers/oauth2/v2.0/token"), graph=(RequestURL has "graph.microsoft.com/v1.0/me/drive/"), SourceIP, SourceHostName)
| summarize Events=count() by SourceIP, SourceHostName
| where Events >= eventsThreshold

//Detect AADInternals use, where we see a domain changed from managed to federated, and the issuer contains any.sts or the issuer suffix is 8 characters, a combination of letters and numbers

//Data connector required for this query - M365 Defender - CloudAppEvents

CloudAppEvents
| where ActionType == "Set domain authentication."
| extend Actor = tostring(RawEventData.UserId)
| extend mp=parse_json(RawEventData.ModifiedProperties)
| extend DomainName = tostring(parse_json(tostring(RawEventData.Target))[0].ID)
| mv-apply mp on (
where mp.Name == "IssuerUri"
| extend Issuer=mp.NewValue
)
| extend mp=parse_json(RawEventData.ModifiedProperties)
| mv-apply mp on (
where mp.Name == "LiveType"
| extend OldDomainType = mp.OldValue
| extend NewDomainType = mp.NewValue
)
| project TimeGenerated, Actor, DomainName, OldDomainType, NewDomainType, Issuer
| parse Issuer with * @'://' Issuer @'"' *
| extend IssuerSuffix = split(Issuer, '/')[-1]
| where OldDomainType has "Managed" and NewDomainType has "Federated"
| where Issuer has "any.sts" or IssuerSuffix matches regex "^[a-zA-Z0-9]{8}$"

//Advanced Hunting query

//Data connector required for this query - Advanced Hunting license

CloudAppEvents
| where ActionType == "Set domain authentication."
| extend Actor = tostring(RawEventData.UserId)
| extend mp=parse_json(RawEventData.ModifiedProperties)
| extend DomainName = tostring(parse_json(tostring(RawEventData.Target))[0].ID)
| mv-apply mp on (
where mp.Name == "IssuerUri"
| extend Issuer=mp.NewValue
)
| extend mp=parse_json(RawEventData.ModifiedProperties)
| mv-apply mp on (
where mp.Name == "LiveType"
| extend OldDomainType = mp.OldValue
| extend NewDomainType = mp.NewValue
)
| project Timestamp, Actor, DomainName, OldDomainType, NewDomainType, Issuer
| parse Issuer with * @'://' Issuer @'"' *
| extend IssuerSuffix = split(Issuer, '/')[-1]
| where OldDomainType has "Managed" and NewDomainType has "Federated"
| where Issuer has "any.sts" or IssuerSuffix matches regex "^[a-zA-Z0-9]{8}$"

//Detect when one of your Exchange admins grants themselves access to another users mailbox

//Data connector required for this query - M365 Defender - CloudAppEvents

//Microsoft Sentinel query
CloudAppEvents
| extend Operation= tostring(RawEventData.Operation)
| where Operation == "Add-MailboxPermission"
| extend TargetMailbox = tostring(parse_json(tostring(RawEventData.Parameters))[2].Value)
| extend UserAdded = tostring(parse_json(tostring(RawEventData.Parameters))[3].Value)
| extend AccessGranted = tostring(parse_json(tostring(RawEventData.Parameters))[4].Value)
| extend Actor = tostring(RawEventData.UserId)
| where Actor =~ UserAdded 
| project TimeGenerated, Actor, TargetMailbox, UserAdded, AccessGranted

//Advanced Hunting query

//Data connector required for this query - Advanced Hunting license

CloudAppEvents
| extend Operation= tostring(RawEventData.Operation)
| where Operation == "Add-MailboxPermission"
| extend TargetMailbox = tostring(parse_json(tostring(RawEventData.Parameters))[2].Value)
| extend UserAdded = tostring(parse_json(tostring(RawEventData.Parameters))[3].Value)
| extend AccessGranted = tostring(parse_json(tostring(RawEventData.Parameters))[4].Value)
| extend Actor = tostring(RawEventData.UserId)
| where Actor =~ UserAdded 
| project Timestamp, Actor, TargetMailbox, UserAdded, AccessGranted

//Use the Defender for Cloud Apps logs to detect when a mail forward is created on a mailbox (not an individual mailbox rule). Retrieve the address the mail was forwarded to and whether is both stored and forwarded

//Data connector required for this query - M365 Defender - CloudAppEvents

//Microsoft Sentinel query
CloudAppEvents
| where ActionType == "Set-Mailbox"
| extend UserId = tostring(RawEventData.UserId)
| extend ForwardingSetting = tostring(parse_json(tostring(RawEventData.Parameters))[1].Name)
| extend ForwardingAddress = tostring(parse_json(tostring(RawEventData.Parameters))[1].Value)
| extend StoreandForward = tostring(parse_json(tostring(RawEventData.Parameters))[2].Name)
| extend ['Email Stored and Forwarded'] = tostring(parse_json(tostring(RawEventData.Parameters))[2].Value)
| where ForwardingSetting == "ForwardingSmtpAddress" and isnotempty(ForwardingAddress)
| extend ['Forwarding Email Address']=split(ForwardingAddress, ":")[-1]
| project-away ForwardingSetting, StoreandForward
| project
    TimeGenerated,
    UserId,
    IPAddress, 
    ['Forwarding Email Address'], 
    ['Email Stored and Forwarded']

//Advanced Hunting query

//Data connector required for this query - Advanced Hunting license

CloudAppEvents
| where ActionType == "Set-Mailbox"
| extend UserId = tostring(RawEventData.UserId)
| extend ForwardingSetting = tostring(parse_json(tostring(RawEventData.Parameters))[1].Name)
| extend ForwardingAddress = tostring(parse_json(tostring(RawEventData.Parameters))[1].Value)
| extend StoreandForward = tostring(parse_json(tostring(RawEventData.Parameters))[2].Name)
| extend ['Email Stored and Forwarded'] = tostring(parse_json(tostring(RawEventData.Parameters))[2].Value)
| where ForwardingSetting == "ForwardingSmtpAddress" and isnotempty(ForwardingAddress)
| extend ['Forwarding Email Address']=split(ForwardingAddress, ":")[-1]
| project-away ForwardingSetting, StoreandForward
| project
    Timestamp,
    UserId,
    IPAddress, 
    ['Forwarding Email Address'], 
    ['Email Stored and Forwarded']

//Create a pivot table of all actions taken during a risky sign in

//Data connector required for this query - Advanced Hunting license

//First find the SessionId of any medium or high risk sign ins (where risk level is 50 or 100)
//Advanced Hunting query, this query doesn't work in Sentinel because SessionId isn't sent over currently
let riskysignins=
    AADSignInEventsBeta
    | where Timestamp > ago(7d)
    | where RiskLevelDuringSignIn in (50, 100)
    | distinct SessionId;
CloudAppEvents
| where Timestamp > ago(7d)
| extend RawEventData = parse_json(RawEventData)
| extend SessionId = RawEventData.SessionId
| where isnotempty(SessionId)
//Match on the risky sign in SessionId from above
| where SessionId in (riskysignins)
| extend Activity = strcat(Application, " - ", ActionType)
//Create pivot table of all actions by each user
| evaluate pivot(Activity, count(), AccountDisplayName)

//Using regex for searching for MFA phone number changes can be a valuable hunting strategy for understanding user compromise. Regex can be used to look for particular patterns for phone numbers, for instance if your business is in Europe, then USA formatted numbers may be suspicious
//This query is part of The Definitive Guide to KQL: Using Kusto Query Language for Operations, Defending, and Threat Hunting - https://aka.ms/KQLMSPress and was contributed by Marius F - https://www.linkedin.com/in/mariussagdal/

//Data connector required for this query - Defender for Cloud Apps

CloudAppEvents 
| where Timestamp >= datetime("Insert date") 
| where ActionType == "Update user." and RawEventData contains "StrongAuthentication" 
| extend target = RawEventData.ObjectId 
| mvexpand ModifiedProperties = parse_json(RawEventData.ModifiedProperties) 
| where ModifiedProperties matches regex @"\+\d{1,3}\s*\d{9,}" 
| mvexpand ModifiedProperties = parse_json(ModifiedProperties) 
| where ModifiedProperties contains "NewValue" and ModifiedProperties matches regex @"\+\d{1,3}\s*\d{9,}" 
| extend PhoneNumber = extract(@"\+\d{1,3}\s*\d{9,}", 0, tostring(ModifiedProperties)) 
| project Timestamp, target, PhoneNumber

//Use the Defender for Cloud Apps logs to detect when an action is taken in Azure Active Directory that is considered an admin operation

//Data connector required for this query - M365 Defender - CloudAppEvents

//Microsoft Sentinel query
CloudAppEvents
| where Application == "Office 365"
| extend Workload=RawEventData.Workload
| where Workload == "AzureActiveDirectory"
| where IsAdminOperation == "true"
| project TimeGenerated, ActionType, AccountDisplayName, ActivityType, RawEventData

//Advanced Hunting query

//Data connector required for this query - Advanced Hunting license

CloudAppEvents
| where Application == "Office 365"
| extend Workload=RawEventData.Workload
| where Workload == "AzureActiveDirectory"
| where IsAdminOperation == "1"
| project Timestamp, ActionType, AccountDisplayName, ActivityType, RawEventData

//Find new events in Defender for Cloud Apps seen in the last week vs the previously 90 days

//Data connector required for this query - M365 Defender - CloudAppEvents

//Microsoft Sentinel query
//First find all the activities from the last 90 days prior to this week
let knownactivities=
    CloudAppEvents
    | where TimeGenerated > ago(90d) and TimeGenerated < ago (7d)
    | extend Operation = tostring(RawEventData.Operation)
    | extend UserId = tostring(RawEventData.UserId)
    | extend Workload = tostring(RawEventData.Workload)
    //Create a new column that adds workload and operation together to make the events more readable
    | extend Activity = strcat(Workload, " - ", Operation)
    | distinct Activity;
//Find activities from the last week
CloudAppEvents
| where TimeGenerated > ago(7d)
| extend Operation = tostring(RawEventData.Operation)
| extend UserId = tostring(RawEventData.UserId)
| extend Workload = tostring(RawEventData.Workload)
//Create a new column that adds workload and operation together to make the events more readable
| extend Activity = strcat(Workload, " - ", Operation)
//Exclude activities we have already seen
| where Activity !in (knownactivities)
//Find the time the new activity was first seen and how many counts seen this week
| summarize ['First Time Seen']=min(TimeGenerated), Count=count() by Activity
| sort by Count desc 


//Advanced Hunting query, only 30 days of data is retained in Advanced Hunting so we can instead look at events new in the last 3 compared to the prior 30, but you can change the times around if needed

//Data connector required for this query - Advanced Hunting license

//First find all the activities from the last 30 days prior to this week
let knownactivities=
    CloudAppEvents
    | where Timestamp > ago(30d) and Timestamp < ago (3d)
    | extend Operation = tostring(RawEventData.Operation)
    | extend UserId = tostring(RawEventData.UserId)
    | extend Workload = tostring(RawEventData.Workload)
    //Create a new column that adds workload and operation together to make the events more readable
    | extend Activity = strcat(Workload, " - ", Operation)
    | distinct Activity;
//Find activities from the last week
CloudAppEvents
| where Timestamp > ago(3d)
| extend Operation = tostring(RawEventData.Operation)
| extend UserId = tostring(RawEventData.UserId)
| extend Workload = tostring(RawEventData.Workload)
//Create a new column that adds workload and operation together to make the events more readable
| extend Activity = strcat(Workload, " - ", Operation)
//Exclude activities we have already seen
| where Activity !in (knownactivities)
//Find the time the new activity was first seen and how many counts seen this week
| summarize ['First Time Seen']=min(Timestamp), Count=count() by Activity
| sort by Count desc

//Find emails that have been reported by your users as spam/phishing that have been rescanned and found to be genuine spam or phishing

//Data connector required for this query - M365 Defender - CloudAppEvents

//Microsoft Sentinel query
CloudAppEvents
| where ActionType == "UserSubmission"
| extend UserId = tostring(RawEventData.UserId)
| extend RescanVerdict = tostring(parse_json(tostring(RawEventData.RescanResult)).RescanVerdict)
| extend RescanTimeTimestamp = tostring(parse_json(tostring(RawEventData.RescanResult)).Timestamp)
| extend Subject = tostring(RawEventData.Subject)
| extend P1Sender = tostring(RawEventData.P1Sender)
| extend P2Sender = tostring(RawEventData.P2Sender)
| where RescanVerdict != "NotSpam"
| project
    TimeGenerated,
    UserId,
    P1Sender,
    P2Sender,
    Subject,
    RescanVerdict,
    RescanTimeTimestamp

//Data connector required for this query - Advanced Hunting license

//Advanced Hunting query
CloudAppEvents
| where ActionType == "UserSubmission"
| extend UserId = tostring(RawEventData.UserId)
| extend RescanVerdict = tostring(parse_json(tostring(RawEventData.RescanResult)).RescanVerdict)
| extend RescanTimeTimestamp = tostring(parse_json(tostring(RawEventData.RescanResult)).Timestamp)
| extend Subject = tostring(RawEventData.Subject)
| extend P1Sender = tostring(RawEventData.P1Sender)
| extend P2Sender = tostring(RawEventData.P2Sender)
| where RescanVerdict != "NotSpam"
| project
    Timestamp,
    UserId,
    P1Sender,
    P2Sender,
    Subject,
    RescanVerdict,
    RescanTimeTimestamp

//Alert when the phishing status of a Microsoft Form is changed, this could be a sign one of your accounts has been compromised and being used to host malicious Forms

//Data connector required for this query - M365 Defender - CloudAppEvents

//Microsoft Sentinel query
CloudAppEvents
| where TimeGenerated > ago (7d)
| extend Operation = tostring(RawEventData.Operation)
| where Operation == "UpdatePhishingStatus"
| extend UserId = tostring(RawEventData.UserId)
| extend Workload = tostring(RawEventData.Workload)
| extend FormStatus = tostring(parse_json(tostring(RawEventData.ActivityParameters)).FormPhishingStatus)
| extend FormId = tostring(RawEventData.FormId)
| extend FormName = tostring(RawEventData.FormName)
| where FormStatus == "Auto Blocked"
| project TimeGenerated, Operation, UserId, FormStatus, FormName, FormId

//Advanced Hunting query

//Data connector required for this query - Advanced Hunting license

CloudAppEvents
| where Timestamp > ago (7d)
| extend Operation = tostring(RawEventData.Operation)
| where Operation == "UpdatePhishingStatus"
| extend UserId = tostring(RawEventData.UserId)
| extend Workload = tostring(RawEventData.Workload)
| extend FormStatus = tostring(parse_json(tostring(RawEventData.ActivityParameters)).FormPhishingStatus)
| extend FormId = tostring(RawEventData.FormId)
| extend FormName = tostring(RawEventData.FormName)
| where FormStatus == "Auto Blocked"
| project Timestamp, Operation, UserId, FormStatus, FormName, FormId

//Alert when a user starts a paid trial of a M365 product

//Data connector required for this query - M365 Defender - CloudAppEvents

CloudAppEvents
| extend Operation = tostring(RawEventData.Operation)
| where Operation == "StartAPaidTrial"
| extend UserId = tostring(RawEventData.UserId)
| extend LicenseDisplayName = tostring(RawEventData.LicenseDisplayName)
| extend Workload = tostring(RawEventData.Workload)
| project TimeGenerated, Operation, UserId, LicenseDisplayName, Workload

//Create a pivot table of all actions in Defender for Cloud Apps by your privileged users over the last 7 days
//Lookup the IdentityInfo table for any users holding a privileged role

//Data connector required for this query - M365 Defender - CloudAppEvents

//Microsoft Sentinel query
let privusers=
    IdentityInfo
    | where TimeGenerated > ago(21d)
    | summarize arg_max(TimeGenerated, *) by AccountUPN
    //Add any roles that you are interested in auditing
    | where AssignedRoles has_any ("Global Administrator", "Security Administrator", "SharePoint Administrator")
    | distinct AccountUPN;
CloudAppEvents
| where TimeGenerated > ago(7d)
| extend Operation = tostring(RawEventData.Operation)
| extend UserId = tostring(RawEventData.UserId)
| extend Workload = tostring(RawEventData.Workload)
//Create a new column that adds workload and operation together to make the events more readable
| extend Activity = strcat(Workload, " - ", Operation)
| where UserId in~ (privusers)
//Create pivot table of all actions by each user
| evaluate pivot(Activity, count(), UserId)

//Advanced hunting query

//Data connector required for this query - Advanced Hunting license

CloudAppEvents
| where Timestamp > ago(7d)
| extend Operation = tostring(RawEventData.Operation)
| extend UserId = tostring(RawEventData.UserId)
| extend Workload = tostring(RawEventData.Workload)
//Advanced hunting doesn't retain role information about users, but you can add a list of users in manually to create a table
| where UserId in~ ("[email protected]", "[email protected]")
//Create a new column that adds workload and operation together to make the events more readable
| extend Activity = strcat(Workload, " - ", Operation)
//Create pivot table of all actions by each user
| evaluate pivot(Activity, count(), UserId)

//Defender for Cloud Apps tracks administrative actions under the 'isAdminOperation' flag. This query will build a pivot table of all admin operations completed by your users
//Works in both Sentinel and Advanced Hunting

//Data connector required for this query - Advanced Hunting license or M365 Defender - CloudAppEvents for Sentinel

CloudAppEvents
| where IsAdminOperation == "true"
| where AccountType == "Regular"
| extend UserPrincipalName = tostring(RawEventData.UserId)
| evaluate pivot(ActionType, count(), UserPrincipalName)

//Detect when a user flags a risky sign in within 8 hours of installing a service principal, could be a sign of OAuth consent phishing. This example uses 8 hours between events.

//Data connector required for this query - M365 Defender - CloudAppEvents

//Microsoft Sentinel query. This query could also use Azure AD audit logs as a trigger but this example uses Defender for Cloud App logs.
let threshold=8;
CloudAppEvents
| where ActionType == "Add service principal."
| where AccountType == "Regular"
| extend UserId = tostring(RawEventData.UserId)
| project
    ['Service Principal Install Time']=TimeGenerated,
    UserId,
    ['Service Principal Name']=ObjectName
| join kind=inner (
    AADUserRiskEvents
    | where DetectionTimingType == "realtime"
    | where RiskDetail !in ("aiConfirmedSigninSafe", "userPerformedSecuredPasswordReset")
    | project
        ['Risk Event Time']=TimeGenerated,
        UserId=UserPrincipalName,
        ['Risk Event IP']=IpAddress
    )
    on UserId
| extend ['Minutes Between Events']=datetime_diff("hour", ['Service Principal Install Time'], ['Risk Event Time'])
| where ['Minutes Between Events'] < threshold
| project
    UserId,
    ['Risk Event Time'],
    ['Service Principal Install Time'],
    ['Minutes Between Events'],
    ['Risk Event IP'],
    ['Service Principal Name']

//Advanced Hunting query

//Data connector required for this query - Advanced Hunting license

let threshold=8;
CloudAppEvents
| where ActionType == "Add service principal."
| where AccountType == "Regular"
| extend UserId = tostring(RawEventData.UserId)
| project
    ['Service Principal Install Time']=Timestamp,
    UserId,
    ['Service Principal Name']=ObjectName
| join kind=inner (
    AADSignInEventsBeta
    | where RiskLevelDuringSignIn in (50, 100)
    | project ['Risk Event Time']=Timestamp, UserId=AccountUpn, ['Risk Event IP']=IPAddress
    )
    on UserId
| extend ['Minutes Between Events']=datetime_diff("hour", ['Service Principal Install Time'], ['Risk Event Time'])
| where ['Minutes Between Events'] < threshold
| project
    UserId,
    ['Risk Event Time'],
    ['Service Principal Install Time'],
    ['Minutes Between Events'],
    ['Risk Event IP'],
    ['Service Principal Name']

//Alert when a user triggers an Azure AD risk event followed closely by a mail forward being configured on their mailbox

//Data connector required for this query - Azure Active Directory - AAD User Risk Events
//Data connector required for this query - M365 Defender - CloudAppEvents

//Choose a threshold of the time between events you want to alert one, this example uses 240 minutes between risky event and mail forward creation
let threshold=240;
//First find any real time risk events in Azure AD
AADUserRiskEvents
| where TimeGenerated > ago (7d)
| where DetectionTimingType == "realtime"
| where RiskDetail <> "aiConfirmedSigninSafe"
| project RiskTime=TimeGenerated, UserPrincipalName, RiskEventType, RiskyIP=IpAddress
| join kind=inner (
//Join to Defender for Cloud App events looking for email forward creation events
    CloudAppEvents
    | where TimeGenerated > ago (7d)
    | where ActionType == "Set-Mailbox"
    | extend UserId = tostring(RawEventData.UserId)
    | extend ForwardingSetting = tostring(parse_json(tostring(RawEventData.Parameters))[1].Name)
    | extend ForwardingAddress = tostring(parse_json(tostring(RawEventData.Parameters))[1].Value)
    | extend StoreandForward = tostring(parse_json(tostring(RawEventData.Parameters))[2].Name)
    | extend ['Email Stored and Forwarded'] = tostring(parse_json(tostring(RawEventData.Parameters))[2].Value)
    | where ForwardingSetting == "ForwardingSmtpAddress" and isnotempty(ForwardingAddress)
    | extend ['Forwarding Email Address']=split(ForwardingAddress, ":")[-1]
    | project-away ForwardingSetting, StoreandForward
    | project
        MailForwardTime=TimeGenerated,
        UserId,
        MailForwardIP=IPAddress, ['Forwarding Email Address'], ['Email Stored and Forwarded']
    ) 
    on $left.UserPrincipalName == $right.UserId
//Calculate the time between the two events and alert when less than the threshold
| extend ['Minutes Between Events']=datetime_diff("minute", MailForwardTime, RiskTime)
| where ['Minutes Between Events'] < threshold
| project-away UserId
| project-reorder
    UserPrincipalName,
    RiskTime,
    MailForwardTime,
    ['Minutes Between Events'],
    ['Forwarding Email Address'],
    ['Email Stored and Forwarded'],
    RiskyIP,
    MailForwardIP,
    RiskEventType

//Alert when a user flags an Azure AD risk event followed by creating or updating inbox rules within a short time frame

//Data connector required for this query - Azure Active Directory - AAD User Risk Events
//Data connector required for this query - M365 Defender - CloudAppEvents

//In this example it will detect when the two events are less than 120 minutes apart
AADUserRiskEvents
| where TimeGenerated > ago (1d)
| where DetectionTimingType == "realtime"
| where RiskDetail <> "aiConfirmedSigninSafe"
| project RiskTime=TimeGenerated, UserPrincipalName, RiskEventType, RiskyIP=IpAddress
| join kind=inner (
    CloudAppEvents
    | where TimeGenerated > ago (1d)
    | extend Operation = tostring(RawEventData.Operation)
    | where Operation in ("New-InboxRule", "Set-InboxRule")
    | extend UserId = tostring(RawEventData.UserId)
    | project RuleTime=TimeGenerated, UserId, MailForwardIP=IPAddress, ActivityObjects
    )
    on $left.UserPrincipalName == $right.UserId
| extend ['Minutes Between Events']=datetime_diff("minute", RuleTime, RiskTime)
| where ['Minutes Between Events'] < 120
| project-away UserId
| project-reorder
    UserPrincipalName,
    RiskTime,
    RuleTime,
    ['Minutes Between Events'],
    RiskyIP,
    MailForwardIP,
    RiskEventType,
    ActivityObjects

//Use the Defender for Cloud Apps logs to detect when an inbox rule is created where the name only has special characters, i.e '..' or '.....' this is a common threat actor TTP

//Data connector required for this query - M365 Defender - CloudAppEvents

//Microsoft Sentinel query
CloudAppEvents
| where Application == "Microsoft Exchange Online"
| where ActionType == "New-InboxRule"
| mv-apply p=todynamic(ActivityObjects) on 
(
where p.Name == "Name"
| extend RuleName=p.Value
)
| where isnotempty(RuleName)
| where RuleName matches regex @"^[^a-zA-Z0-9]*$"
| extend AccountUpn=tostring(RawEventData.UserId)
| extend SessionId=tostring(RawEventData.SessionId)
| project TimeGenerated, Application, ActionType, AccountUpn, RuleName, SessionId, IPAddress

//Advanced Hunting query

//Data connector required for this query - Advanced Hunting license

CloudAppEvents
| where Application == "Microsoft Exchange Online"
| where ActionType == "New-InboxRule"
| mv-apply p=todynamic(ActivityObjects) on 
(
where p.Name == "Name"
| extend RuleName=p.Value
)
| where isnotempty(RuleName)
| where RuleName matches regex @"^[^a-zA-Z0-9]*$"
| extend AccountUpn=tostring(RawEventData.UserId)
| extend SessionId=tostring(RawEventData.SessionId)
| project Timestamp, Application, ActionType, AccountUpn, RuleName, SessionId, IPAddress

//Find when an app is installed into Teams using the Defender for Cloud App logs

//Data connector required for this query - M365 Defender - CloudAppEvents

//Microsoft Sentinel query
CloudAppEvents
| where Application == "Microsoft Teams"
| where ActionType == "AppInstalled"
| extend AppDistributionMode = tostring(RawEventData.AppDistributionMode)
| extend AzureADAppId = tostring(RawEventData.AzureADAppId)
| extend UserId = tostring(RawEventData.UserId)
| extend AppName = tostring(RawEventData.AddOnName)
| project TimeGenerated, AppName, AzureADAppId, UserId, AppDistributionMode

//Advanced Hunting query

//Data connector required for this query - Advanced Hunting license

CloudAppEvents
| where Application == "Microsoft Teams"
| where ActionType == "AppInstalled"
| extend AppDistributionMode = tostring(RawEventData.AppDistributionMode)
| extend AzureADAppId = tostring(RawEventData.AzureADAppId)
| extend UserId = tostring(RawEventData.UserId)
| extend AppName = tostring(RawEventData.AddOnName)
| project Timestamp, AppName, AzureADAppId, UserId, AppDistributionMode

//Visualize the most popular emoji reactions used in your tenant, because, why not?

//Top 25 used in the query, you can remove that line if you want them all

//Data connector required for this query - M365 Defender - CloudAppEvents

//Microsoft Sentinel query
CloudAppEvents
| where TimeGenerated > ago (30d)
| where RawEventData.Operation == "ReactedToMessage"
| where RawEventData.Workload == "MicrosoftTeams"
| extend React = tostring(RawEventData.MessageReactionType)
| where isnotempty(React)
| project React
| extend React = tostring(split(React,"_")[-1])
| summarize Count=count() by React
| sort by Count desc
| take 25
| render barchart 

//Advanced Hunting query

//Data connector required for this query - Advanced Hunting license

CloudAppEvents
| where Timestamp > ago (30d)
| where ActionType == @"ReactedToMessage"
| where Application == @"Microsoft Teams"
| extend React = tostring(RawEventData.MessageReactionType)
| where isnotempty(React)
| project React
| extend React = tostring(split(React,"_")[-1])
| summarize Count=count() by React
| sort by Count desc
| take 25
| render columnchart

let torProxies=dynamic(["tor2web.org", "tor2web.com", "torlink.co", "onion.to", "onion.ink", "onion.cab", "onion.nu", "onion.link", 
"onion.it", "onion.city", "onion.direct", "onion.top", "onion.casa", "onion.plus", "onion.rip", "onion.dog", "tor2web.fi", 
"tor2web.blutmagie.de", "onion.sh", "onion.lu", "onion.pet", "t2w.pw", "tor2web.ae.org", "tor2web.io", "tor2web.xyz", "onion.lt", 
"s1.tor-gateways.de", "s2.tor-gateways.de", "s3.tor-gateways.de", "s4.tor-gateways.de", "s5.tor-gateways.de", "hiddenservice.net"]);
_Im_Dns(domain_has_any=torProxies)
| extend HostName = tostring(split(Dvc, ".")[0]), DomainIndex = toint(indexof(Dvc, '.'))
| extend HostNameDomain = iff(DomainIndex != -1, substring(Dvc, DomainIndex + 1), Dvc)
| project-away DomainIndex

let minersDomains=dynamic(["monerohash.com", "do-dear.com", "xmrminerpro.com", "secumine.net", "xmrpool.com", "minexmr.org", "hashanywhere.com", 
"xmrget.com", "mininglottery.eu", "minergate.com", "moriaxmr.com", "multipooler.com", "moneropools.com", "xmrpool.eu", "coolmining.club", 
"supportxmr.com", "minexmr.com", "hashvault.pro", "xmrpool.net", "crypto-pool.fr", "xmr.pt", "miner.rocks", "walpool.com", "herominers.com", 
"gntl.co.uk", "semipool.com", "coinfoundry.org", "cryptoknight.cc", "fairhash.org", "baikalmine.com", "tubepool.xyz", "fairpool.xyz", "asiapool.io", 
"coinpoolit.webhop.me", "nanopool.org", "moneropool.com", "miner.center", "prohash.net", "poolto.be", "cryptoescrow.eu", "monerominers.net", "cryptonotepool.org", 
"extrmepool.org", "webcoin.me", "kippo.eu", "hashinvest.ws", "monero.farm", "supportxmr.com", "xmrpool.eu", "linux-repository-updates.com", "1gh.com", 
"dwarfpool.com", "hash-to-coins.com", "hashvault.pro", "pool-proxy.com", "hashfor.cash", "fairpool.cloud", "litecoinpool.org", "mineshaft.ml", "abcxyz.stream", 
"moneropool.ru", "cryptonotepool.org.uk", "extremepool.org", "extremehash.com", "hashinvest.net", "unipool.pro", "crypto-pools.org", "monero.net", 
"backup-pool.com", "mooo.com", "freeyy.me", "cryptonight.net", "shscrypto.net"]);
_Im_Dns(domain_has_any=minersDomains)
| extend HostName = tostring(split(Dvc, ".")[0]), DomainIndex = toint(indexof(Dvc, '.'))
| extend HostNameDomain = iff(DomainIndex != -1, substring(Dvc, DomainIndex + 1), Dvc)
| project-away DomainIndex

//When a domain is flagged by Defender for Cloud (Azure Security Center) as suspicious then find any other clients that have queried that domain in DNS events

//Data connector required for this query - DNS

let suspiciousurl=
SecurityAlert
| where AlertName startswith "Communication with suspicious random domain name"
| mv-expand todynamic(Entities)
| project Entities
| extend SuspiciousURL = tostring(Entities.DomainName)
| where isnotempty(SuspiciousURL)
| distinct SuspiciousURL;
    DnsEvents
    | where QueryType == "A"
    | project Name, ClientIP
    | where Name in (suspiciousurl)
| summarize ['Client IPs']=make_set(ClientIP) by Name

Event
| where EventLog == "Microsoft-Windows-Sysmon/Operational" and EventID in (13)
| parse EventData with * 'ProcessId">' ProcessId "<"* 'Image">' Image "<" * 'TargetObject">' TargetObject "<" * 'Details">' Details "<" * 'User">' User "<" * 
| where TargetObject has ("HKLM\\System\\CurrentControlSet\\Control\\Lsa\\DsrmAdminLogonBehavior") and Details == "DWORD (0x00000002)"
| summarize StartTime = min(TimeGenerated), EndTime = max(TimeGenerated) by EventID, Computer, User, ProcessId, Image, TargetObject, Details, _ResourceId
| extend HostName = tostring(split(Computer, ".")[0]), DomainIndex = toint(indexof(Computer, '.'))
| extend HostNameDomain = iff(DomainIndex != -1, substring(Computer, DomainIndex + 1), Computer)
| extend AccountName = tostring(split(User, "\\")[1]), AccountNTDomain = tostring(split(User, "\\")[0])
| extend ImageFileName = tostring(split(Image, "\\")[-1])
| extend ImageDirectory = replace_string(Image, ImageFileName, "")
| project-away DomainIndex

//Calculate the percentage that each table in your Sentinel workspace is contributing to total ingestion for billable data

//Data connector required for this query - Usage (generated automatically on a log analytics workspace)

Usage
| where TimeGenerated > ago(30d)
| where IsBillable == "true"
| summarize ['Table size in GB']=sum(Quantity / 1024) by DataType
| as T
| extend Percentage = round(100.0 * ['Table size in GB'] / toscalar (T
    | summarize sum(['Table size in GB'])), 2)
| project-reorder DataType, ['Table size in GB'], Percentage
| sort by Percentage desc

//Calculate the change in size of all your tables from last week to this week

//Data connector required for this query - query will automatically union any data you have

let lastweek=
union withsource=_TableName *
| where TimeGenerated > ago(14d) and TimeGenerated < ago(7d)
| summarize
    Entries = count(), Size = sum(_BilledSize) by Type
| project ['Table Name'] = Type, ['Last Week Table Size'] = Size, ['Last Week Table Entries'] = Entries, ['Last Week Size per Entry'] = 1.0 * Size / Entries
| order by ['Table Name']  desc;
let thisweek=
union withsource=_TableName *
| where TimeGenerated > ago(7d)
| summarize
    Entries = count(), Size = sum(_BilledSize) by Type
| project ['Table Name'] = Type, ['This Week Table Size'] = Size, ['This Week Table Entries'] = Entries, ['This Week Size per Entry'] = 1.0 * Size / Entries
| order by ['Table Name']  desc;
lastweek
| join kind=inner thisweek on ['Table Name']
| extend PercentageChange=todouble(['This Week Table Size']) * 100 / todouble(['Last Week Table Size'])
| project ['Table Name'], ['Last Week Table Size'], ['This Week Table Size'], PercentageChange
| sort by PercentageChange desc

//Detect anomalies in the amount of data being ingested into your Sentinel workspace

//Data connector required for this query - Usage (generated automatically on a log analytics workspace)

//Sensitivity = the lower the number the more sensitive the anomaly detection is, i.e it will find more anomalies, default is 1.5
let sensitivity = 1.5;
//Threshold = set a threshold to account for low volume anomailies, i.e moving from 1 GB of data to 2 GB. This example uses tables larger than 2 GB every 3 hours as a threshold
let threshold = 2;
//First find the anomalies by creating a series of all the data ingestion and using series_decompose_anomalies
let outliers=
Usage
| where IsBillable = true
| make-series TableSize=sum(Quantity / 1024) default=0 on TimeGenerated from ago(7d) to now() step 3h by DataType
| extend outliers=series_decompose_anomalies(TableSize, sensitivity)
| mv-expand TimeGenerated, TableSize, outliers
| where outliers == 1 and TableSize > threshold
//Optionally visualize the anomalies - remove everything below this line to just retrieve the data instead of visualizing
| distinct DataType;
Usage
| where IsBillable = true
| where DataType in (outliers)
| make-series TableSize=sum(Quantity / 1024) default=0 on TimeGenerated from ago(7d) to now() step 3h by DataType
| render timechart with (ytitle="Table Size",title="Anomalous data ingestion")

//Detect when new tables have been written to in the last week compared to the last 90 days

//Data connector required for this query - query will automatically union any data you have

let existingtables=
    union withsource=_TableName *
    | where TimeGenerated > ago(90d) and TimeGenerated < ago(7d)
    | distinct Type;
let newtables=
    union withsource=_TableName *
    | where TimeGenerated > ago(7d)
    | summarize ['First Log Received'] = min(TimeGenerated) by Type
    | project Type, ['First Log Received'];
existingtables
| join kind=rightanti newtables on Type

//Calculate the size of the combined Device* tables from Defender for Endpoint by device name

//Data connector required for this query - M365 Defender - Device* tables

union withsource=_TableName Device*
| where TimeGenerated > ago(7d)
| summarize
    Entries = count(), Size = sum(_BilledSize)
    by DeviceName
| project
    ['Device Name'] = DeviceName,
    ['Table Size'] = Size,
    ['Table Entries'] = Entries,
    ['Size per Entry'] = 1.0 * Size / Entries
| order by ['Table Size']  desc

AuditLogs
| where LoggedByService =~ "PIM"
| where Category =~ "RoleManagement"
| where ActivityDisplayName has "Disable PIM Alert"
| extend IpAddress = case(
  isnotempty(tostring(parse_json(tostring(InitiatedBy.user)).ipAddress)) and tostring(parse_json(tostring(InitiatedBy.user)).ipAddress) != 'null', tostring(parse_json(tostring(InitiatedBy.user)).ipAddress), 
  isnotempty(tostring(parse_json(tostring(InitiatedBy.app)).ipAddress)) and tostring(parse_json(tostring(InitiatedBy.app)).ipAddress) != 'null', tostring(parse_json(tostring(InitiatedBy.app)).ipAddress),
  'Not Available')
| extend InitiatedBy = iff(isnotempty(tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)), 
  tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName), tostring(parse_json(tostring(InitiatedBy.app)).displayName)), UserRoles = tostring(parse_json(tostring(InitiatedBy.user)).ipAddress)
| project InitiatedBy, ActivityDateTime, ActivityDisplayName, IpAddress, AADOperationType, AADTenantId, ResourceId, CorrelationId, Identity
| extend AccountName = tostring(split(InitiatedBy, "@")[0]), AccountUPNSuffix = tostring(split(InitiatedBy, "@")[1])

SecurityAlert 
| where AlertName == "Impossible travel activity"
| extend Extprop = parsejson(Entities)
| mv-expand Extprop
| extend Extprop = parsejson(Extprop)
| extend CmdLine = iff(Extprop['Type']=="process", Extprop['CommandLine'], '')
| extend File = iff(Extprop['Type']=="file", Extprop['Name'], '')
| extend Account = Extprop['Name']
| extend Domain = Extprop['UPNSuffix']
| extend Account = iif(isnotempty(Domain) and Extprop['Type']=="account", tolower(strcat(Account, "@", Domain)), iif(Extprop['Type']=="account", tolower(Account), ""))
| extend IpAddress = iff(Extprop["Type"] == "ip",Extprop['Address'], '')
| extend Process = iff(isnotempty(CmdLine), CmdLine, File)
| project TimeGenerated,Account,IpAddress,CompromisedEntity,Description,ProviderName,ResourceId
| join kind=inner
(
OfficeActivity
| where Operation =~ "Add-MailboxPermission"
| extend value = tostring(parse_json(Parameters)[3].Value)
| where value contains "FullAccess"
| where ResultStatus == "True"
| project Parameters,TimeGenerated,value,RecordType,Operation,OrganizationId,UserType,UserKey,OfficeWorkload,ResultStatus,OfficeObjectId,UserId,ClientIP,ExternalAccess,OriginatingServer,OrganizationName,TenantId,ElevationTime,SourceSystem,OfficeId,OfficeTenantId,Type,SourceRecordId
) on $left.Account == $right.UserId
| join kind=inner
(
AuditLogs
| where ActivityDisplayName =~ "Add eligible member to role in PIM requested (timebound)"
| where AADOperationType =~ "CreateRequestEligibleRole"
| where TargetResources has_any ("-PRIV", "Administrator", "Security")
| extend BuiltinRole = tostring(parse_json(TargetResources[0].displayName))
| extend CustomGroup = tostring(parse_json(TargetResources[3].displayName))
| extend TargetAccount = tostring(parse_json(TargetResources[2].displayName))
| extend Initiatedby = Identity
| project TimeGenerated, ActivityDisplayName, AADOperationType, Initiatedby, TargetAccount, BuiltinRole, CustomGroup, LoggedByService, Result, ResourceId, Id
| sort by TimeGenerated desc
) on $left.UserId == $right.Initiatedby
| extend AccountName = tostring(split(Initiatedby, "@")[0]), AccountUPNSuffix = tostring(split(Initiatedby, "@")[1])
| project AADOperationType, ActivityDisplayName,AccountName, AccountUPNSuffix, Id,ResourceId,IpAddress

let files1 = dynamic(["C:\\Windows\\TAPI\\lsa.exe", "C:\\Windows\\TAPI\\pa.exe", "C:\\Windows\\TAPI\\pc.exe", "C:\\Windows\\TAPI\\Rar.exe"]);
let files2 = dynamic(["svchost.exe","wdmsvc.exe"]);
let FileHash1 = dynamic(["43109fbe8b752f7a9076eaafa417d9ae5c6e827cd5374b866672263fdebd5ec3", "ab50d8d707b97712178a92bbac74ccc2a5699eb41c17aa77f713ff3e568dcedb", "010e32be0f86545e116a8bc3381a8428933eb8789f32c261c81fd5e7857d4a77",         "56cd102b9fc7f3523dad01d632525ff673259dbc9a091be0feff333c931574f7"]);
let FileHash2 = dynamic(["2a1044e9e6e87a032f80c6d9ea6ae61bbbb053c0a21b186ecb3b812b49eb03b7", "9ab7e99ed84f94a7b6409b87e56dc6e1143b05034a5e4455e8c555dbbcd0d2dd", "18a072ccfab239e140d8f682e2874e8ff19d94311fc8bb9564043d3e0deda54b"]);
DeviceProcessEvents
| where ( FolderPath has_any (files1) and SHA256 has_any (FileHash1)) or (FolderPath has_any (files2) and SHA256 has_any (FileHash2))
| extend DvcId = DeviceId
| join kind=leftouter (SecurityAlert
| where ProviderName =~ "MDATP"
| extend ThreatName = tostring(parse_json(ExtendedProperties).ThreatName)
| mv-expand todynamic(Entities)
| extend DvcId = tostring(parse_json(Entities).MdatpDeviceId)
| where isnotempty(DvcId)
// Higher risk score are for Defender alerts related to threat actor
| extend AlertRiskScore = iif(ThreatName has_any ("Backdoor:MSIL/ShellClient.A", "Backdoor:MSIL/ShellClient.A!dll", "Trojan:MSIL/Mimikatz.BA!MTB"), 1.0, 0.5)
| project DvcId, AlertRiskScore) on DvcId
| extend AlertRiskScore = iif(isempty(AlertRiskScore), 0.0, AlertRiskScore)
| extend InitiatingProcessAccount = strcat(InitiatingProcessAccountDomain, "\\", InitiatingProcessAccountName)
| extend HostName = tostring(split(DeviceName, ".")[0]), DomainIndex = toint(indexof(DeviceName, '.'))
| extend HostNameDomain = iff(DomainIndex != -1, substring(DeviceName, DomainIndex + 1), DeviceName)
| extend timestamp = TimeGenerated

let files1 = dynamic(["C:\\Windows\\TAPI\\lsa.exe", "C:\\Windows\\TAPI\\pa.exe", "C:\\Windows\\TAPI\\pc.exe", "C:\\Windows\\TAPI\\Rar.exe"]);
let files2 = dynamic(["svchost.exe","wdmsvc.exe"]);
let FileHash1 = dynamic(["43109fbe8b752f7a9076eaafa417d9ae5c6e827cd5374b866672263fdebd5ec3", "ab50d8d707b97712178a92bbac74ccc2a5699eb41c17aa77f713ff3e568dcedb", "010e32be0f86545e116a8bc3381a8428933eb8789f32c261c81fd5e7857d4a77",     "56cd102b9fc7f3523dad01d632525ff673259dbc9a091be0feff333c931574f7"]);
let FileHash2 = dynamic(["2a1044e9e6e87a032f80c6d9ea6ae61bbbb053c0a21b186ecb3b812b49eb03b7", "9ab7e99ed84f94a7b6409b87e56dc6e1143b05034a5e4455e8c555dbbcd0d2dd", "18a072ccfab239e140d8f682e2874e8ff19d94311fc8bb9564043d3e0deda54b"]);
imProcessCreate
| where ((Process has_any (files1)) and (ActingProcessSHA256 has_any (FileHash1))) or ((Process has_any (files2)) and (ActingProcessSHA256 has_any (FileHash2)))
// Increase risk score if recent alerts for the host
| join kind=leftouter (
  SecurityAlert
  | where ProviderName =~ "MDATP"
  | extend ThreatName = tostring(parse_json(ExtendedProperties).ThreatName)
  | mv-expand todynamic(Entities)
  | extend DvcId = tostring(parse_json(Entities).MdatpDeviceId)
  | where isnotempty(DvcId)
  // Higher risk score are for Defender alerts related to threat actor
  | extend AlertRiskScore = iif(ThreatName has_any ("Backdoor:MSIL/ShellClient.A", "Backdoor:MSIL/ShellClient.A!dll", "Trojan:MSIL/Mimikatz.BA!MTB"), 1.0, 0.5)
  | project DvcId, AlertRiskScore) 
  on DvcId
| extend AlertRiskScore = iif(isempty(AlertRiskScore), 0.0, AlertRiskScore)
| extend AccountName = tostring(split(ActorUsername, @'\')[1]), AccountNTDomain = tostring(split(ActorUsername, @'\')[0])
| extend HostName = tostring(split(Dvc, ".")[0]), DomainIndex = toint(indexof(Dvc, '.'))
| extend HostNameDomain = iff(DomainIndex != -1, substring(Dvc, DomainIndex + 1), Dvc)
| project-away DomainIndex

union isfuzzy=true
    (DeviceFileEvents
    | where ActionType == "FileCreated"
    | where FileName endswith ".h0lyenc" or FolderPath == "C:\\FOR_DECRYPT.html"
    | summarize StartTime = min(TimeGenerated), EndTime = max(TimeGenerated)
        by
        AccountName = InitiatingProcessAccountName, AccountDomain = InitiatingProcessAccountDomain,
        DeviceName,
        Type,
        InitiatingProcessId,
        FileName,
        FolderPath,
        EventType = ActionType,
        Commandline = InitiatingProcessCommandLine,
        InitiatingProcessFileName,
        InitiatingProcessSHA256,
        FileHashCustomEntity = SHA256,
        AlgorithmCustomEntity = "SHA256"
    | extend HostName = tostring(split(DeviceName, ".")[0]), DomainIndex = toint(indexof(DeviceName, '.'))
    | extend HostNameDomain = iff(DomainIndex != -1, substring(DeviceName, DomainIndex + 1), DeviceName)
    ),
    (imFileEvent
    | where EventType == "FileCreated"
    | where TargetFilePath endswith ".h0lyenc" or TargetFilePath == "C:\\FOR_DECRYPT.html"
    | summarize StartTime = min(TimeGenerated), EndTime = max(TimeGenerated)
        by
        ActorUsername,
        DvcHostname,
        DvcDomain,
        DvcId,
        Type,
        EventType,
        FileHashCustomEntity = TargetFileSHA256,
        Hash,
        TargetFilePath,
        Commandline = ActingProcessCommandLine,
        AlgorithmCustomEntity = "SHA256"
    | extend AccountName = tostring(split(ActorUsername, @'\')[1]), AccountDomain = tostring(split(ActorUsername, @'\')[0])
    | extend HostName = DvcHostname, HostNameDomain = DvcDomain
    | extend DeviceName = strcat(DvcHostname, ".", DvcDomain )
    )

//Summarize attack surface reduction audit hits for each device

//Data connector required for this query - M365 Defender - Device* tables

DeviceEvents
| where TimeGenerated > ago (1d)
| where ActionType startswith "Asr"
| extend isAudit = tostring(AdditionalFields.IsAudit)
| where isAudit = true
| project
    TimeGenerated,
    ActionType,
    DeviceName,
    FileName,
    InitiatingProcessAccountDomain,
    InitiatingProcessAccountName,
    InitiatingProcessCommandLine,
    InitiatingProcessParentFileName,
    ProcessTokenElevation
| summarize
    ['Total ASR audit hits']=count(),
    ['Distinct ASR audit rule hits']=dcount(ActionType),
    ['List of processes']=make_set(InitiatingProcessCommandLine)
    by DeviceName
| sort by ['Total ASR audit hits'] desc