Velocon 2022 - Notebook and VQL: Data munging your way to victory!

enrichment.vql

-- add API key here
LET APIKey = 'REDACTED'

-- firstly Materialize all entries for performance
LET all_entries <= SELECT 
            Fqdn,ClientId,
            `Entry Location` as EntryLocation,
            Entry,Enabled,Profile,Description,Company,
            `Image Path` as ImagePath,
            Version,
            `Launch String` as LaunchString,
            `SHA-1` as SHA1
FROM source(artifact="Windows.Sysinternals.Autoruns")

-- process unique hashes once only, output only Malicious hashes
LET hits = SELECT * FROM foreach(
        row={
            SELECT SHA1 FROM all_entries WHERE SHA1 GROUP BY SHA1
        }, query={
            SELECT FirstScan,LastScan,Hash,HashType,
                ScannerCount,ScannerMatch,ScannerPercent,Status,
                ThreatLevel,ThreatName,TrustFactor,Classification	
            FROM Artifact.Server.Enrichment.ReversingLabs(
                Hash=SHA1,ZionKey=APIKey) 
            WHERE Status = 'MALICIOUS'
}, workers=100)

-- output rows 
SELECT * FROM foreach(row=hits,
    query={
        SELECT Fqdn,ClientId as _ClientId,EntryLocation,Entry,Enabled,Profile,
            dict(ImagePath=ImagePath,Company=Company,
                Description=Description,Version=Version
                    ) as Image,
            dict(FirstScan=FirstScan,LastScan=LastScan,SHA1=Hash,
                ScannerCount=ScannerCount,ScannerMatch=ScannerMatch,
                ScannerPercent=ScannerPercent,Status=Status,
                ThreatLevel=ThreatLevel,ThreatName=ThreatName,
                TrustFactor=TrustFactor,Classification=Classification
                    ) as ReversingLabs
        FROM all_entries
        WHERE SHA1 = Hash
    })

etw_monitoring.vql

/*
## Example Velociraptor GUI mode notebook VQL to dev ETW monitoring
Powershell ETW {a0c1853b-5c40-4b15-8766-3cf1c58f985a}<br> 
<br>
1. Create seperate VQL pane and run monitoring<br>
SELECT * FROM watch_etw(guid="{a0c1853b-5c40-4b15-8766-3cf1c58f985a}")<br>

2. Generate event and create cell from cell<br>

3. Paste VQL, replace notebook source details.

*/

-- Add target string to search in EventData
LET TargetString = 'Get-Process'

LET events = SELECT timestamp(epoch=timestamp(string=System.TimeStamp).unix) as EventTime,
        System.ID as EventID,
        System.ProcessID as ProcessID,
        get(member="EventData") AS EventData
    FROM source(
        notebook_id="N.CAUKI1UUN64EA",
        notebook_cell_id="NC.CCGKS14NS0CUS")
    WHERE EventID in ( 4104, 7937 ) 
        AND ( EventData.ScriptBlockText OR EventData.Payload =~ '^Command .+ is Started\.\r\n$' )
        AND format(format='%s',args=EventData) =~ TargetString

SELECT EventTime, ProcessID,EventID,
        if(condition= EventID=4104,
            then= EventData.ScriptBlockText,
            else= regex_replace(
                source=EventData.Payload,
                re='^Command | is Started\.\r\n$',
                    replace=''
            )) as Payload,
        if(condition= EventID=4104,
            then= EventData,
            else= dict(Payload=EventData.Payload,
                ContextInfo=parse_string_with_regex(string=EventData.ContextInfo,
                    regex=[
                        'Severity = (?P<Severity>[^\\r]*)',
                        'Host Name = (?P<HostName>[^\\r]*)',
                        'Host Version = (?P<HostVersion>[^\\r]*)',
                        'Host ID = (?P<HostID>[^\\r]*)',
                        'Host Application = (?P<HostApplication>[^\\r]*)',
                        'Engine Version = (?P<EngineVersion>[^\\r]*)',
                        'Runspace ID = (?P<RunspaceID>[^\\r]*)',
                        'Pipeline ID = (?P<PipelineID>[^\\r]*)',
                        'Command Name= (?P<CommandName>[^\\r]*)',
                        'CommandType = (?P<CommandType>[^\\r]*)',
                        'Script Name = (?P<ScriptName>[^\\r]*)',
                        'Command Path = (?P<CommandPath>[^\\r]*)',
                        'Sequence Number = (?P<SequenceNumber>[^\\r]*)',
                        'User = (?P<User>[^\\r]*)',
                        'Connected User = (?P<ConnectedUser>[^\\r]*)',
                        'Shell ID = (?P<ShellID>[^\\r]*)'
                    ]),
            UserData=EventData.UserData)) as EventData
FROM events

multi_message_decode.vql

/*
# Example Velociraptor VQL to decode multi event log message payload
*/

-- Set Targets
LET TargetHost = 'Re-Dev'
Let TargetScriptBlockId = '79a919da-432b-4960-ad6f-9e22c5552e85'

-- Firstly Select all messages
LET payload = SELECT EventTime,Computer,
        EventData.ScriptBlockId as ScriptBlockId,
        EventData.MessageNumber as MessageNumber,
        EventData.MessageTotal as MessageTotal,
        EventData.ScriptBlockText as ScriptBlockText
    FROM source(artifact="Windows.EventLogs.EvtxHunter")
    WHERE TRUE 
        AND Computer =~ TargetHost
        AND ScriptBlockId = TargetScriptBlockId

-- dynamic function to show function (remove large base64 blob)
LET Function(data) = regex_replace(source=data,
        re='''::FromBase64String\(["'](?P<Base64>[^"']+)["']''',
        replace='::FromBase64String\(*******BASE64*******')

-- dynamic function to extract base64 blob        
LET Base64(data) = parse_string_with_regex(string=data,
        regex='''::FromBase64String\(["'](?P<Base64>[^"']+)''').Base64

-- extract regex, functions and payload, also add event metadata for validation
LET final = SELECT 
    payload.EventTime[0] as EventTime,
    payload.Computer[0] as Computer, 
    payload.ScriptBlockId[0] as ScriptBlockId,
    payload.MessageTotal[0] as MessageTotal,
    join(array=payload.MessageNumber,sep='+') as Messages,
    Function(data = join(array=payload.ScriptBlockText,sep='')) as Function,
    Base64(data = join(array=payload.ScriptBlockText,sep='')) as Base64,
    xor(key=format(format='%c',args=35),string=
        base64decode(string=Base64(data=
            join(array=payload.ScriptBlockText,sep='')))) as Payload
FROM scope()

-- finally output fields for presentation
SELECT
    dict(
        EventTime=EventTime,
        Computer=Computer,
        ScriptBlockId=ScriptBlockId, 
        MessageTotal=MessageTotal,
        Messages=Messages
            ) as EventDetails,
    dict(
        Function=Function,
        Base64=Base64,
        Payload=Payload
            ) as Payload,
    {
        SELECT * FROM Artifact.Windows.Carving.CobaltStrike(TargetBytes=Payload)
    } As CobaltStrike
FROM final