TI Map URL Entity to PaloAlto Data

let dt_lookBack = 1h;
let ioc_lookBack = 14d;
ThreatIntelligenceIndicator
| where TimeGenerated >= ago(ioc_lookBack) and ExpirationDateTime > now()
| summarize LatestIndicatorTime = arg_max(TimeGenerated, *) by IndicatorId
| where Active == true
// Picking up only IOC's that contain the entities we want
| where isnotempty(Url)
// 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 (
  CommonSecurityLog
  | extend IngestionTime = ingestion_time()
  | where IngestionTime > ago(dt_lookBack)
  // Select on Palo Alto logs
  | where DeviceVendor =~ "Palo Alto Networks"
  | where DeviceEventClassID =~ 'url'
  //Uncomment the line below to only alert on allowed connections
  //| where DeviceAction !~ "block-url"
  //Select logs where URL data is populated
  | extend PA_Url = column_ifexists("RequestURL", "None")
  | extend PA_Url = iif(isempty(PA_Url), extract("([^\"]+)", 1, tolower(AdditionalExtensions)), trim('"', PA_Url))
  | extend PA_Url = iif(PA_Url !startswith "http://" and ApplicationProtocol !~ "ssl", strcat('http://', PA_Url), iif(PA_Url !startswith "https://" and ApplicationProtocol =~ "ssl", strcat('https://', PA_Url), PA_Url))
  | where isnotempty(PA_Url)
  | extend CommonSecurityLog_TimeGenerated = TimeGenerated
) on $left.Url == $right.PA_Url
| where CommonSecurityLog_TimeGenerated < ExpirationDateTime
| summarize CommonSecurityLog_TimeGenerated = arg_max(CommonSecurityLog_TimeGenerated, *) by IndicatorId, PA_Url
| project timestamp = CommonSecurityLog_TimeGenerated, Description, ActivityGroupNames, IndicatorId, ThreatType, ExpirationDateTime, ConfidenceScore, DeviceAction, SourceIP, PA_Url, DeviceName

triggerOperator: gt
version: 1.2.3
query: |
  let dt_lookBack = 1h;
  let ioc_lookBack = 14d;
  ThreatIntelligenceIndicator
  | where TimeGenerated >= ago(ioc_lookBack) and ExpirationDateTime > now()
  | summarize LatestIndicatorTime = arg_max(TimeGenerated, *) by IndicatorId
  | where Active == true
  // Picking up only IOC's that contain the entities we want
  | where isnotempty(Url)
  // 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 (
    CommonSecurityLog
    | extend IngestionTime = ingestion_time()
    | where IngestionTime > ago(dt_lookBack)
    // Select on Palo Alto logs
    | where DeviceVendor =~ "Palo Alto Networks"
    | where DeviceEventClassID =~ 'url'
    //Uncomment the line below to only alert on allowed connections
    //| where DeviceAction !~ "block-url"
    //Select logs where URL data is populated
    | extend PA_Url = column_ifexists("RequestURL", "None")
    | extend PA_Url = iif(isempty(PA_Url), extract("([^\"]+)", 1, tolower(AdditionalExtensions)), trim('"', PA_Url))
    | extend PA_Url = iif(PA_Url !startswith "http://" and ApplicationProtocol !~ "ssl", strcat('http://', PA_Url), iif(PA_Url !startswith "https://" and ApplicationProtocol =~ "ssl", strcat('https://', PA_Url), PA_Url))
    | where isnotempty(PA_Url)
    | extend CommonSecurityLog_TimeGenerated = TimeGenerated
  ) on $left.Url == $right.PA_Url
  | where CommonSecurityLog_TimeGenerated < ExpirationDateTime
  | summarize CommonSecurityLog_TimeGenerated = arg_max(CommonSecurityLog_TimeGenerated, *) by IndicatorId, PA_Url
  | project timestamp = CommonSecurityLog_TimeGenerated, Description, ActivityGroupNames, IndicatorId, ThreatType, ExpirationDateTime, ConfidenceScore, DeviceAction, SourceIP, PA_Url, DeviceName  
entityMappings:
- entityType: Host
  fieldMappings:
  - columnName: DeviceName
    identifier: HostName
- entityType: IP
  fieldMappings:
  - columnName: SourceIP
    identifier: Address
- entityType: URL
  fieldMappings:
  - columnName: PA_Url
    identifier: Url
OriginalUri: https://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Threat Intelligence/Analytic Rules/URLEntity_PaloAlto.yaml
queryFrequency: 1h
requiredDataConnectors:
- connectorId: PaloAltoNetworks
  dataTypes:
  - CommonSecurityLog
- connectorId: ThreatIntelligence
  dataTypes:
  - ThreatIntelligenceIndicator
