Detect Potential Kerberoast Activities

let MaxCount = 70; //Number of requests per 2 minute timeframe, depending on org size.
IdentityLogonEvents
| where ActionType == "LogonSuccess"
| where Protocol == "Kerberos"
| extend json = todynamic(parse_json(tostring(AdditionalFields)))
| extend SPN = json.Spns,
       AttackTechniques = json.AttackTechniques
      | project-away json
| where isnotempty(SPN)
| where AttackTechniques has "T1558.003"
| mv-expand SPN
        | extend SPNType = tostring(extract(@"^\w+",0,tostring(SPN)))
| distinct tostring(SPN), DeviceName, AccountUpn, AccountSid, bin(TimeGenerated, 2m), ReportId, tostring(AttackTechniques)
| summarize count(), SPNS=(make_list(SPN, 100000)),ReportId=tostring((make_list(ReportId, 100000))[0]) by AccountUpn,AccountSid,DeviceName, bin(TimeGenerated, 2m), tostring(AttackTechniques)
| extend SPNS = (replace_regex(tostring(SPNS), @'[^\w+-\/]+', '')) 
| where count_ >= MaxCount
| extend HostName = iff(DeviceName has '.', substring(DeviceName, 0, indexof(DeviceName, '.')), DeviceName)
| extend DnsDomain = iff(DeviceName has '.', substring(DeviceName, indexof(DeviceName, '.') + 1), "")
| extend AccountDomain = tostring(split(AccountUpn, '@')[1]), AccountName = tostring(split(AccountUpn, '@')[0])

kind: Scheduled
OriginalUri: https://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Microsoft Defender XDR/Analytic Rules/Execution/PotentialKerberoastActivities.yaml
requiredDataConnectors:
- connectorId: MicrosoftThreatProtection
  dataTypes:
  - IdentityLogonEvents
query: |
  let MaxCount = 70; //Number of requests per 2 minute timeframe, depending on org size.
  IdentityLogonEvents
  | where ActionType == "LogonSuccess"
  | where Protocol == "Kerberos"
  | extend json = todynamic(parse_json(tostring(AdditionalFields)))
  | extend SPN = json.Spns,
         AttackTechniques = json.AttackTechniques
        | project-away json
  | where isnotempty(SPN)
  | where AttackTechniques has "T1558.003"
  | mv-expand SPN
          | extend SPNType = tostring(extract(@"^\w+",0,tostring(SPN)))
  | distinct tostring(SPN), DeviceName, AccountUpn, AccountSid, bin(TimeGenerated, 2m), ReportId, tostring(AttackTechniques)
  | summarize count(), SPNS=(make_list(SPN, 100000)),ReportId=tostring((make_list(ReportId, 100000))[0]) by AccountUpn,AccountSid,DeviceName, bin(TimeGenerated, 2m), tostring(AttackTechniques)
  | extend SPNS = (replace_regex(tostring(SPNS), @'[^\w+-\/]+', '')) 
  | where count_ >= MaxCount
  | extend HostName = iff(DeviceName has '.', substring(DeviceName, 0, indexof(DeviceName, '.')), DeviceName)
  | extend DnsDomain = iff(DeviceName has '.', substring(DeviceName, indexof(DeviceName, '.') + 1), "")
  | extend AccountDomain = tostring(split(AccountUpn, '@')[1]), AccountName = tostring(split(AccountUpn, '@')[0])  
id: 12134de5-361b-427c-a1a0-d43f40a593c4
severity: Medium
queryFrequency: 1h
relevantTechniques:
- T1558.003
tactics:
- CredentialAccess
triggerThreshold: 0
description: |
  This query aim to detect if someone requests service tickets (where count => maxcount)
  The query requires trimming to set a baseline level for MaxCount  
  Mitre Technique: Kerberoasting (T1558.003)
  @MattiasBorg82  
status: Available
name: Detect Potential Kerberoast Activities
version: 1.0.0
entityMappings:
- fieldMappings:
  - identifier: FullName
    columnName: DeviceName
  - identifier: HostName
    columnName: HostName
  - identifier: DnsDomain
    columnName: DnsDomain
  entityType: Host
- fieldMappings:
  - identifier: FullName
    columnName: AccountUpn
  - identifier: DnsDomain
    columnName: AccountDomain
  - identifier: Name
    columnName: AccountName
  entityType: Account
