ADFS Database Named Pipe Connection

id: dcdf9bfc-c239-4764-a9f9-3612e6dff49c
name: ADFS Database Named Pipe Connection
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]"'
severity: Medium
requiredDataConnectors:
  - connectorId: SecurityEvents
    dataTypes:
      - SecurityEvent
  - connectorId: WindowsSecurityEvents
    dataTypes:
      - SecurityEvent
queryFrequency: 1d
queryPeriod: 1d
triggerOperator: gt
triggerThreshold: 0
status: Available
tactics:
  - Collection
relevantTechniques:
  - T1005
tags:
  - Solorigate
  - NOBELIUM
  - SimuLand
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 HostName = tostring(split(Computer, ".")[0]), DomainIndex = toint(indexof(Computer, '.'))
  | extend HostNameDomain = iff(DomainIndex != -1, substring(Computer, DomainIndex + 1), Computer)
  | extend AccountName = tostring(split(UserName, @'\')[1]), AccountNTDomain = tostring(split(UserName, @'\')[0])
entityMappings:
  - entityType: Account
    fieldMappings:
      - identifier: FullName
        columnName: UserName
      - identifier: Name
        columnName: AccountName
      - identifier: NTDomain
        columnName: AccountNTDomain
  - entityType: Host
    fieldMappings:
      - identifier: FullName
        columnName: Computer
      - identifier: HostName
        columnName: HostName
      - identifier: DnsDomain
        columnName: HostNameDomain
version: 1.0.2
kind: Scheduled