TI map URL entity to Cloud App Events

let dt_lookBack = 1h;
let ioc_lookBack = 14d;
ThreatIntelIndicators
//extract key part of kv pair
| extend IndicatorType = replace(@"\[|\]|\""", "", tostring(split(ObservableKey, ":", 0)))
| where IndicatorType == "url"
| extend Url = ObservableValue
| extend IndicatorId = tostring(split(Id, "--")[2])
| where TimeGenerated >= ago(ioc_lookBack)
| summarize LatestIndicatorTime = arg_max(TimeGenerated, *) by IndicatorId, ObservableValue
| where IsActive and (ValidUntil > now() or isempty(ValidUntil))
| join kind=innerunique (CloudAppEvents
| where TimeGenerated >= ago(dt_lookBack)
| extend Url = extract("(http[s]?://(?:[a-zA-Z]|[0-9]|[$-_@.&+]|[!*\\(\\),]|(?:%[0-9a-fA-F][0-9a-fA-F]))+)", 1, tostring(ActivityObjects))
| extend userPrincipalName = tostring(RawEventData.UserId)
| extend TargetResourceDisplayName = tostring(ActivityObjects[0].displayName)
| extend CloudApps_TimeGenerated = TimeGenerated) on Url
| where CloudApps_TimeGenerated < ValidUntil
| summarize CloudApps_TimeGenerated = argmax(CloudApps_TimeGenerated, *) by IndicatorId, Url
| extend 
  Description = column_ifexists("max_CloudApps_TimeGenerated_Description", ""),
  ActivityGroupNames = column_ifexists("max_CloudApps_TimeGenerated_ActivityGroupNames", ""),
  ThreatType = column_ifexists("max_CloudApps_TimeGenerated_ThreatType", ""),
  ExpirationDateTime = column_ifexists("max_CloudApps_TimeGenerated_ExpirationDateTime", ""),
  ConfidenceScore = column_ifexists("max_CloudApps_TimeGenerated_ConfidenceScore", ""),
  IPAddress = column_ifexists("max_CloudApps_TimeGenerated_IPAddress", ""),
  AccountObjectId = column_ifexists("max_CloudApps_TimeGenerated_AccountObjectId", ""),
  AccountDisplayName = column_ifexists("max_CloudApps_TimeGenerated_AccountDisplayName", ""),
  ObjectName = column_ifexists("max_CloudApps_TimeGenerated_ObjectName", ""),
  Application = column_ifexists("max_CloudApps_TimeGenerated_Application", ""),
  ApplicationID = column_ifexists("max_CloudApps_TimeGenerated_ApplicationId", ""),
  userPrincipalName = column_ifexists("max_CloudApps_TimeGenerated_userPrincipalName", "")
| project CloudApps_TimeGenerated, Description, IndicatorId, ThreatType, ExpirationDateTime, ConfidenceScore, Url, IPAddress, userPrincipalName, AccountObjectId, AccountDisplayName, ObjectName, Application, ApplicationID

name: TI map URL entity to Cloud App Events
query: |
  let dt_lookBack = 1h;
  let ioc_lookBack = 14d;
  ThreatIntelIndicators
  //extract key part of kv pair
  | extend IndicatorType = replace(@"\[|\]|\""", "", tostring(split(ObservableKey, ":", 0)))
  | where IndicatorType == "url"
  | extend Url = ObservableValue
  | extend IndicatorId = tostring(split(Id, "--")[2])
  | where TimeGenerated >= ago(ioc_lookBack)
  | summarize LatestIndicatorTime = arg_max(TimeGenerated, *) by IndicatorId, ObservableValue
  | where IsActive and (ValidUntil > now() or isempty(ValidUntil))
  | join kind=innerunique (CloudAppEvents
  | where TimeGenerated >= ago(dt_lookBack)
  | extend Url = extract("(http[s]?://(?:[a-zA-Z]|[0-9]|[$-_@.&+]|[!*\\(\\),]|(?:%[0-9a-fA-F][0-9a-fA-F]))+)", 1, tostring(ActivityObjects))
  | extend userPrincipalName = tostring(RawEventData.UserId)
  | extend TargetResourceDisplayName = tostring(ActivityObjects[0].displayName)
  | extend CloudApps_TimeGenerated = TimeGenerated) on Url
  | where CloudApps_TimeGenerated < ValidUntil
  | summarize CloudApps_TimeGenerated = argmax(CloudApps_TimeGenerated, *) by IndicatorId, Url
  | extend 
    Description = column_ifexists("max_CloudApps_TimeGenerated_Description", ""),
    ActivityGroupNames = column_ifexists("max_CloudApps_TimeGenerated_ActivityGroupNames", ""),
    ThreatType = column_ifexists("max_CloudApps_TimeGenerated_ThreatType", ""),
    ExpirationDateTime = column_ifexists("max_CloudApps_TimeGenerated_ExpirationDateTime", ""),
    ConfidenceScore = column_ifexists("max_CloudApps_TimeGenerated_ConfidenceScore", ""),
    IPAddress = column_ifexists("max_CloudApps_TimeGenerated_IPAddress", ""),
    AccountObjectId = column_ifexists("max_CloudApps_TimeGenerated_AccountObjectId", ""),
    AccountDisplayName = column_ifexists("max_CloudApps_TimeGenerated_AccountDisplayName", ""),
    ObjectName = column_ifexists("max_CloudApps_TimeGenerated_ObjectName", ""),
    Application = column_ifexists("max_CloudApps_TimeGenerated_Application", ""),
    ApplicationID = column_ifexists("max_CloudApps_TimeGenerated_ApplicationId", ""),
    userPrincipalName = column_ifexists("max_CloudApps_TimeGenerated_userPrincipalName", "")
  | project CloudApps_TimeGenerated, Description, IndicatorId, ThreatType, ExpirationDateTime, ConfidenceScore, Url, IPAddress, userPrincipalName, AccountObjectId, AccountDisplayName, ObjectName, Application, ApplicationID  
entityMappings:
- entityType: Account
  fieldMappings:
  - columnName: AccountObjectId
    identifier: ObjectGuid
  - columnName: userPrincipalName
    identifier: FullName
  - columnName: AccountDisplayName
    identifier: DisplayName
- entityType: URL
  fieldMappings:
  - columnName: Url
    identifier: Url
- entityType: IP
  fieldMappings:
  - columnName: IPAddress
    identifier: Address
- entityType: CloudApplication
  fieldMappings:
  - columnName: Application
    identifier: Name
  - columnName: ApplicationID
    identifier: AppId
queryPeriod: 14d
tactics:
- CommandAndControl
triggerOperator: gt
kind: Scheduled
triggerThreshold: 0
OriginalUri: https://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Threat Intelligence (NEW)/Analytic Rules/URLEntity_CloudAppEvents_Updated.yaml
relevantTechniques:
- T1071
id: 526df43b-f514-477c-af7a-c8d3586457fb
severity: Medium
requiredDataConnectors:
- connectorId: MicrosoftThreatProtection
  dataTypes:
  - CloudAppEvents
- connectorId: MicrosoftDefenderThreatIntelligence
  dataTypes:
  - ThreatIntelIndicators
version: 1.0.6
description: |
    'Identifies compromises and attacks and detect malicious activities in one's URL entity from TI'
queryFrequency: 1h