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

ADFS Database Named Pipe Connection

Back
Iddcdf9bfc-c239-4764-a9f9-3612e6dff49c
RulenameADFS Database Named Pipe Connection
DescriptionThis detection uses Sysmon telemetry to detect suspicious local connections via a named pipe to the AD FS configuration database (Windows Internal Database).

In order to use this query you need to be collecting Sysmon EventIdD 18 (Pipe Connected).

If you do not have Sysmon data in your workspace this query will raise an error stating:

Failed to resolve scalar expression named “[@Name]”
SeverityMedium
TacticsCollection
TechniquesT1005
Required data connectorsSecurityEvents
WindowsSecurityEvents
KindScheduled
Query frequency1d
Query period1d
Trigger threshold0
Trigger operatorgt
Source Urihttps://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Windows Security Events/Analytic Rules/ADFSDBNamedPipeConnection.yaml
Version1.0.1
Arm templatedcdf9bfc-c239-4764-a9f9-3612e6dff49c.json
Deploy To Azure
// Adjust this to use a longer timeframe to identify ADFS servers
//let lookback = 6d;
// Adjust this to adjust the key export detection  timeframe
//let timeframe = 1d;
// Start be identifying ADFS servers to reduce FP chance
let ADFS_Servers = (
Event
//| where TimeGenerated > ago(timeframe+lookback)
| where Source == "Microsoft-Windows-Sysmon"
| where EventID == 18
| extend EventData = parse_xml(EventData).DataItem.EventData.Data
| mv-expand bagexpansion=array EventData
| evaluate bag_unpack(EventData)
| extend Key = tostring(column_ifexists('@Name', "")), Value = column_ifexists('#text', "")
| evaluate pivot(Key, any(Value), TimeGenerated, Source, EventLog, Computer, EventLevel, EventLevelName, EventID, UserName, MG, ManagementGroupName, _ResourceId)
| extend Image = column_ifexists("Image", "")
| extend process = split(Image, '\\', -1)[-1]
| where process =~ "Microsoft.IdentityServer.ServiceHost.exe"
| summarize by Computer);
// Look for ADFS servers where Named Pipes event are present
Event
//| where TimeGenerated > ago(timeframe)
| where Source == "Microsoft-Windows-Sysmon"
| where EventID == 18
| where Computer in~ (ADFS_Servers)
| extend RenderedDescription = tostring(split(RenderedDescription, ":")[0])
| extend EventData = parse_xml(EventData).DataItem.EventData.Data
| mv-expand bagexpansion=array EventData
| evaluate bag_unpack(EventData)
| extend Key = tostring(column_ifexists('@Name', "")), Value = column_ifexists('#text', "")
| evaluate pivot(Key, any(Value), TimeGenerated, Source, EventLog, Computer, EventLevel, EventLevelName, EventID, UserName, RenderedDescription, MG, ManagementGroupName, Type, _ResourceId)
| extend RuleName = column_ifexists("RuleName", ""),
    TechniqueId = column_ifexists("TechniqueId", ""),
    TechniqueName = column_ifexists("TechniqueName", ""),
    Image = column_ifexists("Image", ""),
    PipeName = column_ifexists("PipeName", ""),
    EventType = column_ifexists("EventType", "")
| parse RuleName with * 'technique_id=' TechniqueId ',' * 'technique_name=' TechniqueName
// Look for Pipe related to querying the WID
| where PipeName == "\\MICROSOFT##WID\\tsql\\query"
| extend process = split(Image, '\\', -1)[-1]
// Exclude expected processes
| where process !in ("Microsoft.IdentityServer.ServiceHost.exe", "Microsoft.Identity.Health.Adfs.PshSurrogate.exe", "AzureADConnect.exe", "Microsoft.Tri.Sensor.exe", "wsmprovhost.exe","mmc.exe", "sqlservr.exe")
| extend Operation = RenderedDescription
| project-reorder TimeGenerated, EventType, Operation, process, Image, Computer, UserName
| extend HostCustomEntity = Computer, AccountCustomEntity = UserName
tags:
- Solorigate
- NOBELIUM
- SimuLand
status: Available
triggerThreshold: 0
relevantTechniques:
- T1005
OriginalUri: https://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Windows Security Events/Analytic Rules/ADFSDBNamedPipeConnection.yaml
requiredDataConnectors:
- dataTypes:
  - SecurityEvent
  connectorId: SecurityEvents
- dataTypes:
  - SecurityEvent
  connectorId: WindowsSecurityEvents
