Deployable detection rules

//Visualize the breakdown of 365 days of KQL
externaldata(Category: string, Count: int)
[
h@'https://gist.githubusercontent.com/reprise99/12487ffefee2c2c417e2706150e25b8e/raw/0679cbb29e43e370c7304bb9b3d0007042a8ad52/365daysofkql.csv'
] | sort by Count desc | render piechart

{
  "version": "Notebook/1.0",
  "items": [
    {
      "type": 11,
      "content": {
        "version": "LinkItem/1.0",
        "style": "tabs",
        "links": [
          {
            "id": "1a2013a0-0c04-4ade-a7b3-10b1b3a1691f",
            "cellValue": "tab",
            "linkTarget": "parameter",
            "linkLabel": "Azure AD Sign-Ins",
            "subTarget": "azureadsign",
            "style": "link"
          },
          {
            "id": "b25342e2-48b6-4369-b4aa-4c4b100f5417",
            "cellValue": "tab",
            "linkTarget": "parameter",
            "linkLabel": "Azure AD Audit",
            "subTarget": "azureadaudit",
            "style": "link"
          },
          {
            "id": "2138f218-9a99-44ea-9cde-c981aefd1ea7",
            "cellValue": "tab",
            "linkTarget": "parameter",
            "linkLabel": "MFA Analytics",
            "subTarget": "mfa",
            "style": "link"
          },
          {
            "id": "1f23a0b2-df81-4c09-ab67-fcecf73f820e",
            "cellValue": "tab",
            "linkTarget": "parameter",
            "linkLabel": "Office 365 Analytics",
            "subTarget": "o365",
            "style": "link"
          },
          {
            "id": "55414398-f3a6-4170-b967-302f11d52be8",
            "cellValue": "tab",
            "linkTarget": "parameter",
            "linkLabel": "Sentinel Analytics",
            "subTarget": "sentinel",
            "style": "link"
          }
        ]
      },
      "name": "links - 0"
    },
    {
      "type": 9,
      "content": {
        "version": "KqlParameterItem/1.0",
        "parameters": [
          {
            "id": "7505c02c-5bdc-46d9-8ff8-72e5173ed77a",
            "version": "KqlParameterItem/1.0",
            "name": "Timerange",
            "label": "Time Range",
            "type": 4,
            "value": {
              "durationMs": 2592000000
            },
            "typeSettings": {
              "selectableValues": [
                {
                  "durationMs": 259200000
                },
                {
                  "durationMs": 604800000
                },
                {
                  "durationMs": 1209600000
                },
                {
                  "durationMs": 2419200000
                },
                {
                  "durationMs": 2592000000
                },
                {
                  "durationMs": 5184000000
                },
                {
                  "durationMs": 7776000000
                }
              ]
            },
            "timeContext": {
              "durationMs": 86400000
            }
          }
        ],
        "style": "pills",
        "queryType": 0,
        "resourceType": "microsoft.operationalinsights/workspaces"
      },
      "customWidth": "25",
      "name": "parameters - 2"
    },
    {
      "type": 12,
      "content": {
        "version": "NotebookGroup/1.0",
        "groupType": "editable",
        "items": [
          {
            "type": 3,
            "content": {
              "version": "KqlItem/1.0",
              "query": "union SigninLogs, AAD*\r\n| where TimeGenerated {Timerange}\r\n| summarize count() by Type, bin(TimeGenerated, 1d)",
              "size": 1,
              "title": "Total Azure AD Signins per day",
              "queryType": 0,
              "resourceType": "microsoft.operationalinsights/workspaces",
              "visualization": "areachart"
            },
            "name": "query - 3"
          },
          {
            "type": 3,
            "content": {
              "version": "KqlItem/1.0",
              "query": "SigninLogs\r\n| where TimeGenerated {Timerange}\r\n| project TimeGenerated, ResultType, ConditionalAccessPolicies\r\n| where ResultType == 53003\r\n| extend FailedPolicy = tostring(ConditionalAccessPolicies[0].displayName)\r\n| where isnotempty(FailedPolicy)\r\n| summarize FailureCount=count()by FailedPolicy, bin(TimeGenerated, 1d)",
              "size": 1,
              "title": "Conditional Access Policy Failures",
              "queryType": 0,
              "resourceType": "microsoft.operationalinsights/workspaces",
              "visualization": "unstackedbar"
            },
            "name": "query - 0"
          },
          {
            "type": 3,
            "content": {
              "version": "KqlItem/1.0",
              "query": "AADUserRiskEvents\r\n| where TimeGenerated {Timerange}\r\n| summarize RiskEvents=count() by RiskEventType, bin(TimeGenerated, 1d)\r\n| where isnotempty( RiskEvents)\r\n| render timechart ",
              "size": 1,
              "title": "Risk Event Types per day",
              "queryType": 0,
              "resourceType": "microsoft.operationalinsights/workspaces",
              "visualization": "unstackedbar"
            },
            "name": "query - 1"
          },
          {
            "type": 3,
            "content": {
              "version": "KqlItem/1.0",
              "query": "SigninLogs\r\n| project TimeGenerated, AuthenticationDetails\r\n| where TimeGenerated {Timerange}\r\n| extend AuthMethod = tostring(parse_json(AuthenticationDetails)[0].authenticationMethod)\r\n| where AuthMethod != \"Previously satisfied\"\r\n| summarize\r\n    Password=countif(AuthMethod == \"Password\"),\r\n    Passwordless=countif(AuthMethod in (\"FIDO2 security key\", \"Passwordless phone sign-in\", \"Windows Hello for Business\"))\r\n    by bin(TimeGenerated,1d)",
              "size": 1,
              "title": "Password vs Passwordless Sign Ins per day",
              "queryType": 0,
              "resourceType": "microsoft.operationalinsights/workspaces",
              "visualization": "unstackedbar"
            },
            "name": "query - 2"
          },
          {
            "type": 3,
            "content": {
              "version": "KqlItem/1.0",
              "query": "let StartDate = now(-180d);\r\nlet EndDate = now();\r\nAuditLogs\r\n| where OperationName == \"Redeem external user invite\"\r\n| make-series TotalInvites=count() on TimeGenerated in range(StartDate, EndDate, 1d)\r\n| extend (RSquare, SplitIdx, Variance, RVariance, TrendLine)=series_fit_2lines(TotalInvites)\r\n| project TimeGenerated, TotalInvites, TrendLine",
              "size": 1,
              "title": "Guest invites redeemed per day with trend",
              "queryType": 0,
              "resourceType": "microsoft.operationalinsights/workspaces",
              "visualization": "timechart"
            },
            "name": "query - 4"
          }
        ]
      },
      "conditionalVisibility": {
        "parameterName": "tab",
        "comparison": "isEqualTo",
        "value": "azureadsign"
      },
      "name": "group - 1"
    },
    {
      "type": 12,
      "content": {
        "version": "NotebookGroup/1.0",
        "groupType": "editable",
        "items": [
          {
            "type": 3,
            "content": {
              "version": "KqlItem/1.0",
              "query": "AuditLogs\r\n| where TimeGenerated {Timerange}\r\n| where OperationName in (\"Reset password (self-service)\", \"Unlock user account (self-service)\")\r\n| summarize\r\n    PasswordReset=countif(OperationName == \"Reset password (self-service)\" and ResultDescription == \"Successfully completed reset.\"),\r\n    AccountUnlock=countif(OperationName == \"Unlock user account (self-service)\" and ResultDescription == \"Success\")\r\n    by bin(TimeGenerated,1d)",
              "size": 1,
              "title": "Self Service Password Resets & Account Unlocks per day",
              "queryType": 0,
              "resourceType": "microsoft.operationalinsights/workspaces",
              "visualization": "timechart"
            },
            "name": "query - 0"
          },
          {
            "type": 3,
            "content": {
              "version": "KqlItem/1.0",
              "query": "AuditLogs\r\n| where TimeGenerated {Timerange}\r\n| where OperationName in (\"Redeem external user invite\", \"Invite external user\")\r\n| summarize count() by OperationName, bin(TimeGenerated, 1d)",
              "size": 1,
              "title": "Guests Invites vs Redeemed per day",
              "queryType": 0,
              "resourceType": "microsoft.operationalinsights/workspaces",
              "visualization": "unstackedbar"
            },
            "name": "query - 1"
          },
          {
            "type": 3,
            "content": {
              "version": "KqlItem/1.0",
              "query": "AuditLogs\r\n| where TimeGenerated {Timerange}\r\n| where OperationName == \"Redeem external user invite\"\r\n| extend GuestEmail = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)\r\n| extend UserDomain = tostring(split(GuestEmail, \"@\")[1])\r\n| where isnotempty(UserDomain)\r\n| project UserDomain\r\n| summarize DomainCount=count()by UserDomain\r\n| where DomainCount > 15\r\n| sort by DomainCount desc ",
              "size": 0,
              "title": "Top Domains Redeeming Guest Invites",
              "queryType": 0,
              "resourceType": "microsoft.operationalinsights/workspaces",
              "visualization": "piechart"
            },
            "name": "query - 2"
          }
        ]
      },
      "conditionalVisibility": {
        "parameterName": "tab",
        "comparison": "isEqualTo",
        "value": "azureadaudit"
      },
      "name": "group - 4"
    },
    {
      "type": 12,
      "content": {
        "version": "NotebookGroup/1.0",
        "groupType": "editable",
        "items": [
          {
            "type": 3,
            "content": {
              "version": "KqlItem/1.0",
              "query": "SecurityIncident\r\n| where TimeGenerated {Timerange}\r\n| summarize IncidentSeverity=dcount(IncidentNumber)by Severity, bin(TimeGenerated,1d)",
              "size": 1,
              "title": "Microsoft Sentinel Incident Severity per day",
              "queryType": 0,
              "resourceType": "microsoft.operationalinsights/workspaces",
              "visualization": "linechart"
            },
            "name": "query - 0"
          },
          {
            "type": 3,
            "content": {
              "version": "KqlItem/1.0",
              "query": "SecurityIncident\r\n| where TimeGenerated {Timerange}\r\n| where Status == \"New\" and ModifiedBy == \"Incident created from alert\"\r\n| summarize count() by Title\r\n| sort by count_ desc\r\n| take 10",
              "size": 0,
              "title": "Top Sentinel Incidents Triggered",
              "queryType": 0,
              "resourceType": "microsoft.operationalinsights/workspaces",
              "visualization": "table",
              "gridSettings": {
                "formatters": [
                  {
                    "columnMatch": "count_",
                    "formatter": 3,
                    "formatOptions": {
                      "palette": "blue"
                    }
                  }
                ]
              }
            },
            "customWidth": "50",
            "name": "query - 1"
          },
          {
            "type": 3,
            "content": {
              "version": "KqlItem/1.0",
              "query": "SecurityIncident\r\n| where TimeGenerated > ago(180d)\r\n| where Status == \"New\" and ModifiedBy == \"Incident created from alert\"\r\n| summarize arg_max(TimeGenerated, *) by Title\r\n| extend ['Days Since Last Incident'] = datetime_diff(\"day\", now(), TimeGenerated)\r\n| project Title, ['Days Since Last Incident']\r\n| sort by ['Days Since Last Incident'] desc\r\n| take 10",
              "size": 0,
              "title": "Sentinel Incidents not Recently Triggered",
              "queryType": 0,
              "resourceType": "microsoft.operationalinsights/workspaces",
              "gridSettings": {
                "formatters": [
                  {
                    "columnMatch": "Days Since Last Incident",
                    "formatter": 3,
                    "formatOptions": {
                      "palette": "coldHot"
                    }
                  }
                ]
              }
            },
            "customWidth": "50",
            "name": "query - 2"
          }
        ]
      },
      "conditionalVisibility": {
        "parameterName": "tab",
        "comparison": "isEqualTo",
        "value": "sentinel"
      },
      "name": "group - 6"
    },
    {
      "type": 12,
      "content": {
        "version": "NotebookGroup/1.0",
        "groupType": "editable",
        "items": [
          {
            "type": 3,
            "content": {
              "version": "KqlItem/1.0",
              "query": "OfficeActivity\r\n| where TimeGenerated {Timerange}\r\n| project TimeGenerated, Operation\r\n| where Operation in (\"FileSyncDownloadedFull\",\"FileSyncUploadedFull\",\"FileDownloaded\",\"FileUploaded\")\r\n| summarize FilesDownloaded=countif(Operation in (\"FileDownloaded\",\"FileSyncDownloadedFull\")),FilesUploaded=countif(Operation in (\"FileSyncUploadedFull\",\"FileUploaded\")) by bin(TimeGenerated,1d)",
              "size": 1,
              "title": "File Uploads vs Downloads in Office 365 per day",
              "queryType": 0,
              "resourceType": "microsoft.operationalinsights/workspaces",
              "visualization": "unstackedbar"
            },
            "name": "query - 1"
          },
          {
            "type": 3,
            "content": {
              "version": "KqlItem/1.0",
              "query": "OfficeActivity\r\n| where TimeGenerated {Timerange}\r\n| where UserType == \"Regular\"\r\n| where CommunicationType == \"Team\"\r\n| where OfficeWorkload == \"MicrosoftTeams\" \r\n| where Operation in (\"MemberAdded\", \"MemberRemoved\")\r\n| extend User = tostring(Members[0].UPN)\r\n| where User contains \"#EXT\"\r\n| project TimeGenerated, Operation, User\r\n| summarize\r\n    GuestsAdded=countif(Operation == \"MemberAdded\"),\r\n    GuestsRemoved=countif(Operation == \"MemberRemoved\")\r\n    by bin(TimeGenerated,1d)",
              "size": 1,
              "title": "Guests Added vs Removed to Teams per day",
              "queryType": 0,
              "resourceType": "microsoft.operationalinsights/workspaces",
              "visualization": "unstackedbar"
            },
            "name": "query - 2"
          },
          {
            "type": 3,
            "content": {
              "version": "KqlItem/1.0",
              "query": "IdentityInfo\r\n| where TimeGenerated > ago(21d)\r\n| where UserType == \"Guest\"\r\n| summarize arg_max(TimeGenerated, *) by AccountUPN, MailAddress\r\n| project UserId=tolower(AccountUPN), MailAddress\r\n| join kind=inner (\r\n    OfficeActivity\r\n    | where TimeGenerated {Timerange}\r\n    | where Operation in (\"FileSyncDownloadedFull\", \"FileDownloaded\")\r\n    )\r\n    on UserId\r\n| extend username = tostring(split(UserId,\"#\")[0])\r\n| parse MailAddress with * \"@\" userdomain \r\n| summarize FileCount=count() by userdomain\r\n| sort by FileCount desc\r\n| take 10",
              "size": 0,
              "title": "Top Guest Domains Downloading from Office 365",
              "queryType": 0,
              "resourceType": "microsoft.operationalinsights/workspaces",
              "visualization": "table",
              "gridSettings": {
                "formatters": [
                  {
                    "columnMatch": "FileCount",
                    "formatter": 8,
                    "formatOptions": {
                      "palette": "magenta"
                    }
                  }
                ]
              }
            },
            "customWidth": "33",
            "name": "query - 0"
          },
          {
            "type": 3,
            "content": {
              "version": "KqlItem/1.0",
              "query": "SecurityAlert\r\n| where TimeGenerated {Timerange}\r\n| where ProviderName == \"OATP\"\r\n| where AlertName in (\"Email messages containing malicious URL removed after delivery​\",\"Email messages containing phish URLs removed after delivery\")\r\n| extend x = todynamic(Entities)\r\n| parse-where x with * '\"Url\":\"' MaliciousURL '\"' *\r\n| parse-where MaliciousURL with * \"//\" MaliciousDomain \"/\" *\r\n| project TimeGenerated, MaliciousDomain\r\n| summarize DomainCount=count() by MaliciousDomain\r\n| sort by DomainCount desc \r\n| take 10",
              "size": 0,
              "title": "Top Malicious Domains with Phishing Emails Removed",
              "queryType": 0,
              "resourceType": "microsoft.operationalinsights/workspaces",
              "visualization": "table",
              "gridSettings": {
                "formatters": [
                  {
                    "columnMatch": "DomainCount",
                    "formatter": 8,
                    "formatOptions": {
                      "palette": "orangeRed"
                    }
                  }
                ]
              }
            },
            "customWidth": "33",
            "name": "query - 3"
          },
          {
            "type": 3,
            "content": {
              "version": "KqlItem/1.0",
              "query": "SecurityAlert\r\n| where TimeGenerated {Timerange}\r\n| where ProviderName == \"OATP\"\r\n| where AlertName in (\"Email messages containing malicious URL removed after delivery​\", \"Email messages containing malicious file removed after delivery​\")\r\n| extend x = todynamic(Entities)\r\n| mv-expand x\r\n| parse-where x with * 'MailboxPrimaryAddress\":\"' User '\"' *\r\n| summarize PhishingCount=count()by User\r\n| order by PhishingCount",
              "size": 0,
              "title": "Top Users Targeted by Phishing",
              "queryType": 0,
              "resourceType": "microsoft.operationalinsights/workspaces",
              "gridSettings": {
                "formatters": [
                  {
                    "columnMatch": "PhishingCount",
                    "formatter": 8,
                    "formatOptions": {
                      "palette": "orangeRed"
                    }
                  }
                ]
              }
            },
            "customWidth": "33",
            "name": "query - 4"
          }
        ]
      },
      "conditionalVisibility": {
        "parameterName": "tab",
        "comparison": "isEqualTo",
        "value": "o365"
      },
      "name": "group - 7"
    },
    {
      "type": 12,
      "content": {
        "version": "NotebookGroup/1.0",
        "groupType": "editable",
        "items": [
          {
            "type": 3,
            "content": {
              "version": "KqlItem/1.0",
              "query": "SigninLogs\r\n| where TimeGenerated {Timerange}\r\n| where AuthenticationRequirement == \"multiFactorAuthentication\"\r\n| extend x=todynamic(AuthenticationDetails)\r\n| mv-expand x\r\n| project TimeGenerated, x\r\n| extend MFAResultStep = tostring(x.authenticationStepResultDetail)\r\n| summarize\r\n    MFARequired=countif(MFAResultStep == \"MFA completed in Azure AD\"),\r\n    PreviouslySatisfied=countif(MFAResultStep == \"MFA requirement satisfied by claim in the token\")\r\n    by bin(TimeGenerated, 1d)",
              "size": 1,
              "title": "MFA Challenge vs MFA Previously Satisfied per day",
              "queryType": 0,
              "resourceType": "microsoft.operationalinsights/workspaces",
              "visualization": "timechart"
            },
            "name": "query - 2"
          },
          {
            "type": 3,
            "content": {
              "version": "KqlItem/1.0",
              "query": "SigninLogs\r\n| where TimeGenerated {Timerange}\r\n| where AuthenticationRequirement == \"multiFactorAuthentication\"\r\n| extend x=todynamic(AuthenticationDetails)\r\n| mv-expand x\r\n| project TimeGenerated, x\r\n| where x.RequestSequence != \"1\"\r\n| where x.authenticationStepRequirement == \"Multi-factor authentication\"\r\n| extend MFAMethod = tostring(x.authenticationMethod)\r\n| summarize MFAMethodCount=count() by MFAMethod, bin(TimeGenerated, 1d)\r\n| where MFAMethod != \"Previously satisfied\" and isnotempty(MFAMethod)",
              "size": 1,
              "title": "MFA methods per day ",
              "queryType": 0,
              "resourceType": "microsoft.operationalinsights/workspaces",
              "visualization": "areachart"
            },
            "name": "query - 3"
          },
          {
            "type": 3,
            "content": {
              "version": "KqlItem/1.0",
              "query": "SigninLogs\r\n| where TimeGenerated {Timerange}\r\n| where AuthenticationRequirement == \"multiFactorAuthentication\"\r\n| extend AuthMethod = tostring(parse_json(AuthenticationDetails)[0].authenticationMethod)\r\n| summarize count() by AuthMethod\r\n| where AuthMethod has_any (\"Text message\", \"Mobile app notification\", \"OATH verification code\", \"Passwordless phone sign-in\")",
              "size": 2,
              "title": "MFA Methods by Type",
              "queryType": 0,
              "resourceType": "microsoft.operationalinsights/workspaces",
              "visualization": "piechart"
            },
            "customWidth": "33",
            "name": "query - 0"
          },
          {
            "type": 3,
            "content": {
              "version": "KqlItem/1.0",
              "query": "SigninLogs\r\n| where TimeGenerated {Timerange}\r\n| where ResultType == 0\r\n| summarize\r\n    TotalCount=count(),\r\n    MFACount=countif(AuthenticationRequirement == \"multiFactorAuthentication\"),\r\n    nonMFACount=countif(AuthenticationRequirement == \"singleFactorAuthentication\")\r\n    by AppDisplayName\r\n| project\r\n    AppDisplayName,\r\n    TotalCount,\r\n    MFACount,\r\n    nonMFACount,\r\n    MFAPercentage=(todouble(MFACount) * 100 / todouble(TotalCount))\r\n| sort by MFAPercentage asc, TotalCount desc\r\n| take 10",
              "size": 0,
              "title": "Most Popular Apps with the least MFA coverage",
              "queryType": 0,
              "resourceType": "microsoft.operationalinsights/workspaces",
              "gridSettings": {
                "formatters": [
                  {
                    "columnMatch": "MFAPercentage",
                    "formatter": 8,
                    "formatOptions": {
                      "palette": "red"
                    },
                    "numberFormat": {
                      "unit": 1,
                      "options": {
                        "style": "decimal"
                      }
                    }
                  }
                ]
              }
            },
            "customWidth": "66",
            "name": "query - 1"
          }
        ]
      },
      "conditionalVisibility": {
        "parameterName": "tab",
        "comparison": "isEqualTo",
        "value": "mfa"
      },
      "name": "group - 7"
    }
  ],
  "fromTemplateId": "sentinel-UserWorkbook",
  "$schema": "https://github.com/Microsoft/Application-Insights-Workbooks/blob/master/schema/workbook.json"
}

