TI Map URL Entity to OfficeActivity Data [Deprecated]

let dt_lookBack = 1h;
//   let ioc_lookBack = 14d;
//   ThreatIntelIndicators
//    //extract key part of kv pair
//   | extend IndicatorType = replace(@"\[|\]|\""", "", tostring(split(ObservableKey, ":", 0)))
//   | where IndicatorType == "url"
//   | extend Url = ObservableValue
//   | where isnotempty(Url)
//   | extend TrafficLightProtocolLevel = tostring(parse_json(AdditionalFields).TLPLevel)
//   // Picking up only IOC's that contain the entities we want
//   | where isnotempty(Url)
//   | where TimeGenerated >= ago(ioc_lookBack)
//   | summarize LatestIndicatorTime = arg_max(TimeGenerated, *) by Id
//   | where IsActive == true and ValidUntil > now()
//    | project-reorder *, Tags, TrafficLightProtocolLevel, Url, Type
//    // using innerunique to keep perf fast and result set low, we only need one match to indicate potential malicious activity that needs to be investigated
//    | join kind=innerunique (
//    OfficeActivity
//    | where TimeGenerated >= ago(dt_lookBack)
//    //Extract the Url from a number of potential fields
//    | extend Url = iif(OfficeWorkload == "AzureActiveDirectory",extract("(http[s]?://(?:[a-zA-Z]|[0-9]|[$-_@.&+]|[!*\\(\\),]|(?:%[0-9a-fA-F][0-9a-fA-F]))+)", 1,ModifiedProperties),tostring(parse_json(ModifiedProperties)[12].NewValue))
//    | where isnotempty(Url)
//    // Ensure we get a clean URL
//    | extend Url = tostring(split(Url, ';')[0])
//    | extend OfficeActivity_TimeGenerated = TimeGenerated
//    // Project a single user identity that we can use for entity mapping
//    | extend User = iif(isnotempty(UserId), UserId, iif(isnotempty(Actor), tostring(parse_json(Actor)[0].ID), tostring(parse_json(Parameters)[0].Value)))
//    ) on Url
//    | where OfficeActivity_TimeGenerated < ValidUntil
//    | summarize OfficeActivity_TimeGenerated = arg_max(OfficeActivity_TimeGenerated, *) by Id, Url
//    | extend Description = tostring(parse_json(Data).description)
//    | extend ActivityGroupNames = extract(@"ActivityGroup:(\S+)", 1, tostring(parse_json(Data).labels))
//    | project OfficeActivity_TimeGenerated, Description, ActivityGroupNames, Id, Type, ValidUntil, Confidence, Operation,
//    UserType, OfficeWorkload, Parameters, Url, User
//    | extend timestamp = OfficeActivity_TimeGenerated, Name = tostring(split(User, '@', 0)[0]), UPNSuffix = tostring(split(User, '@', 1)[0])
//datatable() []

relevantTechniques:
- T1071
triggerThreshold: 0
version: 1.2.9
triggerOperator: gt
requiredDataConnectors:
- dataTypes:
  - OfficeActivity
  connectorId: Office365
- dataTypes:
  - ThreatIntelligenceIndicator
  connectorId: ThreatIntelligence
- dataTypes:
  - ThreatIntelligenceIndicator
  connectorId: MicrosoftDefenderThreatIntelligence
- dataTypes:
  - ThreatIntelligenceIndicator
  connectorId: ThreatIntelligenceTaxii
name: TI Map URL Entity to OfficeActivity Data [Deprecated]
severity: Medium
queryPeriod: 14d
kind: Scheduled
description: |
    'This query is Deprecated as its filter conditions will never yield results. This query identifies any URL indicators of compromise (IOCs) from threat intelligence (TI) by searching for matches in OfficeActivity data.'
