Microsoft Sentinel Analytic Rules
cloudbrothers.infoAzure Sentinel RepoToggle Dark/Light/Auto modeToggle Dark/Light/Auto modeToggle Dark/Light/Auto modeBack to homepage

TI Map URL Entity to OfficeActivity Data

Back
Id36a9c9e5-3dc1-4ed9-afaa-1d13617bfc2b
RulenameTI Map URL Entity to OfficeActivity Data
DescriptionThis query identifies any URL indicators of compromise (IOCs) from threat intelligence (TI) by searching for matches in OfficeActivity data.
SeverityMedium
TacticsImpact
Required data connectorsMicrosoftDefenderThreatIntelligence
Office365
ThreatIntelligence
KindScheduled
Query frequency1h
Query period14d
Trigger threshold0
Trigger operatorgt
Source Urihttps://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Threat Intelligence/Analytic Rules/URLEntity_OfficeActivity.yaml
Version1.2.5
Arm template36a9c9e5-3dc1-4ed9-afaa-1d13617bfc2b.json
Deploy To Azure
let dt_lookBack = 1h;
let ioc_lookBack = 14d;
ThreatIntelligenceIndicator
| where TimeGenerated >= ago(ioc_lookBack) and ExpirationDateTime > now()
| summarize LatestIndicatorTime = arg_max(TimeGenerated, *) by IndicatorId
| where Active == true
// 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])
version: 1.2.5
name: TI Map URL Entity to OfficeActivity Data
severity: Medium
kind: Scheduled
queryPeriod: 14d
description: |
    'This query identifies any URL indicators of compromise (IOCs) from threat intelligence (TI) by searching for matches in OfficeActivity data.'
entityMappings:
- entityType: Account
  fieldMappings:
  - columnName: Name
    identifier: Name
  - columnName: UPNSuffix
    identifier: UPNSuffix
- entityType: URL
  fieldMappings:
  - columnName: Url
    identifier: Url
query: |
  let dt_lookBack = 1h;
  let ioc_lookBack = 14d;
  ThreatIntelligenceIndicator
  | where TimeGenerated >= ago(ioc_lookBack) and ExpirationDateTime > now()
  | summarize LatestIndicatorTime = arg_max(TimeGenerated, *) by IndicatorId
  | where Active == true
  // 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])  
tactics:
- Impact
triggerOperator: gt
triggerThreshold: 0
OriginalUri: https://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Threat Intelligence/Analytic Rules/URLEntity_OfficeActivity.yaml
requiredDataConnectors:
- connectorId: Office365
  dataTypes:
  - OfficeActivity
- connectorId: ThreatIntelligence
  dataTypes:
  - ThreatIntelligenceIndicator
- connectorId: MicrosoftDefenderThreatIntelligence
  dataTypes:
  - ThreatIntelligenceIndicator
queryFrequency: 1h
id: 36a9c9e5-3dc1-4ed9-afaa-1d13617bfc2b
{
  "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
  "contentVersion": "1.0.0.0",
  "parameters": {
    "workspace": {
      "type": "String"
    }
  },
  "resources": [
    {
      "id": "[concat(resourceId('Microsoft.OperationalInsights/workspaces/providers', parameters('workspace'), 'Microsoft.SecurityInsights'),'/alertRules/36a9c9e5-3dc1-4ed9-afaa-1d13617bfc2b')]",
      "name": "[concat(parameters('workspace'),'/Microsoft.SecurityInsights/36a9c9e5-3dc1-4ed9-afaa-1d13617bfc2b')]",
      "type": "Microsoft.OperationalInsights/workspaces/providers/alertRules",
      "kind": "Scheduled",
      "apiVersion": "2022-11-01-preview",
      "properties": {
        "displayName": "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.'\n",
        "severity": "Medium",
        "enabled": true,
        "query": "let dt_lookBack = 1h;\nlet ioc_lookBack = 14d;\nThreatIntelligenceIndicator\n| where TimeGenerated >= ago(ioc_lookBack) and ExpirationDateTime > now()\n| summarize LatestIndicatorTime = arg_max(TimeGenerated, *) by IndicatorId\n| where Active == true\n// Picking up only IOC's that contain the entities we want\n| where isnotempty(Url)\n// 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\n| join kind=innerunique (\n  OfficeActivity\n  | where TimeGenerated >= ago(dt_lookBack)\n  //Extract the Url from a number of potential fields\n  | 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))\n  | where isnotempty(Url)\n  // Ensure we get a clean URL\n  | extend Url = tostring(split(Url, ';')[0])\n  | extend OfficeActivity_TimeGenerated = TimeGenerated\n  // Project a single user identity that we can use for entity mapping\n  | extend User = iif(isnotempty(UserId), UserId, iif(isnotempty(Actor), tostring(parse_json(Actor)[0].ID), tostring(parse_json(Parameters)[0].Value)))\n) on Url\n| where OfficeActivity_TimeGenerated < ExpirationDateTime\n| summarize OfficeActivity_TimeGenerated = arg_max(OfficeActivity_TimeGenerated, *) by IndicatorId, Url\n| project OfficeActivity_TimeGenerated, Description, ActivityGroupNames, IndicatorId, ThreatType, ExpirationDateTime, ConfidenceScore, Operation,\nUserType, OfficeWorkload, Parameters, Url, User\n| extend timestamp = OfficeActivity_TimeGenerated, Name = tostring(split(User, '@', 0)[0]), UPNSuffix = tostring(split(User, '@', 1)[0])\n",
        "queryFrequency": "PT1H",
        "queryPeriod": "P14D",
        "triggerOperator": "GreaterThan",
        "triggerThreshold": 0,
        "suppressionDuration": "PT1H",
        "suppressionEnabled": false,
        "tactics": [
          "Impact"
        ],
        "alertRuleTemplateName": "36a9c9e5-3dc1-4ed9-afaa-1d13617bfc2b",
        "customDetails": null,
        "entityMappings": [
          {
            "fieldMappings": [
              {
                "columnName": "Name",
                "identifier": "Name"
              },
              {
                "columnName": "UPNSuffix",
                "identifier": "UPNSuffix"
              }
            ],
            "entityType": "Account"
          },
          {
            "fieldMappings": [
              {
                "columnName": "Url",
                "identifier": "Url"
              }
            ],
            "entityType": "URL"
          }
        ],
        "OriginalUri": "https://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Threat Intelligence/Analytic Rules/URLEntity_OfficeActivity.yaml",
        "templateVersion": "1.2.5"
      }
    }
  ]
}