let default_file_ext_blocklist = dynamic(['.ps1', '.vbs', '.bat', '.scr']); // Update this list as per your requirement
let custom_file_ext_blocklist=toscalar(_GetWatchlist('RiskyFileTypes')
  | extend Extension=column_ifexists("Extension", "")
  | where isnotempty(Extension)
  | summarize make_set(Extension)); // If you have an extensive list, you can also create a Watchlist that includes the file extensions you want to detect
let file_ext_blocklist = array_concat(default_file_ext_blocklist, custom_file_ext_blocklist);
_Im_WebSession(starttime=ago(10min), url_has_any=file_ext_blocklist, eventresult='Success')
| extend requestedFileName=tostring(split(tostring(parse_url(Url)["Path"]), '/')[-1])
| extend requestedFileExtension=extract(@'(\.\w+)$', 1, requestedFileName, typeof(string))
| where requestedFileExtension in (file_ext_blocklist)
| summarize
  EventStartTime=min(TimeGenerated),
  EventEndTime=max(TimeGenerated),
  EventCount=count()
  by SrcIpAddr, SrcUsername, SrcHostname, requestedFileName, Url
| extend
  Name = iif(SrcUsername contains "@", tostring(split(SrcUsername, '@', 0)[0]), SrcUsername),
  UPNSuffix = iif(SrcUsername contains "@", tostring(split(SrcUsername, '@', 1)[0]), "")