queryFrequency: 1h
tactics:
- CommandAndControl
query: |
  let dt_lookBack = 1h;
  //   let ioc_lookBack = 14d;
  //   ThreatIntelIndicators
  //    //extract key part of kv pair
  //   | extend IndicatorType = replace(@"\[|\]|\""", "", tostring(split(ObservableKey, ":", 0)))
  //   | where IndicatorType == "url"
  //   | extend Url = ObservableValue
  //   | where isnotempty(Url)
  //   | extend TrafficLightProtocolLevel = tostring(parse_json(AdditionalFields).TLPLevel)
  //   // Picking up only IOC's that contain the entities we want
  //   | where isnotempty(Url)
  //   | where TimeGenerated >= ago(ioc_lookBack)
  //   | summarize LatestIndicatorTime = arg_max(TimeGenerated, *) by Id
  //   | where IsActive == true and ValidUntil > now()
  //    | project-reorder *, Tags, TrafficLightProtocolLevel, Url, Type
  //    // using innerunique to keep perf fast and result set low, we only need one match to indicate potential malicious activity that needs to be investigated
  //    | join kind=innerunique (
  //    OfficeActivity
  //    | where TimeGenerated >= ago(dt_lookBack)
  //    //Extract the Url from a number of potential fields
  //    | extend Url = iif(OfficeWorkload == "AzureActiveDirectory",extract("(http[s]?://(?:[a-zA-Z]|[0-9]|[$-_@.&+]|[!*\\(\\),]|(?:%[0-9a-fA-F][0-9a-fA-F]))+)", 1,ModifiedProperties),tostring(parse_json(ModifiedProperties)[12].NewValue))
  //    | where isnotempty(Url)
  //    // Ensure we get a clean URL
  //    | extend Url = tostring(split(Url, ';')[0])
  //    | extend OfficeActivity_TimeGenerated = TimeGenerated
  //    // Project a single user identity that we can use for entity mapping
  //    | extend User = iif(isnotempty(UserId), UserId, iif(isnotempty(Actor), tostring(parse_json(Actor)[0].ID), tostring(parse_json(Parameters)[0].Value)))
  //    ) on Url
  //    | where OfficeActivity_TimeGenerated < ValidUntil
  //    | summarize OfficeActivity_TimeGenerated = arg_max(OfficeActivity_TimeGenerated, *) by Id, Url
  //    | extend Description = tostring(parse_json(Data).description)
  //    | extend ActivityGroupNames = extract(@"ActivityGroup:(\S+)", 1, tostring(parse_json(Data).labels))
  //    | project OfficeActivity_TimeGenerated, Description, ActivityGroupNames, Id, Type, ValidUntil, Confidence, Operation,
  //    UserType, OfficeWorkload, Parameters, Url, User
  //    | extend timestamp = OfficeActivity_TimeGenerated, Name = tostring(split(User, '@', 0)[0]), UPNSuffix = tostring(split(User, '@', 1)[0])
  //datatable() []  
OriginalUri: https://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Threat Intelligence (NEW)/Analytic Rules/URLEntity_OfficeActivity.yaml
id: e7abee0a-4a8d-459c-97e4-58c30bee80b1
entityMappings:
- fieldMappings:
  - identifier: FullName
    columnName: User
  - identifier: Name
    columnName: Name
  - identifier: UPNSuffix
    columnName: UPNSuffix
  entityType: Account
- fieldMappings:
  - identifier: Url
    columnName: Url
  entityType: URL