- connectorId: ThreatIntelligenceTaxii
  dataTypes:
  - ThreatIntelligenceIndicator
- connectorId: MicrosoftDefenderThreatIntelligence
  dataTypes:
  - ThreatIntelligenceIndicator
name: TI Map URL Entity to PaloAlto Data
queryPeriod: 14d
severity: Medium
kind: Scheduled
tactics:
- Impact
id: 106813db-679e-4382-a51b-1bfc463befc3
description: |
    'This query identifies any URL indicators of compromise (IOCs) from threat intelligence (TI) by searching for matches in PaloAlto Data.'
triggerThreshold: 0

{
  "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
  "contentVersion": "1.0.0.0",
  "parameters": {
    "workspace": {
      "type": "String"
    }
  },
  "resources": [
    {
      "id": "[concat(resourceId('Microsoft.OperationalInsights/workspaces/providers', parameters('workspace'), 'Microsoft.SecurityInsights'),'/alertRules/106813db-679e-4382-a51b-1bfc463befc3')]",
      "name": "[concat(parameters('workspace'),'/Microsoft.SecurityInsights/106813db-679e-4382-a51b-1bfc463befc3')]",
      "type": "Microsoft.OperationalInsights/workspaces/providers/alertRules",
      "kind": "Scheduled",
      "apiVersion": "2022-11-01-preview",
      "properties": {
        "displayName": "TI Map URL Entity to PaloAlto Data",
        "description": "'This query identifies any URL indicators of compromise (IOCs) from threat intelligence (TI) by searching for matches in PaloAlto Data.'\n",
        "severity": "Medium",
        "enabled": true,
        "query": "let dt_lookBack = 1h;\nlet ioc_lookBack = 14d;\nThreatIntelligenceIndicator\n| where TimeGenerated >= ago(ioc_lookBack) and ExpirationDateTime > now()\n| summarize LatestIndicatorTime = arg_max(TimeGenerated, *) by IndicatorId\n| where Active == true\n// Picking up only IOC's that contain the entities we want\n| where isnotempty(Url)\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  CommonSecurityLog\n  | extend IngestionTime = ingestion_time()\n  | where IngestionTime > ago(dt_lookBack)\n  // Select on Palo Alto logs\n  | where DeviceVendor =~ \"Palo Alto Networks\"\n  | where DeviceEventClassID =~ 'url'\n  //Uncomment the line below to only alert on allowed connections\n  //| where DeviceAction !~ \"block-url\"\n  //Select logs where URL data is populated\n  | extend PA_Url = column_ifexists(\"RequestURL\", \"None\")\n  | extend PA_Url = iif(isempty(PA_Url), extract(\"([^\\\"]+)\", 1, tolower(AdditionalExtensions)), trim('\"', PA_Url))\n  | extend PA_Url = iif(PA_Url !startswith \"http://\" and ApplicationProtocol !~ \"ssl\", strcat('http://', PA_Url), iif(PA_Url !startswith \"https://\" and ApplicationProtocol =~ \"ssl\", strcat('https://', PA_Url), PA_Url))\n  | where isnotempty(PA_Url)\n  | extend CommonSecurityLog_TimeGenerated = TimeGenerated\n) on $left.Url == $right.PA_Url\n| where CommonSecurityLog_TimeGenerated < ExpirationDateTime\n| summarize CommonSecurityLog_TimeGenerated = arg_max(CommonSecurityLog_TimeGenerated, *) by IndicatorId, PA_Url\n| project timestamp = CommonSecurityLog_TimeGenerated, Description, ActivityGroupNames, IndicatorId, ThreatType, ExpirationDateTime, ConfidenceScore, DeviceAction, SourceIP, PA_Url, DeviceName\n",
        "queryFrequency": "PT1H",
        "queryPeriod": "P14D",
        "triggerOperator": "GreaterThan",
        "triggerThreshold": 0,
        "suppressionDuration": "PT1H",
        "suppressionEnabled": false,
        "tactics": [
          "Impact"
        ],
        "alertRuleTemplateName": "106813db-679e-4382-a51b-1bfc463befc3",
        "customDetails": null,
        "entityMappings": [
          {
            "fieldMappings": [
              {
                "columnName": "DeviceName",
                "identifier": "HostName"
              }
            ],
            "entityType": "Host"
          },
          {
            "fieldMappings": [
              {
                "columnName": "SourceIP",
                "identifier": "Address"
              }
            ],
            "entityType": "IP"
          },
          {
            "fieldMappings": [
              {
                "columnName": "PA_Url",
                "identifier": "Url"
              }
            ],
            "entityType": "URL"
          }
        ],
        "OriginalUri": "https://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Threat Intelligence/Analytic Rules/URLEntity_PaloAlto.yaml",
        "templateVersion": "1.2.3"
      }
    }
  ]
}