Back
Id23391c84-87d8-452f-a84c-47a62f01e115
RulenameTI Map URL Entity to UrlClickEvents
DescriptionThis query identifies any URL indicators of compromise (IOCs) from threat intelligence (TI) by searching for matches in UrlClickEvents.
SeverityMedium
TacticsCommandAndControl
TechniquesT1071
Required data connectorsMicrosoftDefenderThreatIntelligence
MicrosoftThreatProtection
ThreatIntelligence
ThreatIntelligenceTaxii
KindScheduled
Query frequency1h
Query period14d
Trigger threshold0
Trigger operatorgt
Source Urihttps://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Threat%20Intelligence/Analytic%20Rules/URLEntity_UrlClickEvents.yaml
Version1.0.2
Arm template23391c84-87d8-452f-a84c-47a62f01e115.json
Deploy To Azure
let dt_lookBack = 1h;
let ioc_lookBack = 14d;
let UrlClickEvents_ = materialize(UrlClickEvents
    | where TimeGenerated >= ago(dt_lookBack)
    | extend UrlClickEvents_TimeGenerated = TimeGenerated);
let ChainReportID = UrlClickEvents_
    | mv-expand todynamic(UrlChain)
    | extend UrlChain = tolower(UrlChain)
    | project ReportId, Url, UrlChain;
// Url is not always in UrlChain, so we need to check both
let ClickedUrls = 
  (union isfuzzy=false (ChainReportID), (ChainReportID | project Url = UrlChain))
  | distinct Url
  | summarize make_list(Url);
let TI = materialize(ThreatIntelligenceIndicator
  | where TimeGenerated >= ago(ioc_lookBack)
  | where isnotempty(Url) and tolower(Url) in (ClickedUrls)
  | summarize LatestIndicatorTime = arg_max(TimeGenerated, *) by IndicatorId
  | where Active == true and ExpirationDateTime > now()
  | project-rename TI_Url = Url, TI_Type = Type
  );
(union isfuzzy=false (TI | join kind=innerunique (ChainReportID) on $left.TI_Url == $right.UrlChain),
  (TI | join kind=innerunique (ChainReportID) on $left.TI_Url == $right.Url))
| project-away UrlChain
| join kind=innerunique (UrlClickEvents_) on ReportId
| where UrlClickEvents_TimeGenerated < ExpirationDateTime
| summarize UrlClickEvents_TimeGenerated = arg_max(UrlClickEvents_TimeGenerated, *) by IndicatorId
| project UrlClickEvents_TimeGenerated, AccountUpn, Description, ActivityGroupNames, IndicatorId, ThreatType, ExpirationDateTime, ConfidenceScore, Url, NetworkMessageId
| extend timestamp = UrlClickEvents_TimeGenerated
| extend timestamp = UrlClickEvents_TimeGenerated, Name = tostring(split(AccountUpn, '@', 0)[0]), UPNSuffix = tostring(split(AccountUpn, '@', 1)[0])
triggerOperator: gt
version: 1.0.2
triggerThreshold: 0
entityMappings:
- entityType: Account
  fieldMappings:
  - columnName: AccountUpn
    identifier: FullName
  - columnName: Name
    identifier: Name
  - columnName: UPNSuffix
    identifier: UPNSuffix
- entityType: URL
  fieldMappings:
  - columnName: Url
    identifier: Url
name: TI Map URL Entity to UrlClickEvents
kind: Scheduled
query: |
  let dt_lookBack = 1h;
  let ioc_lookBack = 14d;
  let UrlClickEvents_ = materialize(UrlClickEvents
      | where TimeGenerated >= ago(dt_lookBack)
      | extend UrlClickEvents_TimeGenerated = TimeGenerated);
  let ChainReportID = UrlClickEvents_
      | mv-expand todynamic(UrlChain)
      | extend UrlChain = tolower(UrlChain)
      | project ReportId, Url, UrlChain;
  // Url is not always in UrlChain, so we need to check both
  let ClickedUrls = 
    (union isfuzzy=false (ChainReportID), (ChainReportID | project Url = UrlChain))
    | distinct Url
    | summarize make_list(Url);
  let TI = materialize(ThreatIntelligenceIndicator
    | where TimeGenerated >= ago(ioc_lookBack)
    | where isnotempty(Url) and tolower(Url) in (ClickedUrls)
    | summarize LatestIndicatorTime = arg_max(TimeGenerated, *) by IndicatorId
    | where Active == true and ExpirationDateTime > now()
    | project-rename TI_Url = Url, TI_Type = Type
    );
  (union isfuzzy=false (TI | join kind=innerunique (ChainReportID) on $left.TI_Url == $right.UrlChain),
    (TI | join kind=innerunique (ChainReportID) on $left.TI_Url == $right.Url))
  | project-away UrlChain
  | join kind=innerunique (UrlClickEvents_) on ReportId
  | where UrlClickEvents_TimeGenerated < ExpirationDateTime
  | summarize UrlClickEvents_TimeGenerated = arg_max(UrlClickEvents_TimeGenerated, *) by IndicatorId
  | project UrlClickEvents_TimeGenerated, AccountUpn, Description, ActivityGroupNames, IndicatorId, ThreatType, ExpirationDateTime, ConfidenceScore, Url, NetworkMessageId
  | extend timestamp = UrlClickEvents_TimeGenerated
  | extend timestamp = UrlClickEvents_TimeGenerated, Name = tostring(split(AccountUpn, '@', 0)[0]), UPNSuffix = tostring(split(AccountUpn, '@', 1)[0])
