VQL Fundamentals :: Velociraptor - Digging deeper! (2024)

VQL is central to the design and functionality of Velociraptor, and a solid grasp of VQL is critical to understanding and extending Velociraptor.

Why a new query language?

The need for a query language arose from our experience of previousDigital Forensic and Incident Response (DFIR) frameworks. Endpoint analysis tools must beflexible enough to adapt to new indicators of compromise (IOCs) and protect against newthreats. While it is always possible to develop new capability incode, it’s not always easy or quick to deploy a new version.

A query language can accelerate the time it takes to discover an IOC, design a rule to detect it, and then deploy the detection at scale acrossa large number of hosts. Using VQL, a DFIR investigator canlearn of a new type of indicator, write relevant VQL queries,package them in an artifact, and hunt for the artifact across theentire deployment in a matter of minutes.

Additionally, VQL artifacts can be shared with the community andfacilitate a DFIR-specific knowledge exchange ofindicators and detection techniques.

Running VQL queries - Notebooks

When learning VQL, we recommend practicing in an environment where you can easily debug, iterate, and interactivelytest each query.

You can read more about notebooks here. For the purposes of this documentation, we will assume you created a notebook and are typing VQLinto the cell.

Basic Syntax

VQL’s syntax is heavily inspired by SQL. It uses the same basicSELECT .. FROM .. WHERE sentence structure, but does not include themore complex SQL syntax, such as JOIN or HAVING. In VQL, similarfunctionality is provided through plugins, which keeps the syntaxsimple and concise.

Whitespace

VQL does not place any restrictions on the use of whitespace in thequery body. We generally prefer queries that are well indented becausethey are more readable and look better but this is not arequirement. Unlike SQL, VQL does not require or allow a semicolon ;at the end of statements.

The following two queries are equivalent

-- This query is all on the same line - not very readable but valid.LET X = SELECT * FROM info() SELECT * FROM X-- We prefer well indented queries but VQL does not mind.LET X= SELECT * FROM info()SELECT * FROM X

Let’s consider the basic syntax of a VQL query.

The query starts with a SELECT keyword, followed by a list of Column Selectors then the FROM keyword and a VQL Plugin potentiallytaking arguments. Finally we have a WHERE keyword followed by afilter expression.

Plugins

While VQL syntax is similar to SQL, SQL was designed to work on statictables in a database. In VQL, the data sources are not actually statictables on disk - they are provided by code that runs to generaterows. VQL Plugins produce rows and are positioned after theFROM clause.

Like all code, VQL plugins use parameters to customize and controltheir operations. VQL Syntax requires all arguments to be provided byname (these are called keyword arguments). Depending on the specificplugins, some arguments are required while some are optional.

You can type ? in the Notebook interface to view alist of possible completions for a keyword. Completions are context sensitive. For example, since plugins must follow the FROM keyword, any suggestionsafter the FROM keyword will be for VQL plugins. Typing ? insidea plugin arguments list shows the possible arguments, theirtype, and if they are required or optional.

Life of a query

In order to understand how VQL works, let’s follow a single row through the query.

1. Velociraptor’s VQL engine will call the plugin and passany relevant arguments to it. The plugin will then generate one ormore rows and send a row at a time into the query for furtherprocessing.

2. The column expression in the query receives therow. However, instead of evaluating the column expressionimmediately, VQL wraps the column expression in a Lazy Evaluator. Lazy evaluators allow the actual evaluation of thecolumn expression to be delayed until a later time.

3. Next, VQL takes the lazy evaluator and uses them to evaluate thefilter condition, which will determine if the row is to beeliminated or passed on.

4. In this example, the filter condition (X=1) must evaluate thevalue of X and therefore will trigger the Lazy Evaluator.

5. Assuming X is indeed 1, the filter will return TRUE and the rowwill be emitted from the query.

Lazy Evaluation

In the previous example, the VQL engine goes through signficant effort to postpone the evaluation as much aspossible. Delaying an evaluation is a recurring theme in VQL and it saves Velociraptor from performing unnecessary work, like evaluating acolumn value if the entire row will be filtered out.

Understanding lazy evaluation is critical to writing efficient VQLqueries. Let’s examine how this work using a series ofexperiments. For these experiments we will use the log() VQLfunction, which simply produces a log message when evaluated.

