Microsoft Sentinel Analytic Rules
cloudbrothers.infoAzure Sentinel RepoToggle Dark/Light/Auto modeToggle Dark/Light/Auto modeToggle Dark/Light/Auto modeBack to homepage

TI Map URL Entity to UrlClickEvents

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
TacticsImpact
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 Intelligence/Analytic Rules/URLEntity_UrlClickEvents.yaml
Version1.0.0
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])
kind: Scheduled
triggerThreshold: 0
OriginalUri: https://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Threat Intelligence/Analytic Rules/URLEntity_UrlClickEvents.yaml
requiredDataConnectors:
- dataTypes:
  - UrlClickEvents
  connectorId: MicrosoftThreatProtection
- dataTypes:
  - ThreatIntelligenceIndicator
  connectorId: ThreatIntelligence
- dataTypes:
  - ThreatIntelligenceIndicator
  connectorId: ThreatIntelligenceTaxii
- dataTypes:
  - ThreatIntelligenceIndicator
  connectorId: MicrosoftDefenderThreatIntelligence
queryPeriod: 14d
tactics:
- Impact
severity: Medium
triggerOperator: gt
description: |
    'This query identifies any URL indicators of compromise (IOCs) from threat intelligence (TI) by searching for matches in UrlClickEvents.'
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])  
name: TI Map URL Entity to UrlClickEvents
version: 1.0.0
id: 23391c84-87d8-452f-a84c-47a62f01e115
queryFrequency: 1h
entityMappings:
- entityType: Account
  fieldMappings:
  - columnName: Name
    identifier: Name
  - columnName: UPNSuffix
    identifier: UPNSuffix
- entityType: URL
  fieldMappings:
  - columnName: Url
    identifier: Url
{
  "$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/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": "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/Analytic Rules/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",
        "suppressionDuration": "PT1H",
        "suppressionEnabled": false,
        "tactics": [
          "Impact"
        ],
        "templateVersion": "1.0.0",
        "triggerOperator": "GreaterThan",
        "triggerThreshold": 0
      },
      "type": "Microsoft.OperationalInsights/workspaces/providers/alertRules"
    }
  ]
}