scan

This is example from WPNinja Summit 2022 session "Throwing KQL like a shuriken". Presented by Gianni Castaldi and Alex Verboon

Example 1

// Find Evil
search "ntdsutil"

// Find Evil
DeviceImageLoadEvents
| where InitiatingProcessFileName =~ "ntdsutil.exe" 

// Step one samlib.dll
DeviceImageLoadEvents
| where InitiatingProcessFileName =~ "ntdsutil.exe" 
| sort by Timestamp asc
| scan with_match_id=funnel_id declare(Step:string, Delta:timespan) with
(
    step Authentication: InitiatingProcessFileName =~ "ntdsutil.exe" 
        and FileName =~ "samlib.dll" 
            => Step = "Authenticated";
)

// Step two add vss_ps.dll
DeviceImageLoadEvents
| where InitiatingProcessFileName =~ "ntdsutil.exe" 
| sort by Timestamp asc
| scan with_match_id=funnel_id declare(Step:string, Delta:timespan) with
(
    step Authentication: InitiatingProcessFileName =~ "ntdsutil.exe" 
        and FileName =~ "samlib.dll" 
            => Step = "Authenticated";
    step NTDSExport: InitiatingProcessFileName =~ "ntdsutil.exe" 
        and FileName =~ "vss_ps.dll" 
        and Authentication.Timestamp > 10m 
            => Step = "NTDS export"
            , Delta = Timestamp - Authentication.Timestamp;
)
| project-reorder Timestamp, DeviceId, DeviceName, funnel_id, FileName, Step

// Step three finalize all
DeviceImageLoadEvents
| where InitiatingProcessFileName =~ "ntdsutil.exe" 
| sort by Timestamp asc
| scan with_match_id=funnel_id declare(Step:string, Delta:timespan) with
(
    step Authentication: InitiatingProcessFileName =~ "ntdsutil.exe" 
        and FileName =~ "samlib.dll" 
            => Step = "Authenticated";
    step NTDSExport: InitiatingProcessFileName =~ "ntdsutil.exe" 
        and FileName =~ "vss_ps.dll" 
        and Authentication.Timestamp > 10m 
            => Step = "NTDS export"
            , Delta = Timestamp - Authentication.Timestamp;
)
| where Step == "NTDS export"
| project-reorder Timestamp, DeviceId, DeviceName, funnel_id, FileName, Step

Example 2

let SuspiciousFiles = dynamic([
    "dir.exe"
    , "ipconfig.exe"
    , "systeminfo.exe"
    , "ping.exe"
    , "type.exe"
    , "net.exe"
    , "dsquery.exe"
    , "csvde.exe"
    , "nbtstat.exe"
    , "nltest.exe"
    , "ntdsutil.exe"
    , "adfind.exe"
    , "nslookup.exe"
    , "procdump.exe"
    , "whoami.exe"
    , "wmic.exe"
    , "mimikatz.exe"
    , "tasklist.exe"
    , "rubeus.exe"]);
DeviceProcessEvents
| where FileName has_any(SuspiciousFiles)

let SuspiciousFiles = dynamic(["dir.exe", "ipconfig.exe", "systeminfo.exe", "ping.exe", "type.exe", "net.exe"
    , "dsquery.exe", "csvde.exe", "nbtstat.exe", "nltest.exe", "ntdsutil.exe", "adfind.exe", "nslookup.exe"
    , "procdump.exe", "whoami.exe", "wmic.exe", "mimikatz.exe", "tasklist.exe", "rubeus.exe"]);
DeviceProcessEvents
| where FileName has_any(SuspiciousFiles)
| project Timestamp, DeviceId, FileName, ProcessCommandLine, ReportId
| sort by DeviceId asc, Timestamp asc
| scan with_match_id=funnel_id declare(Step:int, Delta:timespan, Duration:timespan, Files:dynamic, CommandLines:dynamic) with
(
    step s1: FileName in(SuspiciousFiles) 
        => Step = 1, Delta = timespan(0)
        , Duration = timespan(0)
        , Files = pack_array(FileName)
        , CommandLines = pack_array(ProcessCommandLine);
)
| summarize arg_max(Timestamp, ReportId, *) by funnel_id, DeviceId, Step

