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

GreyNoise TI map IP entity to OfficeActivity

Back
Idc51628fe-999c-5150-9fd7-660fc4f58ed2
RulenameGreyNoise TI map IP entity to OfficeActivity
DescriptionThis query maps any GreyNoise IP indicators of compromise (IOCs) from threat intelligence (TI), by searching for matches in OfficeActivity.
SeverityMedium
TacticsImpact
Required data connectorsGreyNoise2SentinelAPI
MicrosoftDefenderThreatIntelligence
Office365
ThreatIntelligence
ThreatIntelligenceTaxii
KindScheduled
Query frequency1h
Query period14d
Trigger threshold0
Trigger operatorgt
Source Urihttps://github.com/Azure/Azure-Sentinel/blob/master/Solutions/GreyNoiseThreatIntelligence/Analytic Rules/GreyNoise_IPEntity_OfficeActivity.yaml
Version1.0.0
Arm templatec51628fe-999c-5150-9fd7-660fc4f58ed2.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
// Fetch threat intelligence indicators related to IP addresses
let IP_Indicators = ThreatIntelligenceIndicator
  | where TimeGenerated >= ago(ioc_lookBack)
  | summarize LatestIndicatorTime = arg_max(TimeGenerated, *) by IndicatorId
  | where Active == true and ExpirationDateTime > now()
  | where SourceSystem == 'GreyNoise'
  | where isnotempty(NetworkIP) or isnotempty(EmailSourceIpAddress) or isnotempty(NetworkDestinationIP) or isnotempty(NetworkSourceIP)
  | extend TI_ipEntity = iff(isnotempty(NetworkIP), NetworkIP, NetworkDestinationIP)
  | extend TI_ipEntity = iff(isempty(TI_ipEntity) and isnotempty(NetworkSourceIP), NetworkSourceIP, TI_ipEntity)
  | extend TI_ipEntity = iff(isempty(TI_ipEntity) and isnotempty(EmailSourceIpAddress), EmailSourceIpAddress, TI_ipEntity)
  | where ipv4_is_private(TI_ipEntity) == false and  TI_ipEntity !startswith "fe80" and TI_ipEntity !startswith "::" and TI_ipEntity !startswith "127.";
// Perform a join between IP indicators and OfficeActivity events
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
      | where TimeGenerated >= ago(dt_lookBack)
      | where isnotempty(ClientIP)
      | 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]), '')
      | extend OfficeActivity_TimeGenerated = TimeGenerated
  )
  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])
id: c51628fe-999c-5150-9fd7-660fc4f58ed2
name: GreyNoise TI map IP entity to OfficeActivity
requiredDataConnectors:
- connectorId: ThreatIntelligence
  dataTypes:
  - ThreatIntelligenceIndicator
- connectorId: ThreatIntelligenceTaxii
  dataTypes:
  - ThreatIntelligenceIndicator
- connectorId: MicrosoftDefenderThreatIntelligence
  dataTypes:
  - ThreatIntelligenceIndicator
- connectorId: Office365
  dataTypes:
  - OfficeActivity
- connectorId: GreyNoise2SentinelAPI
  dataTypes:
  - ThreatIntelligenceIndicator
severity: Medium
triggerThreshold: 0
OriginalUri: https://github.com/Azure/Azure-Sentinel/blob/master/Solutions/GreyNoiseThreatIntelligence/Analytic Rules/GreyNoise_IPEntity_OfficeActivity.yaml
kind: Scheduled
description: |
    This query maps any GreyNoise IP indicators of compromise (IOCs) from threat intelligence (TI), by searching for matches in OfficeActivity.