queryPeriod: 1d
tactics:
- Collection
severity: Medium
triggerOperator: gt
description: |
  'This detection uses Sysmon telemetry to detect suspicious local connections via a named pipe to the AD FS configuration database (Windows Internal Database).
  In order to use this query you need to be collecting Sysmon EventIdD 18 (Pipe Connected).
  If you do not have Sysmon data in your workspace this query will raise an error stating:
  Failed to resolve scalar expression named "[@Name]"'  
query: |
  // Adjust this to use a longer timeframe to identify ADFS servers
  //let lookback = 6d;
  // Adjust this to adjust the key export detection  timeframe
  //let timeframe = 1d;
  // Start be identifying ADFS servers to reduce FP chance
  let ADFS_Servers = (
  Event
  //| where TimeGenerated > ago(timeframe+lookback)
  | where Source == "Microsoft-Windows-Sysmon"
  | where EventID == 18
  | extend EventData = parse_xml(EventData).DataItem.EventData.Data
  | mv-expand bagexpansion=array EventData
  | evaluate bag_unpack(EventData)
  | extend Key = tostring(column_ifexists('@Name', "")), Value = column_ifexists('#text', "")
  | evaluate pivot(Key, any(Value), TimeGenerated, Source, EventLog, Computer, EventLevel, EventLevelName, EventID, UserName, MG, ManagementGroupName, _ResourceId)
  | extend Image = column_ifexists("Image", "")
  | extend process = split(Image, '\\', -1)[-1]
  | where process =~ "Microsoft.IdentityServer.ServiceHost.exe"
  | summarize by Computer);
  // Look for ADFS servers where Named Pipes event are present
  Event
  //| where TimeGenerated > ago(timeframe)
  | where Source == "Microsoft-Windows-Sysmon"
  | where EventID == 18
  | where Computer in~ (ADFS_Servers)
  | extend RenderedDescription = tostring(split(RenderedDescription, ":")[0])
  | extend EventData = parse_xml(EventData).DataItem.EventData.Data
  | mv-expand bagexpansion=array EventData
  | evaluate bag_unpack(EventData)
  | extend Key = tostring(column_ifexists('@Name', "")), Value = column_ifexists('#text', "")
  | evaluate pivot(Key, any(Value), TimeGenerated, Source, EventLog, Computer, EventLevel, EventLevelName, EventID, UserName, RenderedDescription, MG, ManagementGroupName, Type, _ResourceId)
  | extend RuleName = column_ifexists("RuleName", ""),
      TechniqueId = column_ifexists("TechniqueId", ""),
      TechniqueName = column_ifexists("TechniqueName", ""),
      Image = column_ifexists("Image", ""),
      PipeName = column_ifexists("PipeName", ""),
      EventType = column_ifexists("EventType", "")
  | parse RuleName with * 'technique_id=' TechniqueId ',' * 'technique_name=' TechniqueName
  // Look for Pipe related to querying the WID
  | where PipeName == "\\MICROSOFT##WID\\tsql\\query"
  | extend process = split(Image, '\\', -1)[-1]
  // Exclude expected processes
  | where process !in ("Microsoft.IdentityServer.ServiceHost.exe", "Microsoft.Identity.Health.Adfs.PshSurrogate.exe", "AzureADConnect.exe", "Microsoft.Tri.Sensor.exe", "wsmprovhost.exe","mmc.exe", "sqlservr.exe")
  | extend Operation = RenderedDescription
  | project-reorder TimeGenerated, EventType, Operation, process, Image, Computer, UserName
  | extend HostCustomEntity = Computer, AccountCustomEntity = UserName  
name: ADFS Database Named Pipe Connection
version: 1.0.1
kind: Scheduled
id: dcdf9bfc-c239-4764-a9f9-3612e6dff49c
queryFrequency: 1d
entityMappings:
- entityType: Account
  fieldMappings:
  - columnName: AccountCustomEntity
    identifier: FullName
