ThreatConnect TI Map URL Entity to OfficeActivity Data

Back


Id	12c3b31b-66a6-53ff-b6ab-6ae45e56dc92
Rulename	ThreatConnect TI Map URL Entity to OfficeActivity Data
Description	This query identifies any URL indicators of compromise (IOCs) from threat intelligence (TI) by searching for matches in OfficeActivity data.
Severity	Medium
Tactics	CommandAndControl
Techniques	T1071
Required data connectors	MicrosoftDefenderThreatIntelligence Office365 ThreatIntelligence
Kind	Scheduled
Query frequency	1h
Query period	14d
Trigger threshold	0
Trigger operator	gt
Source Uri	https://github.com/Azure/Azure-Sentinel/blob/master/Solutions/ThreatConnect/Analytic Rules/ThreatConnect_URLEntity_OfficeActivity.yaml
Version	1.0.1
Arm template	12c3b31b-66a6-53ff-b6ab-6ae45e56dc92.json

KQL

let dt_lookBack = 1h;
let ioc_lookBack = 14d;
  ThreatIntelligenceIndicator
  | where TimeGenerated >= ago(ioc_lookBack)
  | summarize LatestIndicatorTime = arg_max(TimeGenerated, *) by IndicatorId
  | where ExpirationDateTime > now() and Active == true
// Filter out non ThreatConnect TI Sources
| where SourceSystem startswith "ThreatConnect-"
// 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 (
  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 < ExpirationDateTime
| summarize OfficeActivity_TimeGenerated = arg_max(OfficeActivity_TimeGenerated, *) by IndicatorId, Url
| project OfficeActivity_TimeGenerated, Description, ActivityGroupNames, IndicatorId, ThreatType, ExpirationDateTime, ConfidenceScore, Operation,
UserType, OfficeWorkload, Parameters, Url, User
| extend timestamp = OfficeActivity_TimeGenerated, Name = tostring(split(User, '@', 0)[0]), UPNSuffix = tostring(split(User, '@', 1)[0])

YAML

tactics:
- CommandAndControl
query: |
  let dt_lookBack = 1h;
  let ioc_lookBack = 14d;
    ThreatIntelligenceIndicator
    | where TimeGenerated >= ago(ioc_lookBack)
    | summarize LatestIndicatorTime = arg_max(TimeGenerated, *) by IndicatorId
    | where ExpirationDateTime > now() and Active == true
  // Filter out non ThreatConnect TI Sources
  | where SourceSystem startswith "ThreatConnect-"
  // 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 (
    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 < ExpirationDateTime
  | summarize OfficeActivity_TimeGenerated = arg_max(OfficeActivity_TimeGenerated, *) by IndicatorId, Url
  | project OfficeActivity_TimeGenerated, Description, ActivityGroupNames, IndicatorId, ThreatType, ExpirationDateTime, ConfidenceScore, Operation,
  UserType, OfficeWorkload, Parameters, Url, User
  | extend timestamp = OfficeActivity_TimeGenerated, Name = tostring(split(User, '@', 0)[0]), UPNSuffix = tostring(split(User, '@', 1)[0])  
requiredDataConnectors:
- dataTypes:
  - OfficeActivity
  connectorId: Office365
- dataTypes:
  - ThreatIntelligenceIndicator
  connectorId: ThreatIntelligence
- dataTypes:
  - ThreatIntelligenceIndicator
  connectorId: MicrosoftDefenderThreatIntelligence
name: ThreatConnect TI Map URL Entity to OfficeActivity Data
queryPeriod: 14d
OriginalUri: https://github.com/Azure/Azure-Sentinel/blob/master/Solutions/ThreatConnect/Analytic Rules/ThreatConnect_URLEntity_OfficeActivity.yaml
triggerThreshold: 0
description: |
    'This query identifies any URL indicators of compromise (IOCs) from threat intelligence (TI) by searching for matches in OfficeActivity data.'
version: 1.0.1
kind: Scheduled
queryFrequency: 1h
severity: Medium
entityMappings:
- entityType: Account
  fieldMappings:
  - identifier: Name
    columnName: Name
  - identifier: UPNSuffix
    columnName: UPNSuffix
- entityType: URL
  fieldMappings:
  - identifier: Url
    columnName: Url
triggerOperator: gt
id: 12c3b31b-66a6-53ff-b6ab-6ae45e56dc92
relevantTechniques:
- T1071

ARM