let threatCategory="Powershell";
let knownUserAgentsIndicators = materialize(externaldata(UserAgent:string, Category:string)
    [ @"https://raw.githubusercontent.com/Azure/Azure-Sentinel/master/Sample%20Data/Feeds/UnusualUserAgents.csv"]
        with(format="csv", ignoreFirstRecord=True));
let knownUserAgents=toscalar(knownUserAgentsIndicators | where Category==threatCategory | where isnotempty(UserAgent) | summarize make_list(UserAgent));
let customUserAgents=toscalar(_GetWatchlist("UnusualUserAgents") | where SearchKey==threatCategory | extend UserAgent=column_ifexists("UserAgent","") | where isnotempty(UserAgent) | summarize make_list(UserAgent));
let fullUAList = array_concat(knownUserAgents,customUserAgents);
_Im_WebSession(httpuseragent_has_any=fullUAList)
| project SrcIpAddr, Url, TimeGenerated, HttpUserAgent, SrcUsername
| extend AccountName = tostring(split(SrcUsername, "@")[0]), AccountUPNSuffix = tostring(split(SrcUsername, "@")[1])

let threatCategory="Cryptominer";
let knownUserAgentsIndicators = materialize(externaldata(UserAgent:string, Category:string)
    [ @"https://raw.githubusercontent.com/Azure/Azure-Sentinel/master/Sample%20Data/Feeds/UnusualUserAgents.csv"] 
        with(format="csv", ignoreFirstRecord=True));
let knownUserAgents=toscalar(knownUserAgentsIndicators | where Category==threatCategory | where isnotempty(UserAgent) | summarize make_list(UserAgent));
let customUserAgents=toscalar(_GetWatchlist("UnusualUserAgents") | where SearchKey==threatCategory | extend UserAgent=column_ifexists("UserAgent","") | where isnotempty(UserAgent) | summarize make_list(UserAgent));
let fullUAList = array_concat(knownUserAgents,customUserAgents);
_Im_WebSession(httpuseragent_has_any=fullUAList)
| summarize N_Events=count() by  SrcIpAddr, Url, TimeGenerated, HttpUserAgent, SrcUsername
| extend AccountName = tostring(split(SrcUsername, "@")[0]), AccountUPNSuffix = tostring(split(SrcUsername, "@")[1])

let threatCategory="Hacking Tool";
let knownUserAgentsIndicators = materialize(externaldata(UserAgent:string, Category:string)
    [ @"https://raw.githubusercontent.com/Azure/Azure-Sentinel/master/Sample%20Data/Feeds/UnusualUserAgents.csv"] 
        with(format="csv", ignoreFirstRecord=True));
let knownUserAgents=toscalar(knownUserAgentsIndicators | where Category==threatCategory | where isnotempty(UserAgent) | summarize make_list(UserAgent));
let customUserAgents=toscalar(_GetWatchlist("UnusualUserAgents") | where SearchKey==threatCategory | extend UserAgent=column_ifexists("UserAgent","") | where isnotempty(UserAgent) | summarize make_list(UserAgent));
let fullUAList = array_concat(knownUserAgents,customUserAgents);
_Im_WebSession(httpuseragent_has_any=fullUAList)
| project SrcIpAddr, Url, TimeGenerated, HttpUserAgent, SrcUsername
| extend AccountName = tostring(split(SrcUsername, "@")[0]), AccountUPNSuffix = tostring(split(SrcUsername, "@")[1])

//If you add "Microsoft-AzureADPasswordProtection-DCAgent/Admin" as a log source to Sentinel/Log Analytics you can query Azure AD Password Protection events
Event
| where Source == "Microsoft-AzureADPasswordProtection-DCAgent"
| where EventID in ("10014", "10015", "10016", "30002", "30004", "30026", "10024", "30008", "30010", "30028", "30024", "30003", "30005", "30027", "30022", "30007", "10025", "30009", "30029", "30023")

// change the starttime value for a longer period of known OIDs
let starttime = 1d;
// change the lookback value for a longer period of lookback for suspicious/abnormal
let lookback = 1h;
let OIDList = SecurityEvent
| where TimeGenerated >= ago(starttime)
| where EventSourceName == 'AD FS Auditing'
| where EventID == 501
| where EventData has '/eku'
| extend OIDs = extract_all(@"<Data>([\d+\.]+)</Data>", EventData)
| mv-expand OIDs
| extend OID = tostring(OIDs)
| extend OID_Length = strlen(OID)
| project TimeGenerated, Computer, EventSourceName, EventID, OID, OID_Length, EventData
;
OIDList
| where TimeGenerated >= ago(lookback)
| join kind=leftanti (
OIDList
| where TimeGenerated between (ago(starttime) .. ago(lookback))
| summarize by OID
) on OID
| extend HostName = tostring(split(Computer, ".")[0]), DomainIndex = toint(indexof(Computer, '.'))
| extend HostNameDomain = iff(DomainIndex != -1, substring(Computer, DomainIndex + 1), Computer)

union isfuzzy=true
(
 SecurityEvent
 | where EventID == 4738
 // 2089 value indicates the Don't Expire Password value has been set
 | where UserAccountControl has "%%2089"
 | extend Value_2089 = iff(UserAccountControl has "%%2089","'Don't Expire Password' - Enabled", "Not Changed")
 // 2050 indicates that the Password Not Required value is NOT set, this often shows up at the same time as a 2089 and is the recommended value.  This value may not be in the event.
 | extend Value_2050 = iff(UserAccountControl has "%%2050","'Password Not Required' - Disabled", "Not Changed")
 // If value %%2082 is present in the 4738 event, this indicates the account has been configured to logon WITHOUT a password. Generally you should only see this value when an account is created and only in Event 4720: Account Creation Event.
 | extend Value_2082 = iff(UserAccountControl has "%%2082","'Password Not Required' - Enabled", "Not Changed")
 | project StartTime = TimeGenerated, EventID, Activity, Computer, TargetAccount, TargetUserName, TargetDomainName, TargetSid, 
 AccountType, UserAccountControl, Value_2089, Value_2050, Value_2082, SubjectAccount, SubjectUserName, SubjectDomainName, SubjectUserSid
 ),
 (
 WindowsEvent
 | where EventID == 4738 and EventData has '2089'
 // 2089 value indicates the Don't Expire Password value has been set
 | extend UserAccountControl = tostring(EventData.UserAccountControl)
 | where UserAccountControl has "%%2089"
 | extend Value_2089 = iff(UserAccountControl has "%%2089","'Don't Expire Password' - Enabled", "Not Changed")
 // 2050 indicates that the Password Not Required value is NOT set, this often shows up at the same time as a 2089 and is the recommended value.  This value may not be in the event.
 | extend Value_2050 = iff(UserAccountControl has "%%2050","'Password Not Required' - Disabled", "Not Changed")
 // If value %%2082 is present in the 4738 event, this indicates the account has been configured to logon WITHOUT a password. Generally you should only see this value when an account is created and only in Event 4720: Account Creation Event.
 | extend Value_2082 = iff(UserAccountControl has "%%2082","'Password Not Required' - Enabled", "Not Changed")
 | extend Activity="4738 - A user account was changed."
 | extend TargetAccount = strcat(EventData.TargetDomainName,"\\", EventData.TargetUserName)
 | extend TargetSid = tostring(EventData.TargetSid)
 | extend SubjectAccount = strcat(EventData.SubjectDomainName,"\\", EventData.SubjectUserName)
 | extend SubjectUserSid = tostring(EventData.SubjectUserSid)
 | extend AccountType=case(SubjectAccount endswith "$" or SubjectUserSid in ("S-1-5-18", "S-1-5-19", "S-1-5-20"), "Machine", isempty(SubjectUserSid), "", "User")
 | project StartTime = TimeGenerated, EventID, Activity, Computer, TargetAccount, TargetUserName = tostring(EventData.TargetUserName), TargetDomainName = tostring(EventData.TargetDomainName), TargetSid, 
 AccountType, UserAccountControl, Value_2089, Value_2050, Value_2082, SubjectAccount, SubjectDomainName = tostring(EventData.SubjectDomainName), SubjectUserName = tostring(EventData.SubjectUserName), SubjectUserSid = tostring(EventData.SubjectUserSid)
 )
 | extend HostName = tostring(split(Computer, ".")[0]), DomainIndex = toint(indexof(Computer, '.'))
 | extend HostNameDomain = iff(DomainIndex != -1, substring(Computer, DomainIndex + 1), Computer)
 | project-away DomainIndex

