Detection rules › Kusto

SQL Server spawning suspicious child process

Group by
SHA1
Author
FalconForce
Source
github.com/FalconForceTeam/FalconFriday

This query looks for potential abuse of the SQL Server stored procedure xp_cmdshell which allows command execution on the OS. Running xp_cmdshell on the system triggers the follow process chain: sqlservr.exe => xp_cmdshell 'whoami' => "cmd.exe /c" whoami => whoami.exe. This rule tries to identify running of suspicious commands as a grandchild of sqlservr.exe. The rule is based on a block-list of executables of LOLBINs and other known recon commands or any executable executed with a low prevalence.

MITRE ATT&CK coverage

Event coverage

Rule body kusto

let timeframe = 2*1h;
let default_global_prevalence = 0;
let lolbins = dynamic(["at.exe", "atbroker.exe", "bash.exe", "bitsadmin.exe", "certreq.exe", "certutil.exe", "cmd.exe", "cmdkey.exe", "cmstp.exe", "control.exe", "csc.exe", "cscript.exe", "desktopimgdownldr.exe", "dfsvc.exe", "diantz.exe", "diskshadow.exe", "dnscmd.exe", "esentutl.exe", "eventvwr.exe", "expand.exe", "extexport.exe", "extrac32.exe", "findstr.exe", "forfiles.exe", "ftp.exe", "gfxdownloadwrapper.exe", "gpscript.exe", "hh.exe", "ie4uinit.exe", "ieexec.exe", "ilasm.exe", "infdefaultinstall.exe", "installutil.exe", "jsc.exe", "makecab.exe", "mavinject.exe", "microsoft.workflow.compiler.exe", "mmc.exe", "mpcmdrun.exe", "msbuild.exe", "msconfig.exe", "msdt.exe", "mshta.exe", "msiexec.exe", "netsh.exe", "odbcconf.exe", "pcalua.exe", "pcwrun.exe", "pktmon.exe", "presentationhost.exe", "print.exe", "psr.exe", "rasautou.exe", "reg.exe", "regasm.exe", "regedit.exe", "regini.exe", "register-cimprovider.exe", "regsvcs.exe", "regsvr32.exe", "replace.exe", "rpcping.exe", "rundll32.exe", "runonce.exe", "runscripthelper.exe", "sc.exe", "schtasks.exe", "scriptrunner.exe", "syncappvpublishingserver.exe", "ttdinject.exe", "tttracer.exe", "vbc.exe", "verclsid.exe", "wab.exe", "wmic.exe", "wscript.exe", "wsreset.exe", "xwizard.exe", "agentexecutor.exe", "appvlp.exe", "bginfo.exe", "cdb.exe", "csi.exe", "devtoolslauncher.exe", "dnx.exe", "dotnet.exe", "dxcap.exe", "excel.exe", "mftrace.exe", "msdeploy.exe", "msxsl.exe", "ntdsutil.exe", "powerpnt.exe", "rcsi.exe", "sqldumper.exe", "sqlps.exe", "sqltoolsps.exe", "squirrel.exe", "te.exe", "tracker.exe", "vsjitdebugger.exe", "winword.exe", "wsl.exe", "powershell.exe", "pwsh.exe"]);
let binaries_of_interest = dynamic(["net.exe", "net1.exe", "whoami.exe", "ipconfig.exe", "tasklist.exe", "quser.exe", "tracert.exe", "route.exe", "runas.exe", "klist.exe", "wevtutil.exe", "wmiprvse.exe", "powershell.exe", "bash.exe", "qwinsta.exe", "rwinsta.exe", "replace.exe", "findstr.exe", "icacls.exe", "cacls.exe", "xcopy.exe", "robocopy.exe", "takeown.exe", "vssadmin.exe", "nltest.exe", "nltestk.exe", "sctasks.exe", "nbtstat.exe", "nbtinfo.exe", "mofcomp.exe", "nltestrk.exe", "dnscmd.exe", "registercimprovider.exe", "registercimprovider2.exe", "procdump", "ru.exe", "pspasswd.exe", "psexec.c", "psexec.exe", "pslist.exe", "regsize", "pskill.exe", "pkill.exe", "wsmprovhost.exe", "fltmc.exe", "sdbinst.exe"]);
// Merge both lists into one reference list.
let original_file_name_set=array_concat(lolbins,binaries_of_interest);
let allGrandChilderen = materialize(
  DeviceProcessEvents
  | where ingestion_time() >= ago(timeframe)
  | where ActionType =~ "ProcessCreated"
  | where InitiatingProcessParentFileName =~ "sqlservr.exe"
  | where InitiatingProcessCommandLine startswith "\"cmd.exe\" /c"
);
let allSuspiciousHashes = (
  // The reason for looking at grand children of SQL Server instead of children is that xp_cmdshell always runs a command
  // via cmd.exe /c so the child process will always be cmd.exe. We are interested in the grand child process which is the actual command.
  allGrandChilderen
  // FileProfile is case-sensitive and works on lower-case hashes.
  | where not(isempty(SHA1))
  | summarize DeviceCount=dcount(DeviceId) by SHA1
  | top 1000 by DeviceCount asc
  | invoke FileProfile(SHA1, 1000)
  | where not(ProfileAvailability =~ "Error")
  | where coalesce(GlobalPrevalence,default_global_prevalence) < 250
  | where SignatureState != "SignedValid"
);
allGrandChilderen
| where FileName in~ (original_file_name_set) or SHA1 in ((allSuspiciousHashes))
| join kind=leftouter allSuspiciousHashes on SHA1
// Begin environment-specific filter.
// End environment-specific filter.