tag:
- Kerberoast
triggerOperator: gt
queryPeriod: 1h

{
  "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
  "contentVersion": "1.0.0.0",
  "parameters": {
    "workspace": {
      "type": "String"
    }
  },
  "resources": [
    {
      "apiVersion": "2023-02-01-preview",
      "id": "[concat(resourceId('Microsoft.OperationalInsights/workspaces/providers', parameters('workspace'), 'Microsoft.SecurityInsights'),'/alertRules/12134de5-361b-427c-a1a0-d43f40a593c4')]",
      "kind": "Scheduled",
      "name": "[concat(parameters('workspace'),'/Microsoft.SecurityInsights/12134de5-361b-427c-a1a0-d43f40a593c4')]",
      "properties": {
        "alertRuleTemplateName": "12134de5-361b-427c-a1a0-d43f40a593c4",
        "customDetails": null,
        "description": "This query aim to detect if someone requests service tickets (where count => maxcount)\nThe query requires trimming to set a baseline level for MaxCount  \nMitre Technique: Kerberoasting (T1558.003)\n@MattiasBorg82\n",
        "displayName": "Detect Potential Kerberoast Activities",
        "enabled": true,
        "entityMappings": [
          {
            "entityType": "Host",
            "fieldMappings": [
              {
                "columnName": "DeviceName",
                "identifier": "FullName"
              },
              {
                "columnName": "HostName",
                "identifier": "HostName"
              },
              {
                "columnName": "DnsDomain",
                "identifier": "DnsDomain"
              }
            ]
          },
          {
            "entityType": "Account",
            "fieldMappings": [
              {
                "columnName": "AccountUpn",
                "identifier": "FullName"
              },
              {
                "columnName": "AccountDomain",
                "identifier": "DnsDomain"
              },
              {
                "columnName": "AccountName",
                "identifier": "Name"
              }
            ]
          }
        ],
        "OriginalUri": "https://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Microsoft Defender XDR/Analytic Rules/Execution/PotentialKerberoastActivities.yaml",
        "query": "let MaxCount = 70; //Number of requests per 2 minute timeframe, depending on org size.\nIdentityLogonEvents\n| where ActionType == \"LogonSuccess\"\n| where Protocol == \"Kerberos\"\n| extend json = todynamic(parse_json(tostring(AdditionalFields)))\n| extend SPN = json.Spns,\n       AttackTechniques = json.AttackTechniques\n      | project-away json\n| where isnotempty(SPN)\n| where AttackTechniques has \"T1558.003\"\n| mv-expand SPN\n        | extend SPNType = tostring(extract(@\"^\\w+\",0,tostring(SPN)))\n| distinct tostring(SPN), DeviceName, AccountUpn, AccountSid, bin(TimeGenerated, 2m), ReportId, tostring(AttackTechniques)\n| summarize count(), SPNS=(make_list(SPN, 100000)),ReportId=tostring((make_list(ReportId, 100000))[0]) by AccountUpn,AccountSid,DeviceName, bin(TimeGenerated, 2m), tostring(AttackTechniques)\n| extend SPNS = (replace_regex(tostring(SPNS), @'[^\\w+-\\/]+', '')) \n| where count_ >= MaxCount\n| extend HostName = iff(DeviceName has '.', substring(DeviceName, 0, indexof(DeviceName, '.')), DeviceName)\n| extend DnsDomain = iff(DeviceName has '.', substring(DeviceName, indexof(DeviceName, '.') + 1), \"\")\n| extend AccountDomain = tostring(split(AccountUpn, '@')[1]), AccountName = tostring(split(AccountUpn, '@')[0])\n",
        "queryFrequency": "PT1H",
        "queryPeriod": "PT1H",
        "severity": "Medium",
        "status": "Available",
        "suppressionDuration": "PT1H",
        "suppressionEnabled": false,
        "tactics": [
          "CredentialAccess"
        ],
        "tag": [
          "Kerberoast"
        ],
        "techniques": [
          "T1558"
        ],
        "templateVersion": "1.0.0",
        "triggerOperator": "GreaterThan",
        "triggerThreshold": 0
      },
      "type": "Microsoft.OperationalInsights/workspaces/providers/alertRules"
    }
  ]
}