- entityType: Host
  fieldMappings:
  - columnName: HostCustomEntity
    identifier: FullName
{
  "$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/dcdf9bfc-c239-4764-a9f9-3612e6dff49c')]",
      "kind": "Scheduled",
      "name": "[concat(parameters('workspace'),'/Microsoft.SecurityInsights/dcdf9bfc-c239-4764-a9f9-3612e6dff49c')]",
      "properties": {
        "alertRuleTemplateName": "dcdf9bfc-c239-4764-a9f9-3612e6dff49c",
        "customDetails": null,
        "description": "'This detection uses Sysmon telemetry to detect suspicious local connections via a named pipe to the AD FS configuration database (Windows Internal Database).\nIn order to use this query you need to be collecting Sysmon EventIdD 18 (Pipe Connected).\nIf you do not have Sysmon data in your workspace this query will raise an error stating:\nFailed to resolve scalar expression named \"[@Name]\"'\n",
        "displayName": "ADFS Database Named Pipe Connection",
        "enabled": true,
        "entityMappings": [
          {
            "entityType": "Account",
            "fieldMappings": [
              {
                "columnName": "AccountCustomEntity",
                "identifier": "FullName"
              }
            ]
          },
          {
            "entityType": "Host",
            "fieldMappings": [
              {
                "columnName": "HostCustomEntity",
                "identifier": "FullName"
              }
            ]
          }
        ],
        "OriginalUri": "https://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Windows Security Events/Analytic Rules/ADFSDBNamedPipeConnection.yaml",
        "query": "// Adjust this to use a longer timeframe to identify ADFS servers\n//let lookback = 6d;\n// Adjust this to adjust the key export detection  timeframe\n//let timeframe = 1d;\n// Start be identifying ADFS servers to reduce FP chance\nlet ADFS_Servers = (\nEvent\n//| where TimeGenerated > ago(timeframe+lookback)\n| where Source == \"Microsoft-Windows-Sysmon\"\n| where EventID == 18\n| extend EventData = parse_xml(EventData).DataItem.EventData.Data\n| mv-expand bagexpansion=array EventData\n| evaluate bag_unpack(EventData)\n| extend Key = tostring(column_ifexists('@Name', \"\")), Value = column_ifexists('#text', \"\")\n| evaluate pivot(Key, any(Value), TimeGenerated, Source, EventLog, Computer, EventLevel, EventLevelName, EventID, UserName, MG, ManagementGroupName, _ResourceId)\n| extend Image = column_ifexists(\"Image\", \"\")\n| extend process = split(Image, '\\\\', -1)[-1]\n| where process =~ \"Microsoft.IdentityServer.ServiceHost.exe\"\n| summarize by Computer);\n// Look for ADFS servers where Named Pipes event are present\nEvent\n//| where TimeGenerated > ago(timeframe)\n| where Source == \"Microsoft-Windows-Sysmon\"\n| where EventID == 18\n| where Computer in~ (ADFS_Servers)\n| extend RenderedDescription = tostring(split(RenderedDescription, \":\")[0])\n| extend EventData = parse_xml(EventData).DataItem.EventData.Data\n| mv-expand bagexpansion=array EventData\n| evaluate bag_unpack(EventData)\n| extend Key = tostring(column_ifexists('@Name', \"\")), Value = column_ifexists('#text', \"\")\n| evaluate pivot(Key, any(Value), TimeGenerated, Source, EventLog, Computer, EventLevel, EventLevelName, EventID, UserName, RenderedDescription, MG, ManagementGroupName, Type, _ResourceId)\n| extend RuleName = column_ifexists(\"RuleName\", \"\"),\n    TechniqueId = column_ifexists(\"TechniqueId\", \"\"),\n    TechniqueName = column_ifexists(\"TechniqueName\", \"\"),\n    Image = column_ifexists(\"Image\", \"\"),\n    PipeName = column_ifexists(\"PipeName\", \"\"),\n    EventType = column_ifexists(\"EventType\", \"\")\n| parse RuleName with * 'technique_id=' TechniqueId ',' * 'technique_name=' TechniqueName\n// Look for Pipe related to querying the WID\n| where PipeName == \"\\\\MICROSOFT##WID\\\\tsql\\\\query\"\n| extend process = split(Image, '\\\\', -1)[-1]\n// Exclude expected processes\n| where process !in (\"Microsoft.IdentityServer.ServiceHost.exe\", \"Microsoft.Identity.Health.Adfs.PshSurrogate.exe\", \"AzureADConnect.exe\", \"Microsoft.Tri.Sensor.exe\", \"wsmprovhost.exe\",\"mmc.exe\", \"sqlservr.exe\")\n| extend Operation = RenderedDescription\n| project-reorder TimeGenerated, EventType, Operation, process, Image, Computer, UserName\n| extend HostCustomEntity = Computer, AccountCustomEntity = UserName\n",
        "queryFrequency": "P1D",
        "queryPeriod": "P1D",
        "severity": "Medium",
        "status": "Available",
        "suppressionDuration": "PT1H",
        "suppressionEnabled": false,
        "tactics": [
          "Collection"
        ],
        "tags": [
          "Solorigate",
          "NOBELIUM",
          "SimuLand"
        ],
        "techniques": [
          "T1005"
        ],
        "templateVersion": "1.0.1",
        "triggerOperator": "GreaterThan",
        "triggerThreshold": 0
      },
      "type": "Microsoft.OperationalInsights/workspaces/providers/alertRules"
    }
  ]
}