Cisco - firewall block but success logon to Microsoft Entra ID

let aadFunc = (tableName:string){
CommonSecurityLog
| where DeviceVendor =~ "Cisco"
| where DeviceAction =~ "denied"
| where ipv4_is_private(SourceIP) == false
| summarize count() by SourceIP
| join (
    // Successful signins from IPs blocked by the firewall solution are suspect
    // Include fully successful sign-ins, but also ones that failed only at MFA stage
    // as that supposes the password was sucessfully guessed.
  table(tableName)
  | where ResultType in ("0", "50074", "50076")
) on $left.SourceIP == $right.IPAddress
| extend AccountName = tostring(split(Account, "@")[0]), AccountUPNSuffix = tostring(split(Account, "@")[1])
};
let aadSignin = aadFunc("SigninLogs");
let aadNonInt = aadFunc("AADNonInteractiveUserSignInLogs");
union isfuzzy=true aadSignin, aadNonInt

OriginalUri: https://github.com/Azure/Azure-Sentinel/blob/master/Detections/MultipleDataSources/SigninFirewallCorrelation.yaml
query: |
  let aadFunc = (tableName:string){
  CommonSecurityLog
  | where DeviceVendor =~ "Cisco"
  | where DeviceAction =~ "denied"
  | where ipv4_is_private(SourceIP) == false
  | summarize count() by SourceIP
  | join (
      // Successful signins from IPs blocked by the firewall solution are suspect
      // Include fully successful sign-ins, but also ones that failed only at MFA stage
      // as that supposes the password was sucessfully guessed.
    table(tableName)
    | where ResultType in ("0", "50074", "50076")
  ) on $left.SourceIP == $right.IPAddress
  | extend AccountName = tostring(split(Account, "@")[0]), AccountUPNSuffix = tostring(split(Account, "@")[1])
  };
  let aadSignin = aadFunc("SigninLogs");
  let aadNonInt = aadFunc("AADNonInteractiveUserSignInLogs");
  union isfuzzy=true aadSignin, aadNonInt  
description: |
  'Correlate IPs blocked by a Cisco firewall appliance with successful Microsoft Entra ID signins.
  Because the IP was blocked by the firewall, that same IP logging on successfully to Entra ID is potentially suspect and could indicate credential compromise for the user account.'  
severity: Medium
requiredDataConnectors:
- dataTypes:
  - CommonSecurityLog
  connectorId: CiscoASA
- dataTypes:
  - SigninLogs
  connectorId: AzureActiveDirectory
- dataTypes:
  - AADNonInteractiveUserSignInLogs
  connectorId: AzureActiveDirectory
name: Cisco - firewall block but success logon to Microsoft Entra ID
triggerThreshold: 0
metadata:
  source:
    kind: Community
  categories:
    domains:
    - Security - Network
  author:
    name: Microsoft Security Resarch
  support:
    tier: Community
tactics:
- InitialAccess
version: 1.0.6
relevantTechniques:
- T1078
triggerOperator: gt
entityMappings:
- entityType: Account
  fieldMappings:
  - columnName: UserPrincipalName
    identifier: FullName
  - columnName: AccountName
    identifier: Name
  - columnName: AccountUPNSuffix
    identifier: UPNSuffix
- entityType: IP
  fieldMappings:
  - columnName: SourceIP
    identifier: Address
id: 157c0cfc-d76d-463b-8755-c781608cdc1a
kind: Scheduled
queryFrequency: 1d
queryPeriod: 1d

{
  "$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/157c0cfc-d76d-463b-8755-c781608cdc1a')]",
      "kind": "Scheduled",
      "name": "[concat(parameters('workspace'),'/Microsoft.SecurityInsights/157c0cfc-d76d-463b-8755-c781608cdc1a')]",
      "properties": {
        "alertRuleTemplateName": "157c0cfc-d76d-463b-8755-c781608cdc1a",
        "customDetails": null,
        "description": "'Correlate IPs blocked by a Cisco firewall appliance with successful Microsoft Entra ID signins.\nBecause the IP was blocked by the firewall, that same IP logging on successfully to Entra ID is potentially suspect and could indicate credential compromise for the user account.'\n",
        "displayName": "Cisco - firewall block but success logon to Microsoft Entra ID",
        "enabled": true,
        "entityMappings": [
          {
            "entityType": "Account",
            "fieldMappings": [
              {
                "columnName": "UserPrincipalName",
                "identifier": "FullName"
              },
              {
                "columnName": "AccountName",
                "identifier": "Name"
              },
              {
                "columnName": "AccountUPNSuffix",
                "identifier": "UPNSuffix"
              }
            ]
          },
          {
            "entityType": "IP",
            "fieldMappings": [
              {
                "columnName": "SourceIP",
                "identifier": "Address"
              }
            ]
          }
        ],
        "OriginalUri": "https://github.com/Azure/Azure-Sentinel/blob/master/Detections/MultipleDataSources/SigninFirewallCorrelation.yaml",
        "query": "let aadFunc = (tableName:string){\nCommonSecurityLog\n| where DeviceVendor =~ \"Cisco\"\n| where DeviceAction =~ \"denied\"\n| where ipv4_is_private(SourceIP) == false\n| summarize count() by SourceIP\n| join (\n    // Successful signins from IPs blocked by the firewall solution are suspect\n    // Include fully successful sign-ins, but also ones that failed only at MFA stage\n    // as that supposes the password was sucessfully guessed.\n  table(tableName)\n  | where ResultType in (\"0\", \"50074\", \"50076\")\n) on $left.SourceIP == $right.IPAddress\n| extend AccountName = tostring(split(Account, \"@\")[0]), AccountUPNSuffix = tostring(split(Account, \"@\")[1])\n};\nlet aadSignin = aadFunc(\"SigninLogs\");\nlet aadNonInt = aadFunc(\"AADNonInteractiveUserSignInLogs\");\nunion isfuzzy=true aadSignin, aadNonInt\n",
        "queryFrequency": "P1D",
        "queryPeriod": "P1D",
        "severity": "Medium",
        "subTechniques": [],
        "suppressionDuration": "PT1H",
        "suppressionEnabled": false,
        "tactics": [
          "InitialAccess"
        ],
        "techniques": [
          "T1078"
        ],
        "templateVersion": "1.0.6",
        "triggerOperator": "GreaterThan",
        "triggerThreshold": 0
      },
      "type": "Microsoft.OperationalInsights/workspaces/providers/alertRules"
    }
  ]
}