(union isfuzzy=true 
(SecurityEvent 
| where EventID == 4662 // You need to create a SACL on the ADFS Policy Store DKM group for this event to be created. 
| where ObjectServer == 'DS'
| where OperationType == 'Object Access'
//| where ObjectName contains '<GUID of ADFS Policy Store DKM Group object' This is unique to the domain. Check description for more details.
| where ObjectType contains '5cb41ed0-0e4c-11d0-a286-00aa003049e2' // Contact Class
| where Properties contains '8d3bca50-1d7e-11d0-a081-00aa006c33ed' // Picture Attribute - Ldap-Display-Name: thumbnailPhoto
| extend AccountName = SubjectUserName, AccountDomain = SubjectDomainName
| extend timestamp = TimeGenerated, DeviceName = Computer
),
( WindowsEvent 
| where EventID == 4662 // You need to create a SACL on the ADFS Policy Store DKM group for this event to be created. 
| where EventData has_all('Object Access', '5cb41ed0-0e4c-11d0-a286-00aa003049e2','8d3bca50-1d7e-11d0-a081-00aa006c33ed') 
| extend ObjectServer = tostring(EventData.ObjectServer)
| where ObjectServer == 'DS'
| extend OperationType = tostring(EventData.OperationType)
| where OperationType == 'Object Access'
//| where ObjectName contains '<GUID of ADFS Policy Store DKM Group object' This is unique to the domain. Check description for more details.
| extend ObjectType = tostring(EventData.ObjectType)
| where ObjectType contains '5cb41ed0-0e4c-11d0-a286-00aa003049e2' // Contact Class
| extend Properties = tostring(EventData.Properties)
| where Properties contains '8d3bca50-1d7e-11d0-a081-00aa006c33ed' // Picture Attribute - Ldap-Display-Name: thumbnailPhoto
| extend AccountName = tostring(EventData.SubjectUserName), AccountDomain = tostring(EventData.SubjectDomainName)
| extend timestamp = TimeGenerated, DeviceName = Computer
),
(DeviceEvents
| where ActionType =~ "LdapSearch"
| where AdditionalFields.AttributeList contains "thumbnailPhoto"
| where AdditionalFields.DistinguishedName contains "CN=ADFS,CN=Microsoft,CN=Program Data" // Filter results to show only hits related to the ADFS AD container
| extend timestamp = TimeGenerated, AccountName = InitiatingProcessAccountName, AccountDomain = InitiatingProcessAccountDomain
)
)
| extend Account = strcat(AccountDomain, "\\", AccountName)

//Combined Azure Resource Graph and Log Analytics query - https://learn.microsoft.com/en-us/azure/azure-monitor/logs/azure-monitor-data-explorer-proxy#combine-azure-resource-graph-tables-with-a-log-analytics-workspace
//This looks for all Windows devices in Resource Graph and attempts to determine current log status

arg("").Resources 
| where type == "microsoft.compute/virtualmachines"
| extend OSType= tostring(parse_json(tostring(parse_json(tostring(properties.storageProfile)).osDisk)).osType)
| extend VMStatus = tostring(parse_json(tostring(parse_json(tostring(parse_json(tostring(properties.extended)).instanceView)).powerState)).displayStatus)
| where OSType contains "Windows"
| where VMStatus == "VM running"
| join kind=fullouter  (
    Heartbeat
    | where TimeGenerated > ago(30d)
    | where OSType contains "Windows" and isnotempty( Resource)
    | summarize arg_max(TimeGenerated, *) by ResourceId
    | project-rename LastLogTime=TimeGenerated
    )
    on $left.name == $right.Resource
| extend Status = case(isempty(id) and isnotempty(LastLogTime), strcat("Logs exist for this device, but it is no longer in Resource Graph - has it been decomissioned? ❓"),
                        isnotempty(id) and isempty(LastLogTime), strcat("Logs do not exist for this device, but it is in Resource Graph - do you need to onboard it? ❌"),
                        isnotempty(id) and isnotempty(LastLogTime), strcat("Logs exist for this machine and it is in Resource Graph ✅" ),
                        "unknown"
                        )
| extend DaysSinceLastLog=datetime_diff('day',now(),LastLogTime)
| project ResoureceGraphName=name, ResoureceGraphId=id,HeartBeatName=Resource,HeartBeatResourceId=ResourceId, Status, DaysSinceLastLog, LastLogTime

let Dev0530_threats = dynamic(["Trojan:Win32/SiennaPurple.A", "Ransom:Win32/SiennaBlue.A", "Ransom:Win32/SiennaBlue.B"]);
SecurityAlert
| where ProviderName == "MDATP"
| extend ThreatName = tostring(parse_json(ExtendedProperties).ThreatName)
| extend ThreatFamilyName = tostring(parse_json(ExtendedProperties).ThreatFamilyName)
| where ThreatName in~ (Dev0530_threats) or ThreatFamilyName in~ (Dev0530_threats)
| extend CompromisedEntity = tolower(CompromisedEntity)
| join kind=inner (DeviceInfo
    | extend DeviceName = tolower(DeviceName)
) on $left.CompromisedEntity == $right.DeviceName
| summarize by bin(TimeGenerated, 1d), DisplayName, ThreatName, ThreatFamilyName, PublicIP, AlertSeverity, Description, tostring(LoggedOnUsers), DeviceId, TenantId, CompromisedEntity, tostring(LoggedOnUsers), ProductName, Entities
| extend HostName = tostring(split(CompromisedEntity, ".")[0]), DomainIndex = toint(indexof(CompromisedEntity, '.'))
| extend HostNameDomain = iff(DomainIndex != -1, substring(CompromisedEntity, DomainIndex + 1), CompromisedEntity)
| project-away DomainIndex

let Europium_threats = dynamic(["TrojanDropper:ASP/WebShell!MSR", "Trojan:Win32/BatRunGoXml", "DoS:Win64/WprJooblash", "Ransom:Win32/Eagle!MSR", "Trojan:Win32/Debitom.A"]);
DeviceInfo
| extend DeviceName = tolower(DeviceName)
| join kind=inner ( SecurityAlert
| where ProviderName == "MDATP"
| extend ThreatName = tostring(parse_json(ExtendedProperties).ThreatName)
| extend ThreatFamilyName = tostring(parse_json(ExtendedProperties).ThreatFamilyName)
| where ThreatName in~ (Europium_threats) or ThreatFamilyName in~ (Europium_threats)
| extend CompromisedEntity = tolower(CompromisedEntity)
) on $left.DeviceName == $right.CompromisedEntity
| summarize by DisplayName, ThreatName, ThreatFamilyName, PublicIP, AlertSeverity, Description, tostring(LoggedOnUsers), DeviceId, TenantId, bin(TimeGenerated, 1d), CompromisedEntity, tostring(LoggedOnUsers), ProductName, Entities
| extend HostName = tostring(split(CompromisedEntity, ".")[0]), DomainIndex = toint(indexof(CompromisedEntity, '.'))
| extend HostNameDomain = iff(CompromisedEntity != -1, substring(CompromisedEntity, DomainIndex + 1), CompromisedEntity)

let Hive_threats = dynamic(["Ransom:Win64/Hive", "Ransom:Win32/Hive"]);
DeviceInfo
| extend DeviceName = tolower(DeviceName)
| join kind=inner ( SecurityAlert
| where ProviderName == "MDATP"
| extend ThreatName = tostring(parse_json(ExtendedProperties).ThreatName)
| extend ThreatFamilyName = tostring(parse_json(ExtendedProperties).ThreatFamilyName)
| where ThreatName in~ (Hive_threats) or ThreatFamilyName in~ (Hive_threats)
| extend CompromisedEntity = tolower(CompromisedEntity)
) on $left.DeviceName == $right.CompromisedEntity
| summarize by bin(TimeGenerated, 1d), DisplayName, ThreatName, ThreatFamilyName, PublicIP, AlertSeverity, Description, tostring(LoggedOnUsers), DeviceId, TenantId , CompromisedEntity, tostring(LoggedOnUsers), ProductName, Entities
| extend HostName = tostring(split(CompromisedEntity, ".")[0]), DomainIndex = toint(indexof(CompromisedEntity, '.'))
| extend HostNameDomain = iff(DomainIndex != -1, substring(CompromisedEntity, DomainIndex + 1), CompromisedEntity)
| project-away DomainIndex

//Query to find public IP addresses associated to AWS instances
AWSCloudTrail
| where EventName has "AllocateAddress"
| extend IPAssigned = tostring(parse_json(ResponseElements).publicIp)
| extend AllocationID = tostring(parse_json(ResponseElements).allocationId)
| project TimeGenerated, UserIdentityArn, UserIdentityAccountId, IPAssigned, AllocationID

