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

TI map IP entity to OfficeActivity

Back
Idf15370f4-c6fa-42c5-9be4-1d308f40284e
RulenameTI map IP entity to OfficeActivity
DescriptionThis query maps any IP indicators of compromise (IOCs) from threat intelligence (TI), by searching for matches in OfficeActivity.
SeverityMedium
TacticsCommandAndControl
TechniquesT1071
Required data connectorsMicrosoftDefenderThreatIntelligence
Office365
ThreatIntelligence
ThreatIntelligenceTaxii
KindScheduled
Query frequency1h
Query period14d
Trigger threshold0
Trigger operatorgt
Source Urihttps://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Threat Intelligence/Analytic Rules/IPEntity_OfficeActivity.yaml
Version1.4.3
Arm templatef15370f4-c6fa-42c5-9be4-1d308f40284e.json
Deploy To Azure
let dt_lookBack = 1h; // Look back 1 hour for OfficeActivity events
let ioc_lookBack = 14d; // Look back 14 days for threat intelligence indicators
let OfficeActivity_ = materialize(OfficeActivity
  | where isnotempty(ClientIP)
  | where TimeGenerated >= ago(dt_lookBack)
  | extend ClientIPValues = extract_all(@'\[?(::ffff:)?(?P<IPAddress>(\d+\.\d+\.\d+\.\d+)|[^\]%]+)(%\d+)?\]?([-:](?P<Port>\d+))?', dynamic(["IPAddress", "Port"]), ClientIP)[0]
  | extend IPAddress = iff(array_length(ClientIPValues) > 0, tostring(ClientIPValues[0]), '')
  | project-rename OfficeActivity_TimeGenerated = TimeGenerated);