Stages and Predicates

Parameters

let timeframe = 2*1h;
let default_global_prevalence = 0;
let original_file_name_set = array_concat(lolbins,binaries_of_interest);

Let binding: lolbins

let lolbins = dynamic(["at.exe", "atbroker.exe", "bash.exe", "bitsadmin.exe", "certreq.exe", "certutil.exe", "cmd.exe", "cmdkey.exe", "cmstp.exe", "control.exe", "csc.exe", "cscript.exe", "desktopimgdownldr.exe", "dfsvc.exe", "diantz.exe", "diskshadow.exe", "dnscmd.exe", "esentutl.exe", "eventvwr.exe", "expand.exe", "extexport.exe", "extrac32.exe", "findstr.exe", "forfiles.exe", "ftp.exe", "gfxdownloadwrapper.exe", "gpscript.exe", "hh.exe", "ie4uinit.exe", "ieexec.exe", "ilasm.exe", "infdefaultinstall.exe", "installutil.exe", "jsc.exe", "makecab.exe", "mavinject.exe", "microsoft.workflow.compiler.exe", "mmc.exe", "mpcmdrun.exe", "msbuild.exe", "msconfig.exe", "msdt.exe", "mshta.exe", "msiexec.exe", "netsh.exe", "odbcconf.exe", "pcalua.exe", "pcwrun.exe", "pktmon.exe", "presentationhost.exe", "print.exe", "psr.exe", "rasautou.exe", "reg.exe", "regasm.exe", "regedit.exe", "regini.exe", "register-cimprovider.exe", "regsvcs.exe", "regsvr32.exe", "replace.exe", "rpcping.exe", "rundll32.exe", "runonce.exe", "runscripthelper.exe", "sc.exe", "schtasks.exe", "scriptrunner.exe", "syncappvpublishingserver.exe", "ttdinject.exe", "tttracer.exe", "vbc.exe", "verclsid.exe", "wab.exe", "wmic.exe", "wscript.exe", "wsreset.exe", "xwizard.exe", "agentexecutor.exe", "appvlp.exe", "bginfo.exe", "cdb.exe", "csi.exe", "devtoolslauncher.exe", "dnx.exe", "dotnet.exe", "dxcap.exe", "excel.exe", "mftrace.exe", "msdeploy.exe", "msxsl.exe", "ntdsutil.exe", "powerpnt.exe", "rcsi.exe", "sqldumper.exe", "sqlps.exe", "sqltoolsps.exe", "squirrel.exe", "te.exe", "tracker.exe", "vsjitdebugger.exe", "winword.exe", "wsl.exe", "powershell.exe", "pwsh.exe"]);