-- Case 1: One row and one log messageSELECT OS, log(message="I Ran!") AS LogFROM info()-- Case 2: No rows and no log messagesSELECT OS, log(message="I Ran!") AS LogFROM info()WHERE OS = "Unknown"-- Case 3: Log message but no rowsSELECT OS, log(message="I Ran!") AS LogFROM info()WHERE Log AND OS = "Unknown"-- Case 4: No rows and no log messagesSELECT OS, log(message="I Ran!") AS LogFROM info()WHERE OS = "Unknown" AND Log

In Case 1, a single row will be emitted by the query and the associated log function will be evaluated, producing a log message.

Case 2 adds a condition which should eliminate the row. Because therow is eliminated VQL can skip evaluation of the log()function. No log message will be produced.

Cases 3 and 4 illustrate VQL’s evaluation order of AND terms - fromleft to right with an early exit.

We can use this property to control when expensive functions areevaluated e.g. hash() or upload().

What is a Scope?

Scope is a concept common in many languages, and it is also central inVQL. A scope is a bag of names that is used to resolve symbols,functions and plugins in the query.

For example, consider the query

SELECT OS FROM info()

VQL sees “info” as a plugin and looks in the scope to get the realimplementation of the plugin.

Scopes can be nested, which means that in different parts of the query anew child scope is used to evaluate the query. The child scope isconstructed by layering a new set of names over the top of theprevious set. When VQL tries to resolve a name, it looks up the scopein reverse order going from layer to layer until the symbol isresolved.

Take the following query for example,

VQL evaluates the info() plugin, which emits a single row. Then VQLcreates a child scope, with the row at the bottom level. When VQL triesto resolve the symbol OS from the column expression, it examines thescope stack in reverse, checking if the symbol OS exists in thelower layer. If not, VQL checks the next layer, and so on.

SELECT Pid, Name, { SELECT Name FROM pslist(pid=Ppid)} AS ParentNameFROM pslist()

String constants

Strings denoted by " or ' can escape special characters using the\. For example, "\n" means a new line. This is useful but italso means that backslashes need to be escaped. This is sometimesinconvenient, especially when dealing with Windows paths (thatcontains a lot of backslashes).

Therefore, Velociraptor also offers a multi-line raw string which isdenoted by ''' (three single quotes). Within this type of string noescaping is possible, and the all characters are treated literally -including new lines. You can use ''' to denote multi line strings.

Identifiers with spaces

In VQL an Identifier is the name of a column, member of a dict or akeyword name. Sometimes identifiers contain special characters such asspace or . which make it difficult to specify them without havingVQL get confused by these extra characters.

