TI Map URL Entity to Syslog Data

id: 4de24a28-dcd0-4a0d-bf14-96d8483dc05a
name: TI Map URL Entity to Syslog Data
description: |
  'This query identifies any URL indicators of compromise (IOCs) from threat intelligence (TI) by searching for matches in Syslog data.'
severity: Medium
requiredDataConnectors:
  - connectorId: Syslog
    dataTypes:
      - Syslog
  - connectorId: ThreatIntelligence
    dataTypes:
      - ThreatIntelIndicators
  - connectorId: ThreatIntelligenceTaxii
    dataTypes:
      - ThreatIntelIndicators
  - connectorId: MicrosoftDefenderThreatIntelligence
    dataTypes:
      - ThreatIntelIndicators
queryFrequency: 1h
queryPeriod: 14d
triggerOperator: gt
triggerThreshold: 0
tactics:
  - CommandAndControl
relevantTechniques:
  - T1071
query: |
  let dt_lookBack = 1h;
  let ioc_lookBack = 14d;
  ThreatIntelIndicators
  // Picking up only IOC's that contain the entities we want
  //extract key part of kv pair
  | extend IndicatorType = replace(@"\[|\]|\""", "", tostring(split(ObservableKey, ":", 0)))
  | where IndicatorType == "url"
  | extend Url = ObservableValue
  | extend TrafficLightProtocolLevel = tostring(parse_json(AdditionalFields).TLPLevel)
  | where TimeGenerated >= ago(ioc_lookBack)
  | summarize LatestIndicatorTime = arg_max(TimeGenerated, *) by Id, ObservableValue
  | where IsActive and (ValidUntil > now() or isempty(ValidUntil))
     | 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 (
    Syslog
    | where TimeGenerated >= ago(dt_lookBack)
    // Extract URL from the Syslog message but only take messages that include URLs
    | extend Url = extract("(http[s]?://(?:[a-zA-Z]|[0-9]|[$-_@.&+]|[!*\\(\\),]|(?:%[0-9a-fA-F][0-9a-fA-F]))+)", 1,SyslogMessage)
    | extend Syslog_TimeGenerated = TimeGenerated
  ) on Url
  | where Syslog_TimeGenerated < ValidUntil
  | summarize Syslog_TimeGenerated  = arg_max(Syslog_TimeGenerated , *) by Id, Url
  | extend Description = tostring(parse_json(Data).description)
  | extend ActivityGroupNames = extract(@"ActivityGroup:(\S+)", 1, tostring(parse_json(Data).labels))
  | project timestamp = Syslog_TimeGenerated, Description, ActivityGroupNames, Id, Type, ValidUntil, Confidence, SyslogMessage, Computer, ProcessName, Url, HostIP
entityMappings:
  - entityType: Host
    fieldMappings:
      - identifier: HostName
        columnName: Computer
  - entityType: IP
    fieldMappings:
      - identifier: Address
        columnName: HostIP
  - entityType: URL
    fieldMappings:
      - identifier: Url
        columnName: Url
version: 1.2.8
kind: Scheduled