let WellKnownLocalSID = "S-1-5-32-5[0-9][0-9]$";
let WellKnownGroupSID = "S-1-5-21-[0-9]*-[0-9]*-[0-9]*-5[0-9][0-9]$|S-1-5-21-[0-9]*-[0-9]*-[0-9]*-1102$|S-1-5-21-[0-9]*-[0-9]*-[0-9]*-1103$|S-1-5-21-[0-9]*-[0-9]*-[0-9]*-498$|S-1-5-21-[0-9]*-[0-9]*-[0-9]*-1000$";
let AC_Add =
(union isfuzzy=true
(SecurityEvent
// Event ID related to member addition.
| where EventID in (4728, 4732,4756)
| where TargetSid matches regex WellKnownLocalSID or TargetSid matches regex WellKnownGroupSID
| parse EventData with * '"MemberName">' * '=' AccountAdded ",OU" *
| where isnotempty(AccountAdded)
| extend GroupAddedTo = TargetUserName, AddingAccount = Account
| extend  AccountAdded_GroupAddedTo_AddingAccount = strcat(AccountAdded, "||", GroupAddedTo, "||", AddingAccount )
| project AccountAdded_GroupAddedTo_AddingAccount, AccountAddedTime = TimeGenerated
),
(WindowsEvent
// Event ID related to member addition.
| where EventID in (4728, 4732,4756)
| extend TargetSid = tostring(EventData.TargetSid)
| where TargetSid matches regex WellKnownLocalSID or TargetSid matches regex WellKnownGroupSID
| parse EventData.MemberName with * '"MemberName">' * '=' AccountAdded ",OU" *
| where isnotempty(AccountAdded)
| extend TargetUserName = tostring(EventData.TargetUserName)
| extend AddingAccount =  strcat(tostring(EventData.SubjectDomainName),"\\", tostring(EventData.SubjectUserName))
| extend GroupAddedTo = TargetUserName
| extend  AccountAdded_GroupAddedTo_AddingAccount = strcat(AccountAdded, "||", GroupAddedTo, "||", AddingAccount )
| project AccountAdded_GroupAddedTo_AddingAccount, AccountAddedTime = TimeGenerated
)
);
let AC_Remove =
( union isfuzzy=true
(SecurityEvent
// Event IDs related to member removal.
| where EventID in (4729,4733,4757)
| where TargetSid matches regex WellKnownLocalSID or TargetSid matches regex WellKnownGroupSID
| parse EventData with * '"MemberName">' * '=' AccountRemoved ",OU" *
| where isnotempty(AccountRemoved)
| extend GroupRemovedFrom = TargetUserName, RemovingAccount = Account
| extend AccountRemoved_GroupRemovedFrom_RemovingAccount = strcat(AccountRemoved, "||", GroupRemovedFrom, "||", RemovingAccount)
| project AccountRemoved_GroupRemovedFrom_RemovingAccount, AccountRemovedTime = TimeGenerated, Computer, AccountRemoved = tolower(AccountRemoved),
RemovingAccount, RemovingAccountLogonId = SubjectLogonId, GroupRemovedFrom = TargetUserName, TargetDomainName
),
(WindowsEvent
// Event IDs related to member removal.
| where EventID in (4729,4733,4757)
| extend TargetSid = tostring(EventData.TargetSid)
| where TargetSid matches regex WellKnownLocalSID or TargetSid matches regex WellKnownGroupSID
| parse EventData.MemberName with * '"MemberName">' * '=' AccountRemoved ",OU" *
| where isnotempty(AccountRemoved)
| extend TargetUserName = tostring(EventData.TargetUserName)
| extend RemovingAccount =  strcat(tostring(EventData.SubjectDomainName),"\\", tostring(EventData.SubjectUserName))
| extend GroupRemovedFrom = TargetUserName
| extend AccountRemoved_GroupRemovedFrom_RemovingAccount = strcat(AccountRemoved, "||", GroupRemovedFrom, "||", RemovingAccount)
| extend RemovedAccountLogonId= tostring(EventData.SubjectLogonId)
| extend TargetDomainName = tostring(EventData.TargetDomainName)
| project AccountRemoved_GroupRemovedFrom_RemovingAccount, AccountRemovedTime = TimeGenerated, Computer, AccountRemoved = tolower(AccountRemoved),
RemovingAccount, RemovedAccountLogonId, GroupRemovedFrom = TargetUserName, TargetDomainName
));
AC_Add
| join kind = inner AC_Remove 
on $left.AccountAdded_GroupAddedTo_AddingAccount == $right.AccountRemoved_GroupRemovedFrom_RemovingAccount
| extend DurationinSecondAfter_Removed = datetime_diff ('second', AccountRemovedTime, AccountAddedTime)
| where DurationinSecondAfter_Removed > 0
| extend HostName = tostring(split(Computer, ".")[0]), DomainIndex = toint(indexof(Computer, '.'))
| extend HostNameDomain = iff(DomainIndex != -1, substring(Computer, DomainIndex + 1), Computer)
| extend RemovedAccountName = tostring(split(AccountRemoved, @"\")[1]), RemovedAccountNTDomain = tostring(split(AccountRemoved, @"\")[0])
| extend RemovingAccountName = tostring(split(RemovingAccount, @"\")[1]), RemovingAccountNTDomain = tostring(split(RemovingAccount, @"\")[0])
| project-away DomainIndex

let core_domains = (SigninLogs
  | where TimeGenerated > ago(7d)
  | where ResultType == 0
  | extend domain = tolower(split(UserPrincipalName, "@")[1])
  | summarize by tostring(domain));
  let alternative_domains = (SigninLogs
  | where TimeGenerated > ago(7d)
  | where isnotempty(AlternateSignInName)
  | where ResultType == 0
  | extend domain = tolower(split(AlternateSignInName, "@")[1])
  | summarize by tostring(domain));
  AuditLogs
  | where TimeGenerated > ago(1d)
  | where OperationName =~ "Add User"
  | 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(iff(isnotempty(InitiatedBy.user.ipAddress), InitiatedBy.user.ipAddress, InitiatedBy.app.ipAddress))
  | extend UserAdded = tostring(TargetResources[0].userPrincipalName)
  | extend UserAddedDomain = case(
  UserAdded has "#EXT#", tostring(split(tostring(split(UserAdded, "#EXT#")[0]), "_")[1]),
  UserAdded !has "#EXT#", tostring(split(UserAdded, "@")[1]),
  UserAdded)
  | where UserAddedDomain !in (core_domains) and UserAddedDomain !in (alternative_domains)
  | extend AddedByName = case(
  InitiatingUserPrincipalName has "#EXT#", tostring(split(tostring(split(InitiatingUserPrincipalName, "#EXT#")[0]), "_")[0]),
  InitiatingUserPrincipalName !has "#EXT#", tostring(split(InitiatingUserPrincipalName, "@")[0]),
  InitiatingUserPrincipalName)
  | extend AddedByUPNSuffix = case(
  InitiatingUserPrincipalName has "#EXT#", tostring(split(tostring(split(InitiatingUserPrincipalName, "#EXT#")[0]), "_")[1]),
  InitiatingUserPrincipalName !has "#EXT#", tostring(split(InitiatingUserPrincipalName, "@")[1]),
  InitiatingUserPrincipalName)
  | extend UserAddedName = case(
  UserAdded has "#EXT#", tostring(split(tostring(split(UserAdded, "#EXT#")[0]), "_")[0]),
  UserAdded !has "#EXT#", tostring(split(UserAdded, "@")[0]),
  UserAdded)

let admin_users = (IdentityInfo
  | summarize arg_max(TimeGenerated, *) by AccountUPN
  | where AssignedRoles contains "admin"
  | summarize by tolower(AccountUPN));
  AuditLogs
  | where OperationName =~ "Admin registered security info"
  | where ResultReason =~ "Admin registered temporary access pass method for user"
  | extend TargetUserPrincipalName = tostring(TargetResources[0].userPrincipalName)
  | where tolower(TargetUserPrincipalName) in (admin_users)
  | extend TargetAadUserId = tostring(TargetResources[0].id)
  | extend InitiatingUserPrincipalName = tostring(InitiatedBy.user.userPrincipalName)
  | extend InitiatingAadUserId = tostring(InitiatedBy.user.id)
  | extend InitiatingIPAddress = tostring(InitiatedBy.user.ipAddress)
  | extend TargetAccountName = tostring(split(TargetUserPrincipalName, "@")[0]), TargetAccountUPNSuffix = tostring(split(TargetUserPrincipalName, "@")[1])
  | extend InitiatingAccountName = tostring(split(InitiatingUserPrincipalName, "@")[0]), InitiatingAccountUPNSuffix = tostring(split(InitiatingUserPrincipalName, "@")[1])

SecurityEvent
| where EventID == 5136 and EventData contains "<Data Name=\"ObjectDN\">CN=AdminSDHolder,CN=System"
| parse EventData with * 'ObjectDN">' ObjectDN "<" *
| summarize StartTime = min(TimeGenerated), EndTime = max(TimeGenerated) by Computer, SubjectAccount, SubjectUserSid, SubjectLogonId, ObjectDN
| extend HostName = tostring(split(Computer, ".")[0]), DomainIndex = toint(indexof(Computer, '.'))
| extend HostNameDomain = iff(DomainIndex != -1, substring(Computer, DomainIndex + 1), Computer)
| extend Name = tostring(split(SubjectAccount, "\\")[1]), NTDomain = tostring(split(SubjectAccount, "\\")[0])

//Returns any machines with a significant increase in syslog events over the last 5 days in every 30 minutes of data
let starttime = 5d;
let timeframe = 30m;
let Computers=Syslog
    | where TimeGenerated >= ago(starttime)
    | summarize EventCount=count() by Computer, bin(TimeGenerated, timeframe)
    | where EventCount > 1500
    | order by TimeGenerated
    | summarize EventCount=make_list(EventCount), TimeGenerated=make_list(TimeGenerated) by Computer
    | extend outliers=series_decompose_anomalies(EventCount, 2)
    | mv-expand TimeGenerated, EventCount, outliers
    | where outliers == 1
    | distinct Computer
;
Syslog
| where TimeGenerated >= ago(starttime)
| where Computer in (Computers)
| summarize EventCount=count() by Computer, bin(TimeGenerated, timeframe)
| render timechart

//Searches OfficeActivity table for anomalies in download actions and then retrives all USB file copy events by those users over the last week

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

let starttime = 7d;
let timeframe = 30m;
let operations = dynamic(["FileSyncDownloadedFull", "FileDownloaded"]);
let outlierusers=
    OfficeActivity
    | where TimeGenerated > ago(starttime)
    | where Operation in (['operations'])
    | extend UserPrincipalName = UserId
    | project TimeGenerated, UserPrincipalName
    | order by TimeGenerated
    | summarize Events=count()by UserPrincipalName, bin(TimeGenerated, timeframe)
    | summarize EventCount=make_list(Events), TimeGenerated=make_list(TimeGenerated) by UserPrincipalName
    | extend outliers=series_decompose_anomalies(EventCount, 3)
    | mv-expand TimeGenerated, EventCount, outliers
    | where outliers == 1
    | distinct UserPrincipalName;
let id=
    IdentityInfo
    | where AccountUPN in (outlierusers)
    | where TimeGenerated > ago (21d)
    | summarize arg_max(TimeGenerated, *) by AccountName
    | extend LoggedOnUser = AccountName
    | project LoggedOnUser, AccountUPN, JobTitle, EmployeeId, Country, City
    | join kind=inner 
        (
        DeviceInfo
        | where TimeGenerated > ago (21d)
        | summarize arg_max(TimeGenerated, *) by DeviceName
        | extend LoggedOnUser = tostring(LoggedOnUsers[0].UserName)
        )
        on LoggedOnUser
    | project LoggedOnUser, AccountUPN, JobTitle, Country, DeviceName, EmployeeId;
DeviceEvents
| where TimeGenerated > ago(7d)
| join kind=inner id on DeviceName
| where ActionType == "UsbDriveMounted"
| extend DriveLetter = tostring(todynamic(AdditionalFields).DriveLetter)
| join kind=inner (DeviceFileEvents
    | where TimeGenerated > ago(7d)
    | extend FileCopyTime = TimeGenerated
    | where ActionType == "FileCreated"
    | parse FolderPath with DriveLetter '\\' *
    | extend DriveLetter = tostring(DriveLetter)
    )
    on DeviceId, DriveLetter
| extend FileCopied = FileName1
| distinct
    DeviceName,
    DriveLetter,
    FileCopied,
    LoggedOnUser,
    AccountUPN,
    JobTitle,
    EmployeeId,
    Country

let known_locations = (SigninLogs
  | where TimeGenerated between(ago(7d)..ago(1d))
  | where ResultType == 0
  | extend LocationDetail = strcat(Location, "-", LocationDetails.state)
  | summarize by LocationDetail);
let known_asn = (SigninLogs
  | where TimeGenerated between(ago(7d)..ago(1d))
  | where ResultType == 0
  | summarize by AutonomousSystemNumber);
SigninLogs
| where TimeGenerated > ago(1d)
| where ResultType == 0
| where isempty(DeviceDetail.deviceId)
| where AuthenticationRequirement == "singleFactorAuthentication"
| extend LocationParsed = parse_json(LocationDetails), DeviceParsed = parse_json(DeviceDetail)
| extend City = tostring(LocationParsed.city), State = tostring(LocationParsed.state)
| extend LocationDetail = strcat(Location, "-", State)
| extend DeviceId = tostring(DeviceParsed.deviceId), DeviceName=tostring(DeviceParsed.displayName), OS=tostring(DeviceParsed.operatingSystem), Browser=tostring(DeviceParsed.browser)
| where AutonomousSystemNumber !in (known_asn) and LocationDetail !in (known_locations)
| project TimeGenerated, Type, UserId, UserDisplayName, UserPrincipalName, IPAddress, Location, State, City, ResultType, ResultDescription, AppId, AppDisplayName, AuthenticationRequirement, ConditionalAccessStatus, ResourceDisplayName, ClientAppUsed, Identity, HomeTenantId, ResourceTenantId, Status, UserAgent, DeviceId, DeviceName, OS, Browser, MfaDetail
| extend Name = tostring(split(UserPrincipalName,'@',0)[0]), UPNSuffix = tostring(split(UserPrincipalName,'@',1)[0])

let short_uaLength = 5;
let long_uaLength = 1000;
let c_threshold = 100;
W3CIISLog
// Exclude local IPs as these create noise
| where cIP !startswith "192.168." and cIP != "::1"
| where isnotempty(csUserAgent) and csUserAgent !in~ ("-", "MSRPC") and (string_size(csUserAgent) <= short_uaLength or string_size(csUserAgent) >= long_uaLength)
| extend csUserAgent_size = string_size(csUserAgent)
| summarize StartTimeUtc = min(TimeGenerated), EndTimeUtc = max(TimeGenerated), ConnectionCount = count() by Computer, sSiteName, sPort, csUserAgent, csUserAgent_size, csUserName , csMethod, csUriStem, sIP, cIP, scStatus, scSubStatus, scWin32Status
| where ConnectionCount < c_threshold
| extend HostName = tostring(split(Computer, ".")[0]), DomainIndex = toint(indexof(Computer, '.'))
| extend HostNameDomain = iff(DomainIndex != -1, substring(Computer, DomainIndex + 1), Computer)
| extend AccountName = tostring(split(csUserName, "@")[0]), AccountUPNSuffix = tostring(split(csUserName, "@")[1])

//The bigger the window the better the data sample size, as we use IP prevalence, more sample data is better.
//The minimum number of countries that the account has been accessed from [default: 2]
let minimumCountries = 2;
//The delta (%) between the largest in-use IP and the smallest [default: 95]
let deltaThreshold = 95;
//The maximum (%) threshold that the country appears in login data [default: 10]
let countryPrevalenceThreshold = 10;
//The time to project forward after the last login activity [default: 60min]
let projectedEndTime = 60m;
let queryfrequency = 1d;
let queryperiod = 14d;
let aadFunc = (tableName: string) {
    // Get successful signins to Teams
    let signinData =
        table(tableName)
        | where TimeGenerated > ago(queryperiod)
        | where AppDisplayName has "Teams" and ConditionalAccessStatus =~ "success"
        | extend Country = tostring(todynamic(LocationDetails)['countryOrRegion'])
        | where isnotempty(Country) and isnotempty(IPAddress);
    // Calculate prevalence of countries
    let countryPrevalence =
        signinData
        | summarize CountCountrySignin = count() by Country
        | extend TotalSignin = toscalar(signinData | summarize count())
        | extend CountryPrevalence = toreal(CountCountrySignin) / toreal(TotalSignin) * 100;
    // Count signins by user and IP address
    let userIpSignin =
        signinData
        | summarize CountIPSignin = count(), Country = any(Country), ListSigninTimeGenerated = make_list(TimeGenerated) by IPAddress, UserPrincipalName;
    // Calculate delta between the IP addresses with the most and minimum activity by user
    let userIpDelta =
        userIpSignin
        | summarize MaxIPSignin = max(CountIPSignin), MinIPSignin = min(CountIPSignin), DistinctCountries = dcount(Country), make_set(Country) by UserPrincipalName
        | extend UserIPDelta = toreal(MaxIPSignin - MinIPSignin) / toreal(MaxIPSignin) * 100;
    // Collect Team operations the user account has performed within a time range of the suspicious signins
    OfficeActivity
    | where TimeGenerated > ago(queryfrequency)
    | where Operation in~ ("TeamsAdminAction", "MemberAdded", "MemberRemoved", "MemberRoleChanged", "AppInstalled", "BotAddedToTeam")
    | where not (Operation in~ ("MemberAdded", "MemberRemoved") and CommunicationType in~ ("GroupChat", "OneonOne")) // These events have been noisy and are related to initiaing chat conversation and not admin operations.
    | project OperationTimeGenerated = TimeGenerated, UserId = tolower(UserId), Operation
    | join kind = inner(
        userIpDelta
        // Check users with activity from distinct countries
        | where DistinctCountries >= minimumCountries
        // Check users with high IP delta
        | where UserIPDelta >= deltaThreshold
        // Add information about signins and countries
        | join kind = leftouter userIpSignin on UserPrincipalName
        | join kind = leftouter countryPrevalence on Country
        // Check activity that comes from nonprevalent countries
        | where CountryPrevalence < countryPrevalenceThreshold
        | project
            UserPrincipalName,
            SuspiciousIP = IPAddress,
            UserIPDelta,
            SuspiciousSigninCountry = Country,
            SuspiciousCountryPrevalence = CountryPrevalence,
            EventTimes = ListSigninTimeGenerated
    ) on $left.UserId == $right.UserPrincipalName
    // Check the signins occured 60 min before the Teams operations
    | mv-expand SigninTimeGenerated = EventTimes
    | extend SigninTimeGenerated = todatetime(SigninTimeGenerated)
    | where OperationTimeGenerated between (SigninTimeGenerated .. (SigninTimeGenerated + projectedEndTime))
};
let aadSignin = aadFunc("SigninLogs");
let aadNonInt = aadFunc("AADNonInteractiveUserSignInLogs");
union isfuzzy=true aadSignin, aadNonInt
| summarize arg_max(SigninTimeGenerated, *) by UserPrincipalName, SuspiciousIP, OperationTimeGenerated
| summarize
    ActivitySummary = make_bag(pack(tostring(SigninTimeGenerated), pack("Operation", tostring(Operation), "OperationTime", OperationTimeGenerated)))
    by UserPrincipalName, SuspiciousIP, SuspiciousSigninCountry, SuspiciousCountryPrevalence
| extend AccountName = tostring(split(UserPrincipalName, "@")[0]), AccountUPNSuffix = tostring(split(UserPrincipalName, "@")[1])

let LookBack = 1h;
let Data = (
SigninLogs
| where TimeGenerated >= ago(LookBack)
| where parse_json(NetworkLocationDetails)[0].networkType != "trustedNamedLocation" // Excludes known tagged networks
// Counts the number of sign in events in the last hour every 15 minutes by IP
| make-series EventCounts = count() on TimeGenerated from ago(LookBack) to now() step 15m by IPAddress 
);
let AnomalyAlert = (
Data
| extend (Anomalies, Score, Baseline) = series_decompose_anomalies(EventCounts,1.5,-1,'linefit')
| mv-expand EventCounts,TimeGenerated,Anomalies to typeof(double),Baseline to typeof(long),Score to typeof(double)
| where Anomalies > 0
);
AnomalyAlert
| join kind = inner (SigninLogs
| where TimeGenerated between (ago(LookBack) .. now())
| where parse_json(NetworkLocationDetails)[0].networkType != "trustedNamedLocation"
| extend PasswordResult = tostring(parse_json(AuthenticationDetails).authenticationStepResultDetail)
| summarize UserCount = dcount(UserPrincipalName), UserList = make_set(UserPrincipalName), AppName = make_set(AppDisplayName), PasswordResult = make_list(PasswordResult) by IPAddress) on IPAddress
| where PasswordResult has "Correct Password"
| where UserCount > 1 // looks for events targeting more than one user.

//Visualize the most attacked hostname behind an Azure App Gateway/WAF

//Data connector required for this query - Azure Diagnostics (Application Gateways)

AzureDiagnostics
| where TimeGenerated > ago(30d)
| where ResourceType == "APPLICATIONGATEWAYS"
| where isnotempty(ruleId_s)
| summarize ['WAF Hit Count']=count() by hostname_s
| where isnotempty(hostname_s)
| sort by ['WAF Hit Count'] desc
| render barchart with (title="Most WAF Hits by Hostname", xtitle="Hostname")

//Visualize WAF rule actions such as allowed, blocked, detected and matched over time

//Data connector required for this query - Azure Diagnostics (Application Gateways)

AzureDiagnostics
| where TimeGenerated > ago(30d)
| where ResourceType == "APPLICATIONGATEWAYS"
| summarize count()by action_s, bin(TimeGenerated, 1h)
| where isnotempty(action_s)
| render timechart with (ytitle="WAF Hit Count", title="Web application firewall traffic over time")

let timeframe = ago(1d);
AppServiceAntivirusScanAuditLogs
| where ScanStatus == "Failed"
| extend timestamp = TimeGenerated

let timeframe = ago(1d);
AppServiceAntivirusScanAuditLogs
| where NumberOfInfectedFiles > 0
| extend timestamp = TimeGenerated

let Threshold = 3;
AzureDiagnostics
| where Category == "ApplicationGatewayFirewallLog"
| where action_s == "Matched"
| project transactionId_g, hostname_s, requestUri_s, TimeGenerated, clientIp_s, Message, details_message_s, details_data_s
| join kind = inner(
AzureDiagnostics
| where Category == "ApplicationGatewayFirewallLog"
| where action_s == "Blocked"
| parse Message with MessageText 'Total Inbound Score: ' TotalInboundScore ' - SQLI=' SQLI_Score ',XSS=' XSS_Score ',RFI=' RFI_Score ',LFI=' LFI_Score ',RCE=' RCE_Score ',PHPI=' PHPI_Score ',HTTP=' HTTP_Score ',SESS=' SESS_Score '): ' Blocked_Reason '; individual paranoia level scores:' Paranoia_Score
| where Blocked_Reason contains "SQL Injection Attack" and toint(SQLI_Score) >=10 and toint(TotalInboundScore) >= 15) on transactionId_g
| extend Uri = strcat(hostname_s,requestUri_s)
| summarize StartTime = min(TimeGenerated), EndTime = max(TimeGenerated), TransactionID = make_set(transactionId_g), Message = make_set(Message), Detail_Message = make_set(details_message_s), Detail_Data = make_set(details_data_s), Total_TransactionId = dcount(transactionId_g) by clientIp_s, Uri, action_s, SQLI_Score, TotalInboundScore
| where Total_TransactionId >= Threshold

let Threshold = 1;  
 AzureDiagnostics
 | where Category == "ApplicationGatewayFirewallLog"
 | where action_s == "Matched"
 | project transactionId_g, hostname_s, requestUri_s, TimeGenerated, clientIp_s, Message, details_message_s, details_data_s
 | join kind = inner(
 AzureDiagnostics
 | where Category == "ApplicationGatewayFirewallLog"
 | where action_s == "Blocked"
 | parse Message with MessageText 'Total Inbound Score: ' TotalInboundScore ' - SQLI=' SQLI_Score ',XSS=' XSS_Score ',RFI=' RFI_Score ',LFI=' LFI_Score ',RCE=' RCE_Score ',PHPI=' PHPI_Score ',HTTP=' HTTP_Score ',SESS=' SESS_Score '): ' Blocked_Reason '; individual paranoia level scores:' Paranoia_Score
 | where Blocked_Reason contains "XSS" and toint(TotalInboundScore) >=15 and toint(XSS_Score) >= 10 and toint(SQLI_Score) <= 5) on transactionId_g
 | extend Uri = strcat(hostname_s,requestUri_s)
 | summarize StartTime = min(TimeGenerated), EndTime = max(TimeGenerated), TransactionID = make_set(transactionId_g), Message = make_set(Message), Detail_Message = make_set(details_message_s), Detail_Data = make_set(details_data_s), Total_TransactionId = dcount(transactionId_g) by clientIp_s, Uri, action_s, SQLI_Score, XSS_Score, TotalInboundScore
 | where Total_TransactionId >= Threshold

AuditLogs
  | where Category == "ApplicationManagement"
  | where OperationName has_any ("Update Application", "Update Service principal")
  | where TargetResources has "AppIdentifierUri"
  | 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 mod_props = TargetResources[0].modifiedProperties
  | extend TargetAppName = tostring(TargetResources[0].displayName)
  | mv-expand mod_props
  | where mod_props.displayName has "AppIdentifierUri"
  | extend OldURI = tostring(mod_props.oldValue)
  | extend NewURI = tostring(mod_props.newValue)
  | extend UpdatedBy = iif(isnotempty(InitiatingAppName), InitiatingAppName, InitiatingUserPrincipalName)
  | extend InitiatingAccountName = tostring(split(InitiatingUserPrincipalName, "@")[0]), InitiatingAccountUPNSuffix = tostring(split(InitiatingUserPrincipalName, "@")[1])
  | project-reorder TimeGenerated, InitiatingAppName, InitiatingAppServicePrincipalId, InitiatingAadUserId, InitiatingUserPrincipalName, InitiatingIPAddress

AuditLogs
  | where Category =~ "ApplicationManagement"
  | where Result =~ "success"
  | where OperationName =~ 'Update Application'
  | where TargetResources has "AppAddress"
  | mv-expand TargetResources
  | mv-expand TargetResources.modifiedProperties
  | where TargetResources_modifiedProperties.displayName =~ "AppAddress"
  | extend Key = tostring(TargetResources_modifiedProperties.displayName)
  | extend NewValue = TargetResources_modifiedProperties.newValue
  | extend OldValue = TargetResources_modifiedProperties.oldValue
  | where isnotempty(Key) and isnotempty(NewValue)
  | project-reorder Key, NewValue, OldValue
  | extend NewUrls = extract_all('"Address":([^,]*)', tostring(NewValue))
  | extend OldUrls = extract_all('"Address":([^,]*)', tostring(OldValue))
  | extend AddedUrls = set_difference(NewUrls, OldUrls)
  | where array_length(AddedUrls) > 0
  | extend UserAgent = iif(tostring(AdditionalDetails[0].key) == "User-Agent", tostring(AdditionalDetails[0].value), "")
  | 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 AddedBy = iif(isnotempty(InitiatingUserPrincipalName), InitiatingUserPrincipalName, InitiatingAppName)
  | extend TargetAppName = tostring(TargetResources.displayName)
  | extend InitiatingAccountName = tostring(split(InitiatingUserPrincipalName, "@")[0]), InitiatingAccountUPNSuffix = tostring(split(InitiatingUserPrincipalName, "@")[1])
  | project-reorder TimeGenerated, TargetAppName, InitiatingAppName, InitiatingAppServicePrincipalId, InitiatingUserPrincipalName, InitiatingAadUserId, InitiatingIPAddress, AddedUrls, AddedBy, UserAgent

let timeframe = 1d;
let AccountAllowList = dynamic(['SYSTEM']);
let SubCategoryList = dynamic(["Logoff", "Account Lockout", "User Account Management", "Authorization Policy Change"]); // Add any Category in the list to be allowed or disallowed
let tokens = dynamic(["clear", "remove", "success:disable","failure:disable"]); 
(union isfuzzy=true
(
SecurityEvent
| where TimeGenerated >= ago(timeframe)
//| where Process =~ "auditpol.exe" 
| where CommandLine has_any (tokens)
| where AccountType !~ "Machine" and Account !in~ (AccountAllowList)
| parse CommandLine with * "/subcategory:" subcategorytoken
| extend SubCategory = tostring(split(subcategorytoken, "\"")[1]) , Toggle =  tostring(split(subcategorytoken, "\"")[2])
| where SubCategory in~ (SubCategoryList) //use in~ for inclusion or !in~ for exclusion
| where Toggle !in~ ("/failure:disable", " /success:enable /failure:disable") // use this filter if required to exclude certain toggles
| project TimeGenerated, Computer, Account, SubjectDomainName, SubjectUserName, Process, ParentProcessName, CommandLine, SubCategory, Toggle
| extend timestamp = TimeGenerated, AccountName = SubjectUserName, AccountDomain = SubjectDomainName, DeviceName = Computer
),
(
DeviceProcessEvents
| where TimeGenerated >= ago(timeframe)
// | where InitiatingProcessFileName =~ "auditpol.exe" 
| where InitiatingProcessCommandLine has_any (tokens)
| where AccountName !in~ (AccountAllowList)
| parse InitiatingProcessCommandLine with * "/subcategory:" subcategorytoken
| extend SubCategory = tostring(split(subcategorytoken, "\"")[1]) , Toggle =  tostring(split(subcategorytoken, "\"")[2])
| where SubCategory in~ (SubCategoryList) //use in~ for inclusion or !in~ for exclusion
| where Toggle !in~ ("/failure:disable", " /success:enable /failure:disable") // use this filter if required to exclude certain toggles
| project TimeGenerated, DeviceName, AccountName, InitiatingProcessAccountDomain, InitiatingProcessAccountName, InitiatingProcessFileName, InitiatingProcessParentFileName,  InitiatingProcessCommandLine, SubCategory, Toggle
| extend timestamp = TimeGenerated, AccountName = InitiatingProcessAccountName, AccountDomain = InitiatingProcessAccountDomain
),
(
Event
| where TimeGenerated > ago(timeframe)
| where Source == "Microsoft-Windows-Sysmon"
| where EventID == 1
| extend EventData = parse_xml(EventData).DataItem.EventData.Data
| mv-expand bagexpansion=array EventData
| evaluate bag_unpack(EventData)
| extend Key=tostring(['@Name']), Value=['#text']
| evaluate pivot(Key, any(Value), TimeGenerated, Source, EventLog, Computer, EventLevel, EventLevelName, EventID, UserName, RenderedDescription, MG, ManagementGroupName, Type, _ResourceId)
// | where OriginalFileName =~ "auditpol.exe"
| where CommandLine has_any (tokens)
| where User !in~ (AccountAllowList)
| parse CommandLine with * "/subcategory:" subcategorytoken
| extend SubCategory = tostring(split(subcategorytoken, "\"")[1]) , Toggle =  tostring(split(subcategorytoken, "\"")[2])
| where SubCategory in~ (SubCategoryList) //use in~ for inclusion or !in~ for exclusion
| where Toggle !in~ ("/failure:disable", " /success:enable /failure:disable") // use this filter if required to exclude certain toggles
| project TimeGenerated, Computer, User, Process, ParentImage, CommandLine, SubCategory, Toggle
| extend timestamp = TimeGenerated, AccountName = tostring(split(User, @'\')[1]), AccountUPNSuffix = tostring(split(User, @'\')[0]), DeviceName = Computer
)
)
| extend Account = strcat(AccountDomain, "\\", AccountName)

//Detect when an Azure AD Entitlement Package is created. You may want to review to see what resources and roles have been included in the package.

//Data connector required for this query - Azure Active Directory - Audit Logs

AuditLogs
| where TimeGenerated > ago(1d)
| where OperationName == "Create access package"
| where TargetResources[0].type == "AccessPackage"
| extend AccessPackageName = tostring(TargetResources[0].displayName)
| extend Actor = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
| project TimeGenerated, OperationName, AccessPackageName, Actor

//Finds Azure AD audit events from users who hold admin privileges (based on PIM activations in the last 60 days) and then finds any audit events events from those users in the last 7 days that have elevated risk associated to them
//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 Corissa K - https://www.linkedin.com/in/corissakoopmans/

//Data connector required for this query - Azure Active Directory - Audit Logs

let privroles = pack_array("Application Administrator","Authentication Administrator","Cloud Application Administrator","Conditional Access Administrator","Exchange Administrator","Global Administrator","Helpdesk Administrator","Hybrid Identity Administrator","Password Administrator","Privileged Authentication Administrator","Privileged Role Administrator","Security Administrator","SharePoint Administrator","User Administrator");
let privusers = AuditLogs 
| where TimeGenerated > ago(60d) and ActivityDisplayName == 'Add member to role completed (PIM activation)' and Category == "RoleManagement" 
| extend Caller = tostring(InitiatedBy.user.userPrincipalName) 
| extend Role = tostring(TargetResources[0].displayName) 
| where Role in (privroles) 
| distinct Caller;
let Activity =  AuditLogs
    | mv-expand ParsedFields = parse_json(TargetResources)
    | extend Target = tostring(ParsedFields.userPrincipalName), DisplayName = tostring(ParsedFields.displayName)
    | project TimeGenerated, Target, DisplayName, ParsedFields, OperationName;
    let RiskyUsers = SigninLogs
    | where RiskLevelDuringSignIn == "high"
    | where RiskState == "atRisk"
    | project TimeGenerated,UserPrincipalName, UserDisplayName, RiskDetail, RiskLevelDuringSignIn, RiskState;
    Activity
    | join kind=inner(RiskyUsers) on $left.DisplayName==$right.UserDisplayName
    | where TimeGenerated >= ago(7d) and UserPrincipalName in~ (privusers)
    | distinct UserDisplayName, RiskDetail, RiskLevelDuringSignIn, OperationName

//Detect when a domain is added or removed to either the allow or block list in Azure AD external identities

//Data connector required for this query - Azure Active Directory - Audit Logs

AuditLogs
| where OperationName == "Update policy"
| extend Actor = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
| mv-expand TargetResources
| extend modifiedProperties = parse_json(TargetResources).modifiedProperties
| mv-expand modifiedProperties  
| extend newValue = parse_json(modifiedProperties).newValue
| mv-expand todynamic(newValue)
| where newValue has "InvitationsAllowedAndBlockedDomainsPolicy"
| project TimeGenerated, OperationName, Actor, ['New Domain Policy']=newValue

//Alert when an application using Azure AD app proxy is set to pass through as it's pre-auth setting
AuditLogs
| where LoggedByService == "Application Proxy"
| where OperationName == "Update application"
| where Result == "success"
| extend PreAuthSetting = tostring(parse_json(tostring(parse_json(tostring(TargetResources[0].modifiedProperties))[0].newValue)))
| where PreAuthSetting == "Passthru"
| extend ['App Display Name'] = tostring(TargetResources[0].displayName)
| extend Actor = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
| extend ['Actor IP Address'] = tostring(parse_json(tostring(InitiatedBy.user)).ipAddress)
| project TimeGenerated, PreAuthSetting, ['App Display Name'], Actor, ['Actor IP Address']

//Detects when a BitLocker key is read in Azure AD and retrieves the device and key ids

//Data connector required for this query - Azure Active Directory - Audit Logs

AuditLogs
| where OperationName == "Read BitLocker key"
| extend Actor = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
| extend s = tostring(AdditionalDetails[0].value)
| parse s with * "ID: '" KeyId "'" *
| parse s with * "device: '" DeviceId "'"
| project TimeGenerated, OperationName, Actor, KeyId, DeviceId

//Detect when a custom security attribute is set on a user

//Data connector required for this query - Azure Active Directory - Audit Logs

AuditLogs
| where OperationName == "Update attribute values assigned to a user"
| extend x = tostring(parse_json(tostring(TargetResources[0].modifiedProperties))[0].displayName)
| extend ["Attribute Value"] = tostring(parse_json(tostring(parse_json(tostring(TargetResources[0].modifiedProperties))[0].newValue))[0])
| extend Target = tostring(TargetResources[0].userPrincipalName)
| extend Actor = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
| parse x with * '.' ['Attribute Set Name'] "_" *
| extend ["Attribute Name"]=split(x, "_")[1]
| project
    TimeGenerated,
    OperationName,
    Target,
    ['Attribute Set Name'],
    ['Attribute Name'],
    ['Attribute Value'],
    Actor

//Create a daily summary of activities completed by your Azure AD privileged users

//Data connector required for this query - Azure Active Directory - Audit Logs
//Data connector required for this query - Microsoft Sentinel UEBA

let timerange=30d;
IdentityInfo
| where TimeGenerated > ago(21d)
| summarize arg_max(TimeGenerated, *) by AccountUPN
| where isnotempty(AssignedRoles)
| where AssignedRoles != "[]"
| project Actor=AccountUPN
| join kind=inner (
    AuditLogs
    | where TimeGenerated > ago(timerange)
    | extend Actor = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
    | where isnotempty(Actor)
    )
    on Actor
| summarize AdminActivity = make_list(OperationName) by Actor, startofday(TimeGenerated)

//Create a daily summary of activities completed by your O365 admins

//Data connector required for this query - Office 365
//Data connector required for this query - Microsoft Sentinel UEBA

let timerange=14d;
IdentityInfo
| where TimeGenerated > ago(21d)
| summarize arg_max(TimeGenerated, *) by AccountUPN
| where AssignedRoles has_any ("Global Administrator", "Exchange Administrator", "Teams Administrator", "SharePoint Administrator")
| project UserId=AccountUPN
| join kind=inner (
    OfficeActivity
    | where TimeGenerated > ago(timerange)
    )
    on UserId
| summarize AdminActivities=make_list(Operation)by UserId, startofday(TimeGenerated)

//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 - Azure Active Directory - Audit Logs

AuditLogs
| where OperationName == "Set domain authentication"
| extend DomainName = tostring(TargetResources[0].displayName)
| extend Actor = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
| extend ActorIPAddress = tostring(parse_json(tostring(InitiatedBy.user)).ipAddress)
| extend mp=parse_json(TargetResources[0].modifiedProperties)
| mv-apply mp on (
where mp.displayName == "IssuerUri"
| extend Issuer=mp.newValue
)
| extend mp=parse_json(TargetResources[0].modifiedProperties)
| mv-apply mp on (
where mp.displayName == "LiveType"
| extend OldDomainType = mp.oldValue
| extend NewDomainType = mp.newValue
)
| project TimeGenerated, Actor, ActorIPAddress, 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}$"

//Alert when a user is assigned to a permanent active Azure AD role

//Data connector required for this query - Azure Active Directory - Audit Logs

AuditLogs
| where OperationName in ("Add member to role in PIM completed (permanent)","Add member to role in PIM completed (timebound)")
| where TargetResources[2].type == "User"
| extend Actor = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
| extend User = tostring(TargetResources[2].userPrincipalName)
| extend ['Azure AD Role Name'] = tostring(TargetResources[0].displayName)
| project TimeGenerated, Actor, User, ['Azure AD Role Name']

//Detect when Advanced Auditing is disabled for a user
//Reference - https://www.mandiant.com/resources/remediation-and-hardening-strategies-microsoft-365-defend-against-apt29-v13

//Data connector required for this query - Azure Active Directory - Audit Logs

AuditLogs
| where OperationName == "Update user"
| where Result == "success"
| extend Actor = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
| extend targetResources=parse_json(TargetResources)
| extend Target = tostring(TargetResources[0].userPrincipalName)
| extend ['Target ObjectId'] = tostring(TargetResources[0].id)
| mv-apply tr = targetResources on (
    extend targetResource = tr.displayName
    | mv-apply mp = tr.modifiedProperties on (
        where mp.displayName == "LicenseAssignmentDetail"
        | extend NewValue = tostring(mp.newValue)
        ))
| mv-expand todynamic(NewValue)
| where parse_json(tostring(NewValue.DisabledPlans))[0] == "2f442157-a11c-46b9-ae5b-6e39ff4e5849"
| project
    TimeGenerated,
    Actor,
    Target,
    ['Target ObjectId'],
    Activity="Advanced Auditing Disabled"

//Detect changes to Azure AD Conditional Access policies on weekends or outside of business hours

//Data connector required for this query - Azure Active Directory - Audit Logs

let Saturday = time(6.00:00:00);
let Sunday = time(0.00:00:00);
AuditLogs
| where OperationName has "conditional access"
// extend LocalTime to your time zone
| extend LocalTime=TimeGenerated + 5h
// Change hours of the day to suit your company, i.e this would find activations between 6pm and 6am
| where dayofweek(LocalTime) in (Saturday, Sunday) or hourofday(LocalTime) !between (6 .. 18)
| extend ['Conditional Access Policy Name'] = tostring(TargetResources[0].displayName)
| extend Actor = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
| project LocalTime, 
    OperationName, 
    ['Conditional Access Policy Name'], 
    Actor
| sort by LocalTime desc

//Detect when a new credential is added to an Azure AD application registration

//Data connector required for this query - Azure Active Directory - Audit Logs

AuditLogs
| where OperationName has "Update application – Certificates and secrets management"
| extend ApplicationName = tostring(TargetResources[0].displayName)
| extend ApplicationObjectId = tostring(TargetResources[0].id)
| extend Actor = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
| extend ActorIPAddress = tostring(parse_json(tostring(InitiatedBy.user)).ipAddress)
| project TimeGenerated, ApplicationName, ApplicationObjectId, Actor, ActorIPAddress

//Detects users who add, delete or update a Azure AD Conditional Access policy for the first time.
//First find users who have previously made CA policy changes, this example looks back 90 days

//Data connector required for this query - Azure Active Directory - Audit Logs

let knownusers=
    AuditLogs
    | where TimeGenerated > ago(90d) and TimeGenerated < ago(1d)
    | where OperationName in ("Update conditional access policy", "Add conditional access policy", "Delete conditional access policy")
    | extend Actor = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
    | distinct Actor;
//Find new events from users not in the known user list
AuditLogs
| where TimeGenerated > ago(1d)
| where OperationName in ("Update conditional access policy", "Add conditional access policy", "Delete conditional access policy")
| extend Actor = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
| extend ['Policy Name'] = tostring(TargetResources[0].displayName)
| extend ['Policy Id'] = tostring(TargetResources[0].id)
| where Actor !in (knownusers)
| project TimeGenerated, Actor, ['Policy Name'], ['Policy Id']

//Detects users who add a service principal to Azure AD for the first time.

//Data connector required for this query - Azure Active Directory - Audit Logs

let knownusers=
    AuditLogs
    | where TimeGenerated > ago(90d) and TimeGenerated < ago(1d)
    | where OperationName == "Add service principal"
    | extend Actor = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
    | where isnotempty(Actor)
    | distinct Actor;
AuditLogs
| where TimeGenerated > ago(1d)
| where OperationName == "Add service principal"
| extend Actor = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
| where isnotempty(Actor)
| where Actor !in (knownusers)
| extend AppId = tostring(AdditionalDetails[1].value)
| project TimeGenerated, Actor, AppId

//Detect when another Azure AD tenant is added to cross-tenant settings and for each tenant added, retrieve any domain names from your sign in data.
//First retrieve the event where a cross-tenant setting was added

//Data connector required for this query - Azure Active Directory - Audit Logs
//Data connector required for this query - Azure Active Directory - Signin Logs

AuditLogs
| where TimeGenerated > ago(1d)
| where OperationName == "Add a partner to cross-tenant access setting"
| where Result == "success"
| extend GuestTenantId = tostring(parse_json(tostring(parse_json(tostring(TargetResources[0].modifiedProperties))[0].newValue)))
| extend Actor = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
| project TimeGenerated, OperationName, Actor, GuestTenantId
//join back to Azure AD sign in logs for the last 30 days to retrieve inbound guest activity
| join kind=inner (
    SigninLogs
    | where TimeGenerated > ago (180d)
    | where UserType == "Guest"
    | where ResultType == 0
    | where AADTenantId != HomeTenantId and HomeTenantId != ResourceTenantId
    //Split all the domains belonging to inbound guest domains and summarize the list per TenantId
    | extend ['Guest Domains'] = split(UserPrincipalName, '@')[-1]
    | summarize ['Guest Domain Names']=make_set(['Guest Domains']) by HomeTenantId)
    //Join back to the audit even where the TenantId from the added setting matches the sign in data
    on $left.GuestTenantId == $right.HomeTenantId
| project-away HomeTenantId