In this case it is possible to enclose the identifier name with backticks (`).

In the following example, the query specifies keywords with spaces tothe dict() plugin in order to create a dict with keys containing spaces.

The query then continues to extract the value from this key byenclosing the name of the key using backticks.

LET X = SELECT dict(`A key with spaces`="String value") AS DictFROM scope()SELECT Dict, Dict.`A key with spaces` FROM X

Subqueries

VQL Subqueries can be specified as a column expression or as anarguments. Subqueries are delimited by { and }. Subqueries arealso lazily evaluated, and will only be evaluated when necessary.

The following example demonstrates subqueries inside plugin args. Theif() plugin will evaluate the then or the else query dependingon the condition value (in this example when X has the value 1).

SELECT * FROM if(condition=X=1,then={ SELECT * FROM ...},else={ SELECT * FROM ...})

Subqueries as columns

You can use a subquery as a column which will cause it to be evaluatedfor each row (in this way it is similar to the foreach() plugin).

Since subqueries are always an array of dictionaries, the output ifoften difficult to read when the subquery returns many rows orcolumns. As a special case, VQL will simplify subqueries:

1. If the subquery returns one row and has several columns, VQL willput a single dictionary of data in the column.
2. If the subquery returns one row and a single column, the value isexpanded into the cell.

These heuristics are helpful when constructing subqueries to enrichcolumns. If you wish to preserve the array of dicts you can use a VQLfunction instead.

Here is an example to demonstrate:

LET Foo = SELECT "Hello" AS Greeting FROM scope()SELECT { SELECT "Hello" AS Greeting FROM scope() } AS X, { SELECT "Hello" AS Greeting, "Goodbye" AS Farewell FROM scope() } AS Y, Foo AS ZFROM scope()

In the above query - X is a subquery with a single row and a singlecolumn, therefore VQL will simplify the column X to contain "Hello"The second query contains two columns so VQL will simplify it into adict.

Finally to get the full unsimplified content, a VQL stored query canbe used. This will result in an array of one dict, containing a singlecolumn Greeting with value of Hello

Arrays

An array may be defined either by ( and ) or [ and ]. Since itcan be confusing to tell regular parenthesis from an array with asingle element, VQL also allows a trailing comma to indicate a singleelement array. For example (1, ) means an array with one member,whereas (1) means a single value of 1.

The scope() plugin

VQL is strict about the syntax of a VQL statement. Each statement musthave a plugin specified, however sometimes we dont really want toselect from any plugin at all.

The default noop plugin is called scope() and simply returns thecurrent scope as a single row. If you even need to write a query butdo not want to actually run a plugin, use scope() as a noopplugin. For example

-- Returns one row with Value=4SELECT 2 + 2 AS ValueFROM scope()

The Foreach plugin

VQL is modeled on basic SQL since SQL is a familiar language for newusers to pick up. However, SQL quickly becomes more complex with verysubtle syntax that only experienced SQL users use regularly. One ofthe more complex aspects of SQL is the JOIN operator which typicallycomes in multiple flavors with subtle differences (INNER JOIN, OUTERJOIN, CROSS JOIN etc).

While these make sense for SQL since they affect the way indexes areused in the query, VQL does not have table indexes, nor does it haveany tables. Therefore the JOIN operator is meaningless forVelociraptor. To keep VQL simple and accessible, we specifically didnot implement a JOIN operator. For a more detailed discussion of theJOIN operator see emulating join in VQL

Instead of a JOIN operator, VQL has the foreach() plugin, which isprobably the most commonly used plugin in VQL queries. The foreach()plugin takes two arguments:

1. The row parameter is a subquery that provides rows

2. The query parameter is a subquery that will be evaluated on asubscope containing each row that is emitted by the row argument.

Consider the following query:

SELECT * FROM foreach( row={ SELECT Exe FROM pslist(pid=getpid()) }, query={ SELECT ModTime, Size, FullPath FROM stat(filename=Exe) })

Note how Exe is resolved from the produced row since the query isevaluated within the nested scope.

Foreach is useful when we want to run a query on the output of anotherquery.

Foreach on steroids

Normally foreach iterates over each row one at a time. Theforeach() plugin also takes the workers parameter. If this is largerthan 1, foreach() will use multiple threads and evaluate the queryquery in each worker thread. This allows the query to evaluate values in parallel.

For example, the following query retrieves all thefiles in the System32 directory and calculates their hash.

SELECT FullPath, hash(path=FullPath)FROM glob(globs="C:/Windows/system32/*")WHERE NOT IsDir

As each row is emitted from the glob() plugin with a filename of afile, the hash() function is evaluated and the hash iscalculated.

However this is linear, since each hash is calculated before the nexthash is started - hence only one hash is calculated at once.

This example is very suitable for parallelization because globbing forall files is quite fast, but hashing thefiles can be slow. If we delegate the hashing to multiple threads, wecan make more effective use of the CPU.

SELECT * FROM foreach(row={ SELECT FullPath FROM glob(globs="C:/Windows/system32/*") WHERE NOT IsDir}, query={ SELECT FullPath, hash(path=FullPath) FROM scope()}, worker=10)

Foreach and deconstructing a dict

Deconstructing a dict means to take that dict and create a column foreach field of that dict. Consider the following query:

LET Lines = '''Foo BarHello WorldHi There'''LET all_lines = SELECT grok(grok="%{NOTSPACE:First} %{NOTSPACE:Second}", data=Line) AS ParsedFROM parse_lines(accessor="data", filename=Lines)SELECT * FROM foreach(row=all_lines, column="Parsed")

This query reads some lines (for example log lines) and applies a grokexpression to parse each line. The grok function will produce a dictafter parsing the line with fields determined by the grok expression.

The all_lines query will have one column called “Parsed” containinga dict with two fields (First and Second). Using the columnparameter to the foreach() plugin, foreach will use the value in thatcolumn as a row, deconstructing the dict into a table containing theFirst and Second column.

LET expressions

We know that subqueries can be used in various parts ofthe query, such as in a column specifier or as an argument to aplugin. While subqueries are convenient, they can become unwieldy whennested too deeply. VQL offers an alternative to subqueries calledStored Queries.

A stored query is a lazy evaluator of a query that we can store inthe scope. Wherever the stored query is used it will be evaluated ondemand. Consider the example below, where for each process, weevaluate the stat() plugin on the executable to check themodification time of the executable file.

LET myprocess = SELECT Exe FROM pslist()LET mystat = SELECT ModTime, Size, FullPath FROM stat(filename=Exe)SELECT * FROM foreach(row=myprocess, query=mystat)

LET expressions are lazy

We have previously seen VQL goes out of its way to do as little workas possible.

Consider the following query

LET myhashes = SELECT FullPath, hash(path=FullPath)FROM glob(globs="C:/Windows/system32/*")SELECT * FROM myhashesLIMIT 5

The myhashes stored query hashes all files in System32 (manythousands of files). However, this query is used in a second querywith a LIMIT clause.

When the query emits 5 rows in total, the entire query is cancelled(since we do not need any more data) which in turn aborts themyhashes query. Therefore, VQL is able to exit early from any querywithout having to wait for the query to complete.

This is possible because VQL queries are asynchronous - we donot calculate the entire result set of myhashes before usingmyhashes in another query, we simply pass the query itself andforward each row as needed.

Materialized LET expressions

A stored query does not in itself evaluate thequery. Instead the query will beevaluated whenever it is referenced.

Sometimes this is not what we want to do. For example consider a querywhich takes a few seconds to run, but its output is not expected tochange quickly. In that case, we actually want to cache the results ofthe query in memory and simply access it as an array.

Expanding a query into an array in memory is termed Materializingthe query.

For example, consider the following query that lists all sockets onthe machine, and attempts to resolve the process ID to a process nameusing the pslist() plugin.

LET process_lookup = SELECT Pid AS ProcessPid, Name FROM pslist()SELECT Laddr, Status, Pid, { SELECT Name FROM process_lookup WHERE Pid = ProcessPid} AS ProcessNameFROM netstat()

This query will be very slow because the process_lookup stored querywill be re-evaluated for each row returned from netstat (that is, for eachsocket).

The process listing will not likely change during the few seconds it takes the query to run.It would be more efficient to have the process listing cached in memoryfor the entire length of the query.

We recommend that you Materialize the query:

LET process_lookup <= SELECT Pid AS ProcessPid, Name FROM pslist()SELECT Laddr, Status, Pid, { SELECT Name FROM process_lookup WHERE Pid = ProcessPid} AS ProcessNameFROM netstat()

The difference between this query and the previous one is thatthe LET clause uses <= instead of =. The <= is the materializeoperator. It tells VQL to expand the query in place into an arraywhich is then assigned to the variable process_lookup.

Subsequent accesses to process_lookup simply access an in-memoryarray of pid and name for all processes and do not need to runpslist() again.

Local functions

LET expressions may store queries into a variable,and have the queries evaluated in a subscope at the point of use.

A LET expression can also declare explicit passing ofvariables.

Consider the following example which is identical to the exampleabove:

LET myprocess = SELECT Exe FROM pslist()LET mystat(Exe) = SELECT ModTime, Size, FullPath FROM stat(filename=Exe)SELECT * FROM foreach(row=myprocess, query={ SELECT * FROM mystat(Exe=Exe)})

This time mystat is declares as a VQL Local Plugin thattakes arguments. Therefore we now pass it an parameter explicitly andit behaves as a plugin.

Similarly we can define a VQL Local Function.

LET MyFunc(X) = X + 5-- Return 11SELECT MyFunc(X=6) FROM scope()

VQL Operators

In VQL an operator represents an operation to be taken onoperands. Unlike SQL, VQL keeps the number of operators down,preferring to use VQL functions over introducing new operators.

The following operators are available. Most operators apply to twooperands, one on the left and one on the right (so in the expression1 + 2 we say that 1 is the Left Hand Side (LHS), 2 is the RightHand Side (RHS) and + is the operator.

Protocols

When VQL encounters an operator it needs to decide how to actuallyevaluate the operator. This depends on what types the LHS and RHSoperands actually are. The way in which operators interact with thetypes of operands is called a protocol.

Generally VQL does the expected thing but it is valuable to understandwhich protocol will be chosen in specific cases.

Example - Regex operator

For example consider the following query

LET MyArray = ("X", "XY", "Y")LET MyValue = "X"LET MyInteger = 5SELECT MyArray =~ "X", MyValue =~ "X", MyInteger =~ "5"FROM scope()

In the first case the regex operator is applied to an array so theexpression is true if any member of the array matches the regularexpression.

The second case applied the regex to a string, so it is true if thestring matches.

Finally in the last case, the regex is applied to an integer. It makesno sense to apply a regular expression to an integer and so VQLreturns FALSE.

Example - Associative operator applied on a stored query

The Associative operator is denoted by . and accesses a field froman object or dict. One of the interesting protocols of the .operator is when it is applied to a query or a list.

In the following example, I define a stored query that calls theGeneric.Utils.FetchBinary artifact (This artifact fetches the namedbinary):

LET binary = SELECT FullPath FROM Artifact.Generic.Utils.FetchBinary(ToolName="ToolName")

Although a query defined via the LET keyword does not actually runthe query immediately (it is a lazy operator), we can think of thevariable binary as containing an array of dictionaries(e.g. [{"FullPath": "C:\Windows\Temp\binary.exe"}]).

If we now apply the associative operator . to the variable binary,the operator will convert the array into another array, where eachmember is extracted for example binary.FullPath is["C:\Windows\Temp\binary.exe"]. To access the name of the binary wecan then index the first element from the array.

SELECT * FROM execve(argv=[binary.FullPath[0], "-flag"])

LET MFT = SELECT * FROM Artifact.Windows.NTFS.MFT()SELECT MFT.FullPath FROM scope()

VQL control structures

Let’s summarizes some of the more frequent VQL control structures.

We already met with the foreach() plugin before. The row parametercan also receive any iterable type (like an array).

Looping over rows

VQL does not have a JOIN operator - we use the foreach plugin toiterate over the results of one query and apply a second query on it.

SELECT * FROM foreach( row={ <sub query goes here> }, query={ <sub query goes here >})

Looping over arrays

Sometimes arrays are present in column data. We can iterate over theseusing the foreach plugin.

SELECT * FROM foreach( row=<An iterable type>, query={ <sub query goes here >})

If row is an array, the value will be assigned to _value as a special placeholder.

Conditional: if plugin and function

The if() plugin and function allows branching in VQL.

SELECT * FROM if( condition=<sub query or value>, then={ <sub query goes here >}, else={ <sub query goes here >})

If the condition is a query it is true if it returns any rows. Next, we’llevaluate the then subquery or the else subquery. Note that as usual,VQL is lazy and will not evaluate the unused query or expression.

Conditional: switch plugin

The switch() plugin and function allows multiple branching in VQL.

SELECT * FROM switch( a={ <sub query >}, b={ <sub query >}, c={ <sub query >})

Evaluate all subqueries in order and when any of them returns any rowswe stop evaluation the rest of the queries.

As usual VQL is lazy - this means that branches that are not taken areessentially free!

Conditional: chain plugin

The chain() plugin allows multiple queries to be combined.

SELECT * FROM chain( a={ <sub query >}, b={ <sub query >}, c={ <sub query >})

Evaluate all subqueries in order and append all the rows together.

Group by clause

A common need in VQL is to use the GROUP BY clause to stack all rowswhich have the same value, but what exactly does the GROUP BY clausedo?

As the name suggests, GROUP BY splits all the rows into groupscalled bins where each bin has the same value of as the targetexpression.

Consider the query in the example above, the GROUP BY clausespecifies that rows will be grouped where each bin has the same valueof the X column. Using the same table, we can see the first grouphaving X=1 contains 2 rows, while the second group having X=2contains only a single row.

The GROUP BY query will therefore return two rows (one for eachbin). Each row will contain a single value for the X value and oneof the Y values.

Aggregate functions

Aggregate VQL functions are designed to work with the GROUP BYclause to operate on all the rows in each bin separately.

Aggregate functions keep state between evaluations. For exampleconsider the count() function. Each time count() is evaluated, itincrements a number in its own state.

Aggregate function State is kept in an Aggregate Context - aseparate context for each GROUP BY bin. Therefore, the followingquery will produce a count of all the rows in each bin (because eachbin has a separate state).

SELECT X, count() AS CountFROM …GROUP BY X

Aggregate functions are used to calculate values that considermultiple rows.

Some aggregate functions:

• count() counts the total number of rows in each bin.
• sum() adds up a value for an expression in each bin.
• enumerate() collect all the values in each bin into an in-memory array.
• rate() calculates a rate (first order derivative) between eachinvocation and its previous one.

These can be seen in the query below.

VQL Lambda functions

In various places it is possible to specify a VQL lambdafunction. These functions a simple VQL expressions which can be usedas filters, or simple callbacks in some plugins. The format is simple:

x=>x.Field + 2

Represents a simple function with a single parameter x. When thelambda function is evaluated, the caller will pass the value as xand receive the result of the function.

Usually lambda functions are specified as strings, and will beinterpreted at run time. For example the eval() function allows alambda to be directly evaluated (with x being the current scope inthat case).

SELECT eval(func="x=>1+1") AS Two FROM scope()

The scope that is visited at the place where the lambda is evaluatedwill be passed to the lambda function - this allows the lambda toaccess previously defined helper functions.

LET AddTwo(x) = x + 2SELECT eval(func="x=>AddTwo(x=1)") AS Three FROM scope()