let TimePeriod = 5m;
let Threshold = 3;
let SuspiciousFiles = dynamic(["dir.exe", "ipconfig.exe", "systeminfo.exe", "ping.exe", "type.exe", "net.exe"
    , "dsquery.exe", "csvde.exe", "nbtstat.exe", "nltest.exe", "ntdsutil.exe", "adfind.exe", "nslookup.exe"
    , "procdump.exe", "whoami.exe", "wmic.exe", "mimikatz.exe", "tasklist.exe", "rubeus.exe"]);
DeviceProcessEvents
| where FileName has_any(SuspiciousFiles)
| project Timestamp, DeviceId, FileName, ProcessCommandLine, ReportId
| sort by DeviceId asc, Timestamp asc
| scan with_match_id=funnel_id declare(Step:int, Delta:timespan, Duration:timespan, Files:dynamic, CommandLines:dynamic) with
(
    step s1: FileName in(SuspiciousFiles) 
        => Step = 1, Delta = timespan(0)
        , Duration = timespan(0)
        , Files = pack_array(FileName)
        , CommandLines = pack_array(ProcessCommandLine);
    step s2: FileName in(SuspiciousFiles) 
        and Timestamp - s1.Timestamp <= TimePeriod 
        and DeviceId == s1.DeviceId 
        and ProcessCommandLine != s1.ProcessCommandLine => Step = 2
        , Delta = Timestamp - s1.Timestamp
        , Duration = Timestamp - s1.Timestamp
        , Files = pack_array(s1.FileName, FileName)
        , CommandLines = pack_array(s1.ProcessCommandLine, ProcessCommandLine);
    step s3: FileName in(SuspiciousFiles) and Timestamp - s2.Timestamp <= TimePeriod and DeviceId == s2.DeviceId and ProcessCommandLine != s1.ProcessCommandLine and ProcessCommandLine != s2.ProcessCommandLine => Step = 3, Delta = Timestamp - s2.Timestamp, Duration = Timestamp - s1.Timestamp, Files = pack_array(s1.FileName, s2.FileName, FileName), CommandLines = pack_array(s1.ProcessCommandLine, s2.ProcessCommandLine, ProcessCommandLine);
    step s4: FileName in(SuspiciousFiles) and Timestamp - s3.Timestamp <= TimePeriod and DeviceId == s3.DeviceId and ProcessCommandLine != s1.ProcessCommandLine and ProcessCommandLine != s2.ProcessCommandLine and ProcessCommandLine != s3.ProcessCommandLine => Step = 4, Delta = Timestamp - s3.Timestamp, Duration = Timestamp - s1.Timestamp, Files = pack_array(s1.FileName, s2.FileName, s3.FileName, FileName), CommandLines = pack_array(s1.ProcessCommandLine, s2.ProcessCommandLine, s3.ProcessCommandLine, ProcessCommandLine);
    step s5: FileName in(SuspiciousFiles) and Timestamp - s4.Timestamp <= TimePeriod and DeviceId == s4.DeviceId and ProcessCommandLine != s1.ProcessCommandLine and ProcessCommandLine != s2.ProcessCommandLine and ProcessCommandLine != s3.ProcessCommandLine and ProcessCommandLine != s4.ProcessCommandLine => Step = 5, Delta = Timestamp - s4.Timestamp, Duration = Timestamp - s1.Timestamp, Files = pack_array(s1.FileName, s2.FileName, s3.FileName, s4.FileName, FileName), CommandLines = pack_array(s1.ProcessCommandLine, s2.ProcessCommandLine, s3.ProcessCommandLine, s4.ProcessCommandLine, ProcessCommandLine);
)
| summarize arg_max(Timestamp, ReportId, *) by funnel_id, DeviceId, Step

