TI map Email entity to SigninLogs

Back


Id	4b5a7f32-899d-4d22-8de2-0ec90b911a72
Rulename	TI map Email entity to SigninLogs
Description	Identifies a match in SigninLogs table from any Email IOC from TI
Severity	Medium
Tactics	InitialAccess
Techniques	T1566
Required data connectors	AzureActiveDirectory MicrosoftDefenderThreatIntelligence 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 (NEW)/Analytic Rules/EmailEntity_SigninLogs.yaml
Version	1.2.11
Arm template	4b5a7f32-899d-4d22-8de2-0ec90b911a72.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 Signins = materialize(union isfuzzy=true
( SigninLogs | where TimeGenerated >= ago(dt_lookBack)),
( AADNonInteractiveUserSignInLogs | where TimeGenerated >= ago(dt_lookBack)
    | extend Status = todynamic(Status), LocationDetails = todynamic(LocationDetails))
| where isnotempty(UserPrincipalName) and UserPrincipalName matches regex emailregex
| extend UserPrincipalName = tolower(UserPrincipalName)
| extend Status = todynamic(Status), LocationDetails = todynamic(LocationDetails)
| extend StatusCode = tostring(Status.errorCode), StatusDetails = tostring(Status.additionalDetails)
| extend State = tostring(LocationDetails.state), City = tostring(LocationDetails.city), Region = tostring(LocationDetails.countryOrRegion)
| extend SigninLogs_TimeGenerated = TimeGenerated);
let SigninUPNs = Signins | distinct UserPrincipalName | summarize make_list(UserPrincipalName);
ThreatIntelIndicators
//Filtering the table for Email related IOCs
| extend IndicatorType = replace(@"\[|\]|\""", "", tostring(split(ObservableKey, ":", 0)))
| where IndicatorType == "email-addr"
| extend EmailSenderAddress = ObservableValue
| extend EmailSourceDomain = substring(EmailSenderAddress, indexof(EmailSenderAddress, "@") + 1, strlen(EmailSenderAddress) - indexof(EmailSenderAddress, "@") - 1)
| extend TrafficLightProtocolLevel = tostring(parse_json(AdditionalFields).TLPLevel)
| extend Id = tostring(split(Id, "--")[2])
| extend Url = iff(ObservableKey == "url:value", ObservableValue, "")
| where TimeGenerated >= ago(ioc_lookBack)
| where EmailSenderAddress in (SigninUPNs)
| summarize LatestIndicatorTime = arg_max(TimeGenerated, *) by Id, ObservableValue
| where IsActive and (ValidUntil > now() or isempty(ValidUntil))
| extend Description = tostring(parse_json(Data).description)
| where Description !contains_cs "State: inactive;" and Description !contains_cs "State: falsepos;"
  | project-reorder *, TrafficLightProtocolLevel, EmailSenderAddress, EmailSourceDomain, Type
| join kind=innerunique (Signins) on $left.EmailSenderAddress == $right.UserPrincipalName
| where SigninLogs_TimeGenerated < ValidUntil
| summarize SigninLogs_TimeGenerated = arg_max(SigninLogs_TimeGenerated, *) by Id, UserPrincipalName
| extend Description = tostring(parse_json(Data).description)
| extend ActivityGroupNames = extract(@"ActivityGroup:(\S+)", 1, tostring(parse_json(Data).labels))
| project SigninLogs_TimeGenerated, Description, ActivityGroupNames, Id, ValidUntil, Confidence, EmailSenderAddress, IPAddress, UserPrincipalName, AppDisplayName, StatusCode, StatusDetails, NetworkLocationDetails, Type, Url
| extend Name = tostring(split(UserPrincipalName, '@', 0)[0]), UPNSuffix = tostring(split(UserPrincipalName, '@', 1)[0])
| extend timestamp = SigninLogs_TimeGenerated

YAML

relevantTechniques:
- T1566
entityMappings:
- fieldMappings:
  - columnName: UserPrincipalName
    identifier: FullName
  - columnName: Name
    identifier: Name
  - columnName: UPNSuffix
    identifier: UPNSuffix
  entityType: Account
- fieldMappings:
  - columnName: IPAddress
    identifier: Address
  entityType: IP
- fieldMappings:
  - columnName: Url
    identifier: Url
  entityType: URL
triggerThreshold: 0
description: |
    'Identifies a match in SigninLogs table from any Email IOC from TI'
requiredDataConnectors:
- connectorId: ThreatIntelligence
  dataTypes:
  - ThreatIntelIndicators
- connectorId: ThreatIntelligenceTaxii
  dataTypes:
  - ThreatIntelIndicators
- connectorId: AzureActiveDirectory
  dataTypes:
  - SigninLogs
- connectorId: AzureActiveDirectory
  dataTypes:
  - AADNonInteractiveUserSignInLogs
- connectorId: MicrosoftDefenderThreatIntelligence
  dataTypes:
  - ThreatIntelIndicators
triggerOperator: gt
version: 1.2.11
OriginalUri: https://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Threat Intelligence (NEW)/Analytic Rules/EmailEntity_SigninLogs.yaml
id: 4b5a7f32-899d-4d22-8de2-0ec90b911a72
queryFrequency: 1h
query: |
  let dt_lookBack = 1h;
  let ioc_lookBack = 14d;
  let emailregex = @'^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+$';
  let Signins = materialize(union isfuzzy=true
  ( SigninLogs | where TimeGenerated >= ago(dt_lookBack)),
  ( AADNonInteractiveUserSignInLogs | where TimeGenerated >= ago(dt_lookBack)
      | extend Status = todynamic(Status), LocationDetails = todynamic(LocationDetails))
  | where isnotempty(UserPrincipalName) and UserPrincipalName matches regex emailregex
  | extend UserPrincipalName = tolower(UserPrincipalName)
  | extend Status = todynamic(Status), LocationDetails = todynamic(LocationDetails)
  | extend StatusCode = tostring(Status.errorCode), StatusDetails = tostring(Status.additionalDetails)
  | extend State = tostring(LocationDetails.state), City = tostring(LocationDetails.city), Region = tostring(LocationDetails.countryOrRegion)
  | extend SigninLogs_TimeGenerated = TimeGenerated);
  let SigninUPNs = Signins | distinct UserPrincipalName | summarize make_list(UserPrincipalName);
  ThreatIntelIndicators
  //Filtering the table for Email related IOCs
  | extend IndicatorType = replace(@"\[|\]|\""", "", tostring(split(ObservableKey, ":", 0)))
  | where IndicatorType == "email-addr"
  | extend EmailSenderAddress = ObservableValue
  | extend EmailSourceDomain = substring(EmailSenderAddress, indexof(EmailSenderAddress, "@") + 1, strlen(EmailSenderAddress) - indexof(EmailSenderAddress, "@") - 1)
  | extend TrafficLightProtocolLevel = tostring(parse_json(AdditionalFields).TLPLevel)
  | extend Id = tostring(split(Id, "--")[2])
  | extend Url = iff(ObservableKey == "url:value", ObservableValue, "")
  | where TimeGenerated >= ago(ioc_lookBack)
  | where EmailSenderAddress in (SigninUPNs)
  | summarize LatestIndicatorTime = arg_max(TimeGenerated, *) by Id, ObservableValue
  | where IsActive and (ValidUntil > now() or isempty(ValidUntil))
  | extend Description = tostring(parse_json(Data).description)
  | where Description !contains_cs "State: inactive;" and Description !contains_cs "State: falsepos;"
    | project-reorder *, TrafficLightProtocolLevel, EmailSenderAddress, EmailSourceDomain, Type
  | join kind=innerunique (Signins) on $left.EmailSenderAddress == $right.UserPrincipalName
  | where SigninLogs_TimeGenerated < ValidUntil
  | summarize SigninLogs_TimeGenerated = arg_max(SigninLogs_TimeGenerated, *) by Id, UserPrincipalName
  | extend Description = tostring(parse_json(Data).description)
  | extend ActivityGroupNames = extract(@"ActivityGroup:(\S+)", 1, tostring(parse_json(Data).labels))
  | project SigninLogs_TimeGenerated, Description, ActivityGroupNames, Id, ValidUntil, Confidence, EmailSenderAddress, IPAddress, UserPrincipalName, AppDisplayName, StatusCode, StatusDetails, NetworkLocationDetails, Type, Url
  | extend Name = tostring(split(UserPrincipalName, '@', 0)[0]), UPNSuffix = tostring(split(UserPrincipalName, '@', 1)[0])
  | extend timestamp = SigninLogs_TimeGenerated  
severity: Medium
queryPeriod: 14d
name: TI map Email entity to SigninLogs
tactics:
- InitialAccess
kind: Scheduled

ARM