TI map Email entity to OfficeActivity

Back


Id	4a3f5ed7-8da5-4ce2-af6f-c9ada45060f2
Rulename	TI map Email entity to OfficeActivity
Description	Identifies a match in OfficeActivity table from any Email IOC from TI
Severity	Medium
Tactics	InitialAccess
Techniques	T1566
Required data connectors	MicrosoftDefenderThreatIntelligence Office365 ThreatIntelligence ThreatIntelligenceTaxii
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/Threat Intelligence/Analytic Rules/EmailEntity_OfficeActivity.yaml
Version	1.2.7
Arm template	4a3f5ed7-8da5-4ce2-af6f-c9ada45060f2.json

KQL

let dt_lookBack = 1h;
let ioc_lookBack = 14d;
let emailregex = @'^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+$';
let OfficeEvents = materialize(
  OfficeActivity
  | where isnotempty(UserId)
  | where TimeGenerated >= ago(dt_lookBack)
  | where UserId matches regex emailregex
  | project-rename  OfficeActivity_TimeGenerated = TimeGenerated);
let OfficeActivityUPNs = OfficeEvents | distinct UserId = tolower(UserId) | summarize make_list(UserId);
ThreatIntelligenceIndicator
| where isnotempty(EmailSenderAddress)
| where TimeGenerated >= ago(ioc_lookBack)
| where tolower(EmailSenderAddress) in (OfficeActivityUPNs)
| summarize LatestIndicatorTime = arg_max(TimeGenerated, *) by IndicatorId
| where Active == true and ExpirationDateTime > now()
| where Description !contains_cs "State: inactive;" and Description !contains_cs "State: falsepos;"
// 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 (OfficeEvents) on $left.EmailSenderAddress == $right.UserId
| where OfficeActivity_TimeGenerated < ExpirationDateTime
| summarize OfficeActivity_TimeGenerated = arg_max(OfficeActivity_TimeGenerated, *) by IndicatorId, UserId
| project OfficeActivity_TimeGenerated, Description, ActivityGroupNames, IndicatorId, ThreatType, Url, ExpirationDateTime, ConfidenceScore, EmailSenderName, EmailRecipient, EmailSourceDomain, EmailSourceIpAddress, EmailSubject, FileHashValue, FileHashType, UserId, ClientIP, Operation, UserType, RecordType, OfficeWorkload, Parameters
| extend Name = tostring(split(UserId, '@', 0)[0]), UPNSuffix = tostring(split(UserId, '@', 1)[0])
| extend timestamp = OfficeActivity_TimeGenerated

YAML

entityMappings:
- fieldMappings:
  - columnName: UserId
    identifier: FullName
  - columnName: Name
    identifier: Name
  - columnName: UPNSuffix
    identifier: UPNSuffix
  entityType: Account
- fieldMappings:
  - columnName: ClientIP
    identifier: Address
  entityType: IP
- fieldMappings:
  - columnName: Url
    identifier: Url
  entityType: URL
severity: Medium
name: TI map Email entity to OfficeActivity
triggerThreshold: 0
triggerOperator: gt
OriginalUri: https://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Threat Intelligence/Analytic Rules/EmailEntity_OfficeActivity.yaml
id: 4a3f5ed7-8da5-4ce2-af6f-c9ada45060f2
kind: Scheduled
queryFrequency: 1h
relevantTechniques:
- T1566
description: |
    'Identifies a match in OfficeActivity table from any Email IOC from TI'
query: |
  let dt_lookBack = 1h;
  let ioc_lookBack = 14d;
  let emailregex = @'^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+$';
  let OfficeEvents = materialize(
    OfficeActivity
    | where isnotempty(UserId)
    | where TimeGenerated >= ago(dt_lookBack)
    | where UserId matches regex emailregex
    | project-rename  OfficeActivity_TimeGenerated = TimeGenerated);
  let OfficeActivityUPNs = OfficeEvents | distinct UserId = tolower(UserId) | summarize make_list(UserId);
  ThreatIntelligenceIndicator
  | where isnotempty(EmailSenderAddress)
  | where TimeGenerated >= ago(ioc_lookBack)
  | where tolower(EmailSenderAddress) in (OfficeActivityUPNs)
  | summarize LatestIndicatorTime = arg_max(TimeGenerated, *) by IndicatorId
  | where Active == true and ExpirationDateTime > now()
  | where Description !contains_cs "State: inactive;" and Description !contains_cs "State: falsepos;"
  // 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 (OfficeEvents) on $left.EmailSenderAddress == $right.UserId
  | where OfficeActivity_TimeGenerated < ExpirationDateTime
  | summarize OfficeActivity_TimeGenerated = arg_max(OfficeActivity_TimeGenerated, *) by IndicatorId, UserId
  | project OfficeActivity_TimeGenerated, Description, ActivityGroupNames, IndicatorId, ThreatType, Url, ExpirationDateTime, ConfidenceScore, EmailSenderName, EmailRecipient, EmailSourceDomain, EmailSourceIpAddress, EmailSubject, FileHashValue, FileHashType, UserId, ClientIP, Operation, UserType, RecordType, OfficeWorkload, Parameters
  | extend Name = tostring(split(UserId, '@', 0)[0]), UPNSuffix = tostring(split(UserId, '@', 1)[0])
  | extend timestamp = OfficeActivity_TimeGenerated  
version: 1.2.7
tactics:
- InitialAccess
queryPeriod: 14d
requiredDataConnectors:
- dataTypes:
  - OfficeActivity
  connectorId: Office365
- dataTypes:
  - ThreatIntelligenceIndicator
  connectorId: ThreatIntelligence
- dataTypes:
  - ThreatIntelligenceIndicator
  connectorId: ThreatIntelligenceTaxii
- dataTypes:
  - ThreatIntelligenceIndicator
  connectorId: MicrosoftDefenderThreatIntelligence

ARM