queryPeriod: 14d
relevantTechniques:
- T1071
id: 23391c84-87d8-452f-a84c-47a62f01e115
tactics:
- CommandAndControl
requiredDataConnectors:
- dataTypes:
  - UrlClickEvents
  connectorId: MicrosoftThreatProtection
- dataTypes:
  - ThreatIntelligenceIndicator
  connectorId: ThreatIntelligence
- dataTypes:
  - ThreatIntelligenceIndicator
  connectorId: ThreatIntelligenceTaxii
- dataTypes:
  - ThreatIntelligenceIndicator
  connectorId: MicrosoftDefenderThreatIntelligence
severity: Medium
description: |
  'This query identifies any URL indicators of compromise (IOCs) from threat intelligence (TI) by searching for matches in UrlClickEvents.'
OriginalUri: https://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Threat%20Intelligence/Analytic%20Rules/URLEntity_UrlClickEvents.yaml
queryFrequency: 1h
{
  "$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/23391c84-87d8-452f-a84c-47a62f01e115')]",
      "kind": "Scheduled",
      "name": "[concat(parameters('workspace'),'/Microsoft.SecurityInsights/23391c84-87d8-452f-a84c-47a62f01e115')]",
      "properties": {
        "alertRuleTemplateName": "23391c84-87d8-452f-a84c-47a62f01e115",
        "customDetails": null,
        "description": "'This query identifies any URL indicators of compromise (IOCs) from threat intelligence (TI) by searching for matches in UrlClickEvents.'\n",
        "displayName": "TI Map URL Entity to UrlClickEvents",
        "enabled": true,
        "entityMappings": [
          {
            "entityType": "Account",
            "fieldMappings": [
              {
                "columnName": "AccountUpn",
                "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%20Intelligence/Analytic%20Rules/URLEntity_UrlClickEvents.yaml",
        "query": "let dt_lookBack = 1h;\nlet ioc_lookBack = 14d;\nlet UrlClickEvents_ = materialize(UrlClickEvents\n    | where TimeGenerated >= ago(dt_lookBack)\n    | extend UrlClickEvents_TimeGenerated = TimeGenerated);\nlet ChainReportID = UrlClickEvents_\n    | mv-expand todynamic(UrlChain)\n    | extend UrlChain = tolower(UrlChain)\n    | project ReportId, Url, UrlChain;\n// Url is not always in UrlChain, so we need to check both\nlet ClickedUrls = \n  (union isfuzzy=false (ChainReportID), (ChainReportID | project Url = UrlChain))\n  | distinct Url\n  | summarize make_list(Url);\nlet TI = materialize(ThreatIntelligenceIndicator\n  | where TimeGenerated >= ago(ioc_lookBack)\n  | where isnotempty(Url) and tolower(Url) in (ClickedUrls)\n  | summarize LatestIndicatorTime = arg_max(TimeGenerated, *) by IndicatorId\n  | where Active == true and ExpirationDateTime > now()\n  | project-rename TI_Url = Url, TI_Type = Type\n  );\n(union isfuzzy=false (TI | join kind=innerunique (ChainReportID) on $left.TI_Url == $right.UrlChain),\n  (TI | join kind=innerunique (ChainReportID) on $left.TI_Url == $right.Url))\n| project-away UrlChain\n| join kind=innerunique (UrlClickEvents_) on ReportId\n| where UrlClickEvents_TimeGenerated < ExpirationDateTime\n| summarize UrlClickEvents_TimeGenerated = arg_max(UrlClickEvents_TimeGenerated, *) by IndicatorId\n| project UrlClickEvents_TimeGenerated, AccountUpn, Description, ActivityGroupNames, IndicatorId, ThreatType, ExpirationDateTime, ConfidenceScore, Url, NetworkMessageId\n| extend timestamp = UrlClickEvents_TimeGenerated\n| extend timestamp = UrlClickEvents_TimeGenerated, Name = tostring(split(AccountUpn, '@', 0)[0]), UPNSuffix = tostring(split(AccountUpn, '@', 1)[0])\n",
        "queryFrequency": "PT1H",
        "queryPeriod": "P14D",
        "severity": "Medium",
        "subTechniques": [],
        "suppressionDuration": "PT1H",
        "suppressionEnabled": false,
        "tactics": [
          "CommandAndControl"
        ],
        "techniques": [
          "T1071"
        ],
        "templateVersion": "1.0.2",
        "triggerOperator": "GreaterThan",
        "triggerThreshold": 0
      },
      "type": "Microsoft.OperationalInsights/workspaces/providers/alertRules"
    }
  ]
}