Skip to main content

Defining the Query

Queries​

In describing the simplest form of creating a table, we skipped the query itself and just used the timeframe to select the dataset itself. Now we are going to look at how to better select data using a query. There are four ways to select data in the system:

  • Lucene Query
  • Facet Filters
  • Query Tree
  • search and where commands

When using Fluency, you can leverage both the timeframe and search capabilities through the interface to create the dataset. To do this, search using the search bar, facet, or query builder and then switch to the code tab. The code will use the current search as the search for the code. However, a search can also be expressed as options to the search and where commands. The difference between the two is that the search command processes the data in the Map-Reduce database framework, while the where command acts as a filter to an established table. When choosing between the two commands, always choose the search for faster results.

Search Expressions​

Search expression can occur after either a search or where command.

search { from="-7d<d", to=">d" } sContent("@behaviors","O365_User_Added")

The search command can also have a function added to that enhances the query. Besides the timeframe’s from and to attributes, search expressions also refer to fields (key) and value. The term field refers to a dot notation of the field name. While, value refers to the contents stored value.

In a JSON object of:

{
  wheels: 4,
  color: `blue`,
  interfaces: {
    steeringWheel: true,
    pedals: [`clutch`, `accelerate`, `brake`] 
  }
}

A field could be: wheels, color, interfaces.steeringWheel, or interfaces.pedals. While values could be: 4, blue, clutch or true.

The built-in search expressions are:

  • sSontent(field, value) : Returns true when the value is stored in the field.
  • sContains(field, value): Returns true when the value is a substring of the field.
  • sStartswith(field, value): Returns true when the substring is the starting sequence of the field.
  • sEndswith(field, value): Returns true when the substring is the ending sequence of the field.
  • sRange(field, from, to): Used for when there is an explicit field that is a timestamp. In this case, the field will be tested to see if the value is in the range of from to to.
  • sRegexp(field, field, value).
  • sEntityinfo(entityinfo, field, value).
  • sIsnull(field): Tests to see if the field’s value is set to null.
  • sWildcard(field): Tests to see if there is a value related to a field.

where command allows the search expression to be placed on a separate line. This is a post search filter. Here we are using a handy nomenclature for the f() function. The f() function is returning an Object. To the left of the assignment multiple children attributes are referred too, while at the same time being named. The attribute accountName as a child attribute of "@sentinelOneAgent" is being assigned the handle of account.

search {from="<d", to=">m", type="resource"} sContent("@dayIndex","20220511-16")
let {account="accountName", computer="computerName"} = f("@sentinelOneAgent")
let {group="groupName", machineType, modelName} = f("@sentinelOneAgent")
where sContent(account, "Fluency Security - Reseller Account")

The advantage of a where command is that we can first assign handles to the field names that are simpler and easier to understand as they will match the column names of the result table. Then use that handle to refer to is in the search expression. When doing it this way, the handler does not have quotes around it. Looking at the example above, notice that account is assigned the field name of "@sentinelOneAgent.account". Then this handle is used as the field in the sContent search expression. Where commands can be chained together to create complex searches while maintaining readability. But as noted at the very beginning of this section, it is always preferred to use the search command over using the where command.

Query Tree: Fluency LavaDB (LVDB) Query Trees​

Fluency has another method of building queries for both the Event Search and the EventWatch pages. Previously, the Event Search queries were built using either Lucene queries or Regular Expressions. While these were sufficient, they were also limited in some cases. The new mechanism allows more complex queries to be used.

Structure​

The new query tree structure is built using an ordering similar to reverse polish notation. As such, queries are built downward will Booleans coming before individual operations. Here is a somewhat complex example of an LVDB Query taken from a SIGMA rule that was imported for detecting mimikatz:

LavaDB Query Tree

First, notice that the query begins with a root element at the top. All other elements branch off of this root.

Query Root

After the root, typically, there are one or more Boolean operations. These Boolean operations are essential to building complex queries as they ties the leaf elements together. While technically a query can consist of just a single leaf element, that is typically not the case. Cases where a single leaf can be used may suffice just as well being a Lucene or Regular Expression query. Each Boolean operates on all of its child nodes. If there will be multiple top-level conditions (for complex queries), then there will most likely be nested Booleans. See below for groups of Booleans and their children (colored circles).

Query Scope

Each color represents a nested level from the root to the innermost query. In this manner complex queries can combine booleans AND/OR.

To build a query, it is recommended that this be done initially in Event Search in order to verify the results being sought prior to finalizing in EventWatch.

Login to the Fluency Cloud portal: https://(companyname).cloud.fluencysecurity.com.

Open the dropdown menu and choose the Event Search option under the Data Lake section.

Switch to the "QUERY BUILDER" tab.

The three buttons on the top left of the query builder are Delete, Copy, and Paste. These are useful for starting over, pasting in an already created query (such as from EventWatch) or copying the query to be saved/used elsewhere (such as creating an EventWatch behavior). The root element will also be shown with no children. To add a child element, press the "+" button to the right of the root node.

This will provide a dialog for adding a query filter.

In most cases, at the root, this will typically be a Boolean so that more query filters can be added.

Type Selection

Once the Boolean is added, the tree will be updated with it as the new child of root. Click "SAVE" to save it.

Query Confirm

At this point, it becomes a matter of continuing to build the query out either by adding more Booleans to adding the combination of leaf filters needed to provide the results being sought. Once this has been completed, set the time frame and click search. If the desired results are shown then the query may be complete. Otherwise, make changes as needed and re-test until the desired results are shown.

Copy the Query to a new Behavior (EventWatch)​

Once the query has been built and tested, use the "Copy" button from the Query Builder to make a copy of the completed query.

Updated Root Node of Query

Switch to the EventWatch Rules page (if the intent is to build a behavior from this query).

Create a new behavior as normal (clicking the "+" button on the top right).

The only difference now is the need to paste the LVDB query into the behavior as it is built. Look for the LVDB Query checkbox under "Selection Criteria", and check it. The same three buttons should be shown as were shown on the Event Search page. In this case, click the "Paste from Clipboard" button to open the past dialog box. Past the text (currently in the clipboard) into the dialog box and click "SAVE".

Copy to Clipboard

Now, after verifying that the LVDB Query tree is in the new behavior dialog and correct, complete the rest of the behavior as needed.

Summary​

While this post explains the basics of the LVDB Query feature that was added, it is just meant as the starting point for users to test different creations and complex queries. It may seem daunting at first, but with practice, it will become almost second nature for most analysts in creating complex queries for their searches and custom behaviors.

Appendix - Query Filter Options​

If you use the query builder enough, you will find that the terms map almost exaclty into the processing language functions. Here is a list of the type of matches and their meaning:

bool​

  • This can be the boolean AND or OR an operates upon leaf elements below this node, including other booleans. This is the only non-leaf element in the tree aside from the root

content​

  • the entirety of the field specified must match the criteria (exact text match, quotes not required)

contains​

  • the field must contain the criteria within its data

startswith​

  • the field starts with the criteria (replaces the suffix wildcard "*" in search query)

endswith​

  • the field ends with the criteria

range​

  • specifies a range for the field

regexp​

  • a regular expression criteria for the specified field

entityinfo​

  • the contents of the field must be contained in the specified entityinfo list

wildcard​

  • matches anything in the field that is not null

isnull​

  • the field is null

Page last updated: 2023 Aug 10