let TimePeriod = 5m;
let Threshold = 3;
let SuspiciousFiles = dynamic(["dir.exe"
    , "ipconfig.exe"
    , "systeminfo.exe"
    , "ping.exe"
    , "type.exe"
    , "net.exe"
    , "dsquery.exe"
    , "csvde.exe"
    , "nbtstat.exe"
    , "nltest.exe"
    , "ntdsutil.exe"
    , "adfind.exe"
    , "nslookup.exe"
    , "procdump.exe"
    , "whoami.exe"
    , "wmic.exe"
    , "mimikatz.exe"
    , "tasklist.exe"
    , "rubeus.exe"
]);
let ExcludedCommands = dynamic(["wuauserv"
]);
DeviceProcessEvents
| where FileName has_any(SuspiciousFiles)
| where not(ProcessCommandLine has_any(ExcludedCommands))
| where InitiatingProcessParentFileName != "MsSense.exe" and InitiatingProcessParentFileName != "SenseIR.exe" and InitiatingProcessFileName != "MsSense.exe"
| project Timestamp, DeviceId, FileName, ProcessCommandLine, ReportId
| sort by DeviceId asc, Timestamp asc
| scan with_match_id=funnel_id declare(Step:int, Delta:timespan, Duration:timespan, Files:dynamic, CommandLines:dynamic) with
(
    step s1: FileName in(SuspiciousFiles) => Step = 1, Delta = timespan(0), Duration = timespan(0), Files = pack_array(FileName), CommandLines = pack_array(ProcessCommandLine);
    step s2: FileName in(SuspiciousFiles) and Timestamp - s1.Timestamp <= TimePeriod and DeviceId == s1.DeviceId and ProcessCommandLine != s1.ProcessCommandLine => Step = 2, Delta = Timestamp - s1.Timestamp, Duration = Timestamp - s1.Timestamp, Files = pack_array(s1.FileName, FileName), CommandLines = pack_array(s1.ProcessCommandLine, ProcessCommandLine);
    step s3: FileName in(SuspiciousFiles) and Timestamp - s2.Timestamp <= TimePeriod and DeviceId == s2.DeviceId and ProcessCommandLine != s1.ProcessCommandLine and ProcessCommandLine != s2.ProcessCommandLine => Step = 3, Delta = Timestamp - s2.Timestamp, Duration = Timestamp - s1.Timestamp, Files = pack_array(s1.FileName, s2.FileName, FileName), CommandLines = pack_array(s1.ProcessCommandLine, s2.ProcessCommandLine, ProcessCommandLine);
    step s4: FileName in(SuspiciousFiles) and Timestamp - s3.Timestamp <= TimePeriod and DeviceId == s3.DeviceId and ProcessCommandLine != s1.ProcessCommandLine and ProcessCommandLine != s2.ProcessCommandLine and ProcessCommandLine != s3.ProcessCommandLine => Step = 4, Delta = Timestamp - s3.Timestamp, Duration = Timestamp - s1.Timestamp, Files = pack_array(s1.FileName, s2.FileName, s3.FileName, FileName), CommandLines = pack_array(s1.ProcessCommandLine, s2.ProcessCommandLine, s3.ProcessCommandLine, ProcessCommandLine);
    step s5: FileName in(SuspiciousFiles) and Timestamp - s4.Timestamp <= TimePeriod and DeviceId == s4.DeviceId and ProcessCommandLine != s1.ProcessCommandLine and ProcessCommandLine != s2.ProcessCommandLine and ProcessCommandLine != s3.ProcessCommandLine and ProcessCommandLine != s4.ProcessCommandLine => Step = 5, Delta = Timestamp - s4.Timestamp, Duration = Timestamp - s1.Timestamp, Files = pack_array(s1.FileName, s2.FileName, s3.FileName, s4.FileName, FileName), CommandLines = pack_array(s1.ProcessCommandLine, s2.ProcessCommandLine, s3.ProcessCommandLine, s4.ProcessCommandLine, ProcessCommandLine);
)
| summarize arg_max(Timestamp, ReportId, *) by funnel_id, DeviceId, Step
| where Step >= Threshold