Let binding: binaries_of_interest

let binaries_of_interest = dynamic(["net.exe", "net1.exe", "whoami.exe", "ipconfig.exe", "tasklist.exe", "quser.exe", "tracert.exe", "route.exe", "runas.exe", "klist.exe", "wevtutil.exe", "wmiprvse.exe", "powershell.exe", "bash.exe", "qwinsta.exe", "rwinsta.exe", "replace.exe", "findstr.exe", "icacls.exe", "cacls.exe", "xcopy.exe", "robocopy.exe", "takeown.exe", "vssadmin.exe", "nltest.exe", "nltestk.exe", "sctasks.exe", "nbtstat.exe", "nbtinfo.exe", "mofcomp.exe", "nltestrk.exe", "dnscmd.exe", "registercimprovider.exe", "registercimprovider2.exe", "procdump", "ru.exe", "pspasswd.exe", "psexec.c", "psexec.exe", "pslist.exe", "regsize", "pskill.exe", "pkill.exe", "wsmprovhost.exe", "fltmc.exe", "sdbinst.exe"]);

Let binding: allSuspiciousHashes

let allSuspiciousHashes = (
  allGrandChilderen
  | where not(isempty(SHA1))
  | summarize DeviceCount=dcount(DeviceId) by SHA1
  | top 1000 by DeviceCount asc
  | invoke FileProfile(SHA1, 1000)
  | where not(ProfileAvailability =~ "Error")
  | where coalesce(GlobalPrevalence,default_global_prevalence) < 250
  | where SignatureState != "SignedValid"
);

Derived from default_global_prevalence, allGrandChilderen.

The stages below define let allGrandChilderen (the rule's main pipeline source).

Stage 1: source

DeviceProcessEvents

Stage 2: where

| where ingestion_time() >= ago(timeframe)

Stage 3: where

| where ActionType =~ "ProcessCreated"

Stage 4: where

| where InitiatingProcessParentFileName =~ "sqlservr.exe"

Stage 5: where

| where InitiatingProcessCommandLine startswith "\"cmd.exe\" /c"

The stages below run on allGrandChilderen (the outer pipeline).

Stage 6: where

allGrandChilderen
| where FileName in~ (original_file_name_set) or SHA1 in ((allSuspiciousHashes))

Stage 7: join

| join kind=leftouter allSuspiciousHashes on SHA1

Stage 8: summarize

summarize by SHA1

Exclusions

Top-level NOT(...) conjuncts: predicates this rule actively suppresses.

FieldKindExcluded values
ProfileAvailabilityeqError
SHA1is_null(no value, null check)

Indicators

Each row is a field, operator, and value that the rule matches. The corpus column counts how many other rules in the catalog look for the same combination: high numbers point to widely-used, community-vetted indicators. Blank or 1 shows that the indicator is specific to this rule.

FieldKindValues
ActionTypeeq
  • ProcessCreated corpus 10 (kusto 10)
FileNamein
  • original_file_name_set
GlobalPrevalencelt
  • 250 transforms: coalesce_default:0 corpus 4 (kusto 4)
InitiatingProcessCommandLinestarts_with
  • \"cmd.exe\" /c
InitiatingProcessParentFileNameeq
  • sqlservr.exe
SignatureStatene
  • SignedValid transforms: cased

Output fields

Fields the rule emits when it matches. Chronicle authors list these in the outcome block; they appear on the detection and $risk_score drives alerting. Sentinel / Defender XDR rules build them up through project / summarize / extend stages. Sentinel maps these into alert fields via entityMappings and customDetails; Defender XDR custom detections surface them as alert fields directly.

FieldSource
SHA1summarize