queryPeriod: 14d
triggerOperator: gt
queryFrequency: 1h
query: |
  let dt_lookBack = 1h; // Look back 1 hour for OfficeActivity events
  let ioc_lookBack = 14d; // Look back 14 days for threat intelligence indicators
  // Fetch threat intelligence indicators related to IP addresses
  let IP_Indicators = ThreatIntelligenceIndicator
    | where TimeGenerated >= ago(ioc_lookBack)
    | summarize LatestIndicatorTime = arg_max(TimeGenerated, *) by IndicatorId
    | where Active == true and ExpirationDateTime > now()
    | where SourceSystem == 'GreyNoise'
    | where isnotempty(NetworkIP) or isnotempty(EmailSourceIpAddress) or isnotempty(NetworkDestinationIP) or isnotempty(NetworkSourceIP)
    | extend TI_ipEntity = iff(isnotempty(NetworkIP), NetworkIP, NetworkDestinationIP)
    | extend TI_ipEntity = iff(isempty(TI_ipEntity) and isnotempty(NetworkSourceIP), NetworkSourceIP, TI_ipEntity)
    | extend TI_ipEntity = iff(isempty(TI_ipEntity) and isnotempty(EmailSourceIpAddress), EmailSourceIpAddress, TI_ipEntity)
    | where ipv4_is_private(TI_ipEntity) == false and  TI_ipEntity !startswith "fe80" and TI_ipEntity !startswith "::" and TI_ipEntity !startswith "127.";
  // Perform a join between IP indicators and OfficeActivity events
  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
        | where TimeGenerated >= ago(dt_lookBack)
        | where isnotempty(ClientIP)
        | 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]), '')
        | extend OfficeActivity_TimeGenerated = TimeGenerated
    )
    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])  
version: 1.0.0
tactics:
- Impact
entityMappings:
- fieldMappings:
  - 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
{
  "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
  "contentVersion": "1.0.0.0",
  "parameters": {
    "workspace": {
      "type": "String"
    }
  },
  "resources": [
    {
      "apiVersion": "2023-02-01-preview",
      "id": "[concat(resourceId('Microsoft.OperationalInsights/workspaces/providers', parameters('workspace'), 'Microsoft.SecurityInsights'),'/alertRules/c51628fe-999c-5150-9fd7-660fc4f58ed2')]",
      "kind": "Scheduled",
      "name": "[concat(parameters('workspace'),'/Microsoft.SecurityInsights/c51628fe-999c-5150-9fd7-660fc4f58ed2')]",
      "properties": {
        "alertRuleTemplateName": "c51628fe-999c-5150-9fd7-660fc4f58ed2",
        "customDetails": null,
        "description": "This query maps any GreyNoise IP indicators of compromise (IOCs) from threat intelligence (TI), by searching for matches in OfficeActivity.\n",
        "displayName": "GreyNoise TI map IP entity to OfficeActivity",
        "enabled": true,
        "entityMappings": [
          {
            "entityType": "Account",
            "fieldMappings": [
              {
                "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/GreyNoiseThreatIntelligence/Analytic Rules/GreyNoise_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\n// Fetch threat intelligence indicators related to IP addresses\nlet IP_Indicators = ThreatIntelligenceIndicator\n  | where TimeGenerated >= ago(ioc_lookBack)\n  | summarize LatestIndicatorTime = arg_max(TimeGenerated, *) by IndicatorId\n  | where Active == true and ExpirationDateTime > now()\n  | where SourceSystem == 'GreyNoise'\n  | where isnotempty(NetworkIP) or isnotempty(EmailSourceIpAddress) or isnotempty(NetworkDestinationIP) or isnotempty(NetworkSourceIP)\n  | extend TI_ipEntity = iff(isnotempty(NetworkIP), NetworkIP, NetworkDestinationIP)\n  | extend TI_ipEntity = iff(isempty(TI_ipEntity) and isnotempty(NetworkSourceIP), NetworkSourceIP, TI_ipEntity)\n  | extend TI_ipEntity = iff(isempty(TI_ipEntity) and isnotempty(EmailSourceIpAddress), EmailSourceIpAddress, TI_ipEntity)\n  | where ipv4_is_private(TI_ipEntity) == false and  TI_ipEntity !startswith \"fe80\" and TI_ipEntity !startswith \"::\" and TI_ipEntity !startswith \"127.\";\n// Perform a join between IP indicators and OfficeActivity events\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 (\n      OfficeActivity\n      | where TimeGenerated >= ago(dt_lookBack)\n      | where isnotempty(ClientIP)\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      | extend OfficeActivity_TimeGenerated = TimeGenerated\n  )\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,\n    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",
        "suppressionDuration": "PT1H",
        "suppressionEnabled": false,
        "tactics": [
          "Impact"
        ],
        "templateVersion": "1.0.0",
        "triggerOperator": "GreaterThan",
        "triggerThreshold": 0
      },
      "type": "Microsoft.OperationalInsights/workspaces/providers/alertRules"
    }
  ]
}