{
  "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
  "contentVersion": "1.0.0.0",
  "parameters": {
    "workspace": {
      "type": "String"
    }
  },
  "resources": [
    {
      "apiVersion": "2024-01-01-preview",
      "id": "[concat(resourceId('Microsoft.OperationalInsights/workspaces/providers', parameters('workspace'), 'Microsoft.SecurityInsights'),'/alertRules/e7abee0a-4a8d-459c-97e4-58c30bee80b1')]",
      "kind": "Scheduled",
      "name": "[concat(parameters('workspace'),'/Microsoft.SecurityInsights/e7abee0a-4a8d-459c-97e4-58c30bee80b1')]",
      "properties": {
        "alertRuleTemplateName": "e7abee0a-4a8d-459c-97e4-58c30bee80b1",
        "customDetails": null,
        "description": "'This query is Deprecated as its filter conditions will never yield results. This query identifies any URL indicators of compromise (IOCs) from threat intelligence (TI) by searching for matches in OfficeActivity data.'\n",
        "displayName": "TI Map URL Entity to OfficeActivity Data [Deprecated]",
        "enabled": true,
        "entityMappings": [
          {
            "entityType": "Account",
            "fieldMappings": [
              {
                "columnName": "User",
                "identifier": "FullName"
              },
              {
                "columnName": "Name",
                "identifier": "Name"
              },
              {
                "columnName": "UPNSuffix",
                "identifier": "UPNSuffix"
              }
            ]
          },
          {
            "entityType": "URL",
            "fieldMappings": [
              {
                "columnName": "Url",
                "identifier": "Url"
              }
            ]
          }
        ],
        "OriginalUri": "https://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Threat Intelligence (NEW)/Analytic Rules/URLEntity_OfficeActivity.yaml",
        "query": "let dt_lookBack = 1h;\n//   let ioc_lookBack = 14d;\n//   ThreatIntelIndicators\n//    //extract key part of kv pair\n//   | extend IndicatorType = replace(@\"\\[|\\]|\\\"\"\", \"\", tostring(split(ObservableKey, \":\", 0)))\n//   | where IndicatorType == \"url\"\n//   | extend Url = ObservableValue\n//   | where isnotempty(Url)\n//   | extend TrafficLightProtocolLevel = tostring(parse_json(AdditionalFields).TLPLevel)\n//   // Picking up only IOC's that contain the entities we want\n//   | where isnotempty(Url)\n//   | where TimeGenerated >= ago(ioc_lookBack)\n//   | summarize LatestIndicatorTime = arg_max(TimeGenerated, *) by Id\n//   | where IsActive == true and ValidUntil > now()\n//    | project-reorder *, Tags, TrafficLightProtocolLevel, Url, Type\n//    // using innerunique to keep perf fast and result set low, we only need one match to indicate potential malicious activity that needs to be investigated\n//    | join kind=innerunique (\n//    OfficeActivity\n//    | where TimeGenerated >= ago(dt_lookBack)\n//    //Extract the Url from a number of potential fields\n//    | extend Url = iif(OfficeWorkload == \"AzureActiveDirectory\",extract(\"(http[s]?://(?:[a-zA-Z]|[0-9]|[$-_@.&+]|[!*\\\\(\\\\),]|(?:%[0-9a-fA-F][0-9a-fA-F]))+)\", 1,ModifiedProperties),tostring(parse_json(ModifiedProperties)[12].NewValue))\n//    | where isnotempty(Url)\n//    // Ensure we get a clean URL\n//    | extend Url = tostring(split(Url, ';')[0])\n//    | extend OfficeActivity_TimeGenerated = TimeGenerated\n//    // Project a single user identity that we can use for entity mapping\n//    | extend User = iif(isnotempty(UserId), UserId, iif(isnotempty(Actor), tostring(parse_json(Actor)[0].ID), tostring(parse_json(Parameters)[0].Value)))\n//    ) on Url\n//    | where OfficeActivity_TimeGenerated < ValidUntil\n//    | summarize OfficeActivity_TimeGenerated = arg_max(OfficeActivity_TimeGenerated, *) by Id, Url\n//    | extend Description = tostring(parse_json(Data).description)\n//    | extend ActivityGroupNames = extract(@\"ActivityGroup:(\\S+)\", 1, tostring(parse_json(Data).labels))\n//    | project OfficeActivity_TimeGenerated, Description, ActivityGroupNames, Id, Type, ValidUntil, Confidence, Operation,\n//    UserType, OfficeWorkload, Parameters, Url, User\n//    | extend timestamp = OfficeActivity_TimeGenerated, Name = tostring(split(User, '@', 0)[0]), UPNSuffix = tostring(split(User, '@', 1)[0])\n//datatable() []\n",
        "queryFrequency": "PT1H",
        "queryPeriod": "P14D",
        "severity": "Medium",
        "subTechniques": [],
        "suppressionDuration": "PT1H",
        "suppressionEnabled": false,
        "tactics": [
          "CommandAndControl"
        ],
        "techniques": [
          "T1071"
        ],
        "templateVersion": "1.2.9",
        "triggerOperator": "GreaterThan",
        "triggerThreshold": 0
      },
      "type": "Microsoft.OperationalInsights/workspaces/providers/alertRules"
    }
  ]
}