let ActivityIPs = OfficeActivity_ | summarize IPs = make_list(IPAddress);
// Fetch threat intelligence indicators related to IP addresses
let IP_Indicators = materialize(ThreatIntelligenceIndicator
  | where isnotempty(NetworkIP) or isnotempty(EmailSourceIpAddress) or isnotempty(NetworkDestinationIP) or isnotempty(NetworkSourceIP)
  | where TimeGenerated >= ago(ioc_lookBack)
  | extend TI_ipEntity = coalesce(NetworkDestinationIP, NetworkSourceIP, EmailSourceIpAddress)
  | where TI_ipEntity in (ActivityIPs)
  | 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;");
IP_Indicators
// Use innerunique to keep performance fast and result set low, as we only need one match to indicate potential malicious activity that needs investigation
| join kind=innerunique (OfficeActivity_)
  on $left.TI_ipEntity == $right.IPAddress
// Filter out OfficeActivity events that occurred after the expiration of the corresponding indicator
| where OfficeActivity_TimeGenerated < ExpirationDateTime
// Group the results by IndicatorId and keep the OfficeActivity event with the latest timestamp
| summarize OfficeActivity_TimeGenerated = arg_max(OfficeActivity_TimeGenerated, *) by IndicatorId
// Select the desired output fields
| project OfficeActivity_TimeGenerated, Description, ActivityGroupNames, IndicatorId, ThreatType, Url, ExpirationDateTime, ConfidenceScore, TI_ipEntity, ClientIP, UserId, Operation, ResultStatus, RecordType, OfficeObjectId, NetworkIP, NetworkDestinationIP, NetworkSourceIP, EmailSourceIpAddress, Type
| extend timestamp = OfficeActivity_TimeGenerated, Name = tostring(split(UserId, '@', 0)[0]), UPNSuffix = tostring(split(UserId, '@', 1)[0])
query: |
  let dt_lookBack = 1h; // Look back 1 hour for OfficeActivity events
  let ioc_lookBack = 14d; // Look back 14 days for threat intelligence indicators
  let OfficeActivity_ = materialize(OfficeActivity
    | where isnotempty(ClientIP)
    | where TimeGenerated >= ago(dt_lookBack)
    | extend ClientIPValues = extract_all(@'\[?(::ffff:)?(?P<IPAddress>(\d+\.\d+\.\d+\.\d+)|[^\]%]+)(%\d+)?\]?([-:](?P<Port>\d+))?', dynamic(["IPAddress", "Port"]), ClientIP)[0]
    | extend IPAddress = iff(array_length(ClientIPValues) > 0, tostring(ClientIPValues[0]), '')
    | project-rename OfficeActivity_TimeGenerated = TimeGenerated);
  let ActivityIPs = OfficeActivity_ | summarize IPs = make_list(IPAddress);
  // Fetch threat intelligence indicators related to IP addresses
  let IP_Indicators = materialize(ThreatIntelligenceIndicator
    | where isnotempty(NetworkIP) or isnotempty(EmailSourceIpAddress) or isnotempty(NetworkDestinationIP) or isnotempty(NetworkSourceIP)
    | where TimeGenerated >= ago(ioc_lookBack)
    | extend TI_ipEntity = coalesce(NetworkDestinationIP, NetworkSourceIP, EmailSourceIpAddress)
    | where TI_ipEntity in (ActivityIPs)
    | 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;");
  IP_Indicators
  // Use innerunique to keep performance fast and result set low, as we only need one match to indicate potential malicious activity that needs investigation
  | join kind=innerunique (OfficeActivity_)
    on $left.TI_ipEntity == $right.IPAddress
  // Filter out OfficeActivity events that occurred after the expiration of the corresponding indicator
  | where OfficeActivity_TimeGenerated < ExpirationDateTime
  // Group the results by IndicatorId and keep the OfficeActivity event with the latest timestamp
  | summarize OfficeActivity_TimeGenerated = arg_max(OfficeActivity_TimeGenerated, *) by IndicatorId
  // Select the desired output fields
  | project OfficeActivity_TimeGenerated, Description, ActivityGroupNames, IndicatorId, ThreatType, Url, ExpirationDateTime, ConfidenceScore, TI_ipEntity, ClientIP, UserId, Operation, ResultStatus, RecordType, OfficeObjectId, NetworkIP, NetworkDestinationIP, NetworkSourceIP, EmailSourceIpAddress, Type
  | extend timestamp = OfficeActivity_TimeGenerated, Name = tostring(split(UserId, '@', 0)[0]), UPNSuffix = tostring(split(UserId, '@', 1)[0])  
queryFrequency: 1h
entityMappings:
- fieldMappings:
  - identifier: FullName
    columnName: UserId
  - identifier: Name
    columnName: Name
  - identifier: UPNSuffix
    columnName: UPNSuffix
  entityType: Account
- fieldMappings:
  - identifier: Address
    columnName: TI_ipEntity
  entityType: IP
- fieldMappings:
  - identifier: Url
    columnName: Url
  entityType: URL
OriginalUri: https://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Threat Intelligence/Analytic Rules/IPEntity_OfficeActivity.yaml
queryPeriod: 14d
kind: Scheduled
version: 1.4.3
triggerOperator: gt
tactics:
- CommandAndControl
relevantTechniques:
- T1071
name: TI map IP entity to OfficeActivity
triggerThreshold: 0
severity: Medium
description: |
    This query maps any IP indicators of compromise (IOCs) from threat intelligence (TI), by searching for matches in OfficeActivity.
requiredDataConnectors:
- dataTypes:
  - ThreatIntelligenceIndicator
  connectorId: ThreatIntelligence
- dataTypes:
  - ThreatIntelligenceIndicator
  connectorId: ThreatIntelligenceTaxii
- dataTypes:
  - ThreatIntelligenceIndicator
  connectorId: MicrosoftDefenderThreatIntelligence
- dataTypes:
  - OfficeActivity
  connectorId: Office365
id: f15370f4-c6fa-42c5-9be4-1d308f40284e
{
  "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
  "contentVersion": "1.0.0.0",
  "parameters": {
    "workspace": {
      "type": "String"
    }
  },
  "resources": [
    {
      "apiVersion": "2024-01-01-preview",
      "id": "[concat(resourceId('Microsoft.OperationalInsights/workspaces/providers', parameters('workspace'), 'Microsoft.SecurityInsights'),'/alertRules/f15370f4-c6fa-42c5-9be4-1d308f40284e')]",
      "kind": "Scheduled",
      "name": "[concat(parameters('workspace'),'/Microsoft.SecurityInsights/f15370f4-c6fa-42c5-9be4-1d308f40284e')]",
      "properties": {
        "alertRuleTemplateName": "f15370f4-c6fa-42c5-9be4-1d308f40284e",
        "customDetails": null,
        "description": "This query maps any IP indicators of compromise (IOCs) from threat intelligence (TI), by searching for matches in OfficeActivity.\n",
        "displayName": "TI map IP entity to OfficeActivity",
        "enabled": true,
        "entityMappings": [
          {
            "entityType": "Account",
            "fieldMappings": [
              {
                "columnName": "UserId",
                "identifier": "FullName"
              },
              {
                "columnName": "Name",
                "identifier": "Name"
              },
              {
                "columnName": "UPNSuffix",
                "identifier": "UPNSuffix"
              }
            ]
          },
          {
            "entityType": "IP",
            "fieldMappings": [
              {
                "columnName": "TI_ipEntity",
                "identifier": "Address"
              }
            ]
          },
          {
            "entityType": "URL",
            "fieldMappings": [
              {
                "columnName": "Url",
                "identifier": "Url"
              }
            ]
          }
        ],
        "OriginalUri": "https://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Threat Intelligence/Analytic Rules/IPEntity_OfficeActivity.yaml",
        "query": "let dt_lookBack = 1h; // Look back 1 hour for OfficeActivity events\nlet ioc_lookBack = 14d; // Look back 14 days for threat intelligence indicators\nlet OfficeActivity_ = materialize(OfficeActivity\n  | where isnotempty(ClientIP)\n  | where TimeGenerated >= ago(dt_lookBack)\n  | extend ClientIPValues = extract_all(@'\\[?(::ffff:)?(?P<IPAddress>(\\d+\\.\\d+\\.\\d+\\.\\d+)|[^\\]%]+)(%\\d+)?\\]?([-:](?P<Port>\\d+))?', dynamic([\"IPAddress\", \"Port\"]), ClientIP)[0]\n  | extend IPAddress = iff(array_length(ClientIPValues) > 0, tostring(ClientIPValues[0]), '')\n  | project-rename OfficeActivity_TimeGenerated = TimeGenerated);\nlet ActivityIPs = OfficeActivity_ | summarize IPs = make_list(IPAddress);\n// Fetch threat intelligence indicators related to IP addresses\nlet IP_Indicators = materialize(ThreatIntelligenceIndicator\n  | where isnotempty(NetworkIP) or isnotempty(EmailSourceIpAddress) or isnotempty(NetworkDestinationIP) or isnotempty(NetworkSourceIP)\n  | where TimeGenerated >= ago(ioc_lookBack)\n  | extend TI_ipEntity = coalesce(NetworkDestinationIP, NetworkSourceIP, EmailSourceIpAddress)\n  | where TI_ipEntity in (ActivityIPs)\n  | summarize LatestIndicatorTime = arg_max(TimeGenerated, *) by IndicatorId\n  | where  Active == true and ExpirationDateTime > now()\n  | where Description !contains_cs \"State: inactive;\" and Description !contains_cs \"State: falsepos;\");\nIP_Indicators\n// Use innerunique to keep performance fast and result set low, as we only need one match to indicate potential malicious activity that needs investigation\n| join kind=innerunique (OfficeActivity_)\n  on $left.TI_ipEntity == $right.IPAddress\n// Filter out OfficeActivity events that occurred after the expiration of the corresponding indicator\n| where OfficeActivity_TimeGenerated < ExpirationDateTime\n// Group the results by IndicatorId and keep the OfficeActivity event with the latest timestamp\n| summarize OfficeActivity_TimeGenerated = arg_max(OfficeActivity_TimeGenerated, *) by IndicatorId\n// Select the desired output fields\n| project OfficeActivity_TimeGenerated, Description, ActivityGroupNames, IndicatorId, ThreatType, Url, ExpirationDateTime, ConfidenceScore, TI_ipEntity, ClientIP, UserId, Operation, ResultStatus, RecordType, OfficeObjectId, NetworkIP, NetworkDestinationIP, NetworkSourceIP, EmailSourceIpAddress, Type\n| extend timestamp = OfficeActivity_TimeGenerated, Name = tostring(split(UserId, '@', 0)[0]), UPNSuffix = tostring(split(UserId, '@', 1)[0])\n",
        "queryFrequency": "PT1H",
        "queryPeriod": "P14D",
        "severity": "Medium",
        "subTechniques": [],
        "suppressionDuration": "PT1H",
        "suppressionEnabled": false,
        "tactics": [
          "CommandAndControl"
        ],
        "techniques": [
          "T1071"
        ],
        "templateVersion": "1.4.3",
        "triggerOperator": "GreaterThan",
        "triggerThreshold": 0
      },
      "type": "Microsoft.OperationalInsights/workspaces/providers/alertRules"
    }
  ]
}