Building Queries

What Is a Query? #

Queries and filters are methods to search the database in Lucidum to find data that meets your specifications. You can then include the results in charts, actions, and webhooks.

Queries include:

• Type of Data. This is the top-level category for each query and specifies the type of Lucidum object you want to get information about. Choices are:

  • Asset. Retrieve information about assets.

  • User. Retrieve information about users.

  • Asset-IP Mapping. Lucidum uses proprietary machine-learning algorithms to align each asset with an IP address. You can retrieve information about these asset/IP pairs.

  • User-IP Mapping. Lucidum uses proprietary machine-learning algorithms to align each user with an IP address. You can retrieve information about these user/IP pairs.

  • Vulnerability. Retrieve information about vulnerabilities.

• Time Range. You must first select whether you are interested in current values or historic values. The choices are:

  • Current. The default value is from the present day to 7 days old.

  • History. Older than current.

• Operators. Operators define the relationship between the fields and the values. Operators can include “match”, “not match”, “is equal to”, “is not equal to”, “is greater than”, “is less than”, “exists”, “is empty”, among others. Lucidum provides a set of operators for each field. For details on operators, see the chapter on Data Types and Operators

• Values. Values define the criteria for the specified fields. For example, if you chose “Memory Size (GB)” as a field, you could choose “is equal to” as an operator and “256” as a value. Your results would include only assets with 256 GB of memory.

• Keywords. Lucidum queries support two keywords: AND and OR.

  • AND means that the results must meet all the criteria in a multi-part query.

  • OR means that the results must meet one of the criteria in a multi-part query

The Query Builder builds read-only (SELECT) queries.

To learn the basics of queries, you can read these articles. Although the Lucidum user interface does not require SQL syntax, these articles will help you understand how queries work.

https://www.w3schools.com/sql/sql_intro.asp

https://www.w3schools.com/sql/sql_select.asp

https://www.w3schools.com/sql/sql_and_or.asp

Viewing Your Data #

Before creating queries, you might find it helpful to view the available data on your Lucidum system.

After ingesting raw data from multiple data sources, Lucidum deduplicates, triangulates, and aggregates the data to create enriched records for each asset and user.

To view a list of all assets, all users, and all vulnerabilities in your environment:

1. Go to Dashboards

2. In the left pane, go to the All Channels.

3. Search for the following dashboards:

   • Assets

   • Identities

   • Vulnerabilities

4. See details in Viewing Data About All Assets, All Users, and All Vulnerabilities.

Example Queries #

The Value-Oriented Dashboards manual describes already-configured dashboards that you can use as-is or customize to fit your environment. You can also view working queries that you can customize to your needs.

Creating a Query #

There are two ways to create a query:

• From the button

• From the Add Chart or Edit Chart page

Creating a Query from the Query Button #

To create a query from the Query button: click the Query button in the upper right of any page in Lucidum.

1. Click the Query button in the upper right of any page in Lucidum.

   

2. The Build a Query page appears. In the Build a Query page, define the following fields:

   • Type of Data. This is the top-level category for each query and specifies the type of Lucidum object you want to get information about. Choices are:

     • Asset. Retrieve information about assets.

     • Asset-IP Mapping. Lucidum uses proprietary machine-learning algorithms to align each asset with an IP address. You can retrieve information about these asset/IP pairs.

     • User. Retrieve information about users.

     • User-IP Mapping. Lucidum uses proprietary machine-learning algorithms to align each user with an IP address. You can retrieve information about these user/IP pairs.

     • Vulnerability. Retrieve information about vulnerabilities.

   • Time Range. You must first select whether you are interested in current values or historic values. The choices are:

     • Current. The default value is from the present day to 7 days old.

     • History. Older than current.

     NOTE: You can customize or view the Time Range values in Settings > System Settings > Data Settings. Current uses the value of Data Lookback in Days.

   • Fields. Fields are one or more properties that you are interested in, such as “first time seen”, “ip address”, “county code”, or “risk score”. Fields map to column names in a database table. For details on fields, see the chapter on Fields and Regular Expressions

3. In the Build a Query page, click the Next button. The Build a Current Asset Query page appears.

   

4. Follow the steps in the section Building the Query.

Creating a Query from the Add Chart or Edit Chart page #

To create a query from the Add Chart page or the Edit Chart page:

1. Create or edit the chart.

   

2. In the Add Chart or Edit Chart page, click the Configure Filters button. The Configure Filters for Chart page appears.

   

3. Follow the steps in the section Building the Query.

Building the Query #

1. In the both the Build a Query page and the Configure Filters page, you follow the same steps to build a query.

   

2. Provide values in the following fields:

   • Field. In this field, you select a field for the query. A field is a characteristic of a Lucidum object, usually a column in a database table. For details about fields, see the section on Fields.

   • Operators. In this field, you select an Operator. The list of operators is dependent on the value you selected in Field. For details on operators for each data type, see the section on Operators.

   • Values. In this field, you select a value. This list of available values is dependent on the value you selected in Field and the value you selected in Operators.

3. For the example query in the screen capture above, we specified:

   • Type of Data. Asset. We are interested in assets.

   • Time Range. Current. We will collect data from the database for the current .

   • Field. Risk Score. We will examine the field “Risk Score”.

   • Operator. is greater than or equal to. Because the Operator field includes operators for numeric values, we know “Risk Score” is a numeric value. We are looking for values of “Risk Score” that are greater than or equal to the value in Value.

   • Value. 15.65. Lucidum populates the list of values with all the values for “Risk Score” in the assets table for the current . We chose 15.65. So we are looking for values of “Risk Score” that are greater than or equal to 15.65.

4. To see the results of the query, click the Show Result button. The Query Result page appears.

   

Fields #

The Types of Data field specifies an object to examine. Choices are:

• Asset

• Asset-IP Mapping

• User

• User-IP Mapping

• Vulnerability

When building a query, Fields are characteristics of the Lucidum objects. For example, a characteristic of a user is the user’s email address. A characteristic of an asset is the asset’s IP address. Usually, a field maps to a column name in a Lucidum database.

Lucidum ingests information about assets, users, and data from your environment. Lucidum correlates that information to provide you with details about assets, users, asset-IP mapping, user-IP mapping, and vulnerabilities.

For details on the standard list of fields in Lucidum and special characters you can use in queries, see the appendix on Fields and Regular Expressions.

You might see fields in the appendix that don’t appear in your Lucidum system. This means that Lucidum has not fetched that data from your environment, either because you have not yet configured the connector or because your environment doesn’t include that type of asset.

You might see fields called “Extra Fields” in your Lucidum system that don’t appear in the list of fields in the appendix. This means that Lucidum has fetched data from your environment that is either specific to your environment or not available in all environments.

Creating Favorite Fields #

Lucidum allows you to add frequently used fields to a list of Favorites.

If you frequently use a field, you can click the star icon next to the field name. The field then appears in the Favorite category at the top of the list of fields.

Operators #

Operators define the relationship between the fields and the values.

Operators are dependent upon the data type.

For example:

• Numeric data includes operators like “is equal to” or “is greater than”.
• Date and time data includes operators like “within past”.
• Text data includes operators like “match” or “is equal to”.

For details on each data type and its operators, see the appendix on Data Types and Operators.

Values #

The list of values is dependent on the Lucidum object, the Field, and the Operator. Lucidum populates the list of Values after you select a Lucidum object, Field, and Operator.

You can also use regular expressions in the Values field. For details, see the appendix on Fields and Regular Expressions.

For example, if you selected the following:

• Build Query from (Lucidum object). Asset
• Field. Risk Score
• Operator. is greater than or equal to
• Value. 15.65

The Build Query from field indicates that we are interested in Assets.

Field indicates that we want to retrieve assets with a specific Risk Score.

Because Operators includes operators for numeric values, we know Risk Score is a numeric value.

Lucidum populates the Values with all the numeric values for all Risk Scores in the Assets database table. In our example above, we chose 15.65.

Writing Queries that Use AND and OR #

Lucidum allows you to create multi-part queries. To do this, you can use AND and OR keywords.

• The link for AND creates an AND condition.

• The link for OR creates an OR condition.

AND Condition #

An AND condition specifies that Lucidum should retrieve all records that match all conditions. You can specify as many conditions as you choose.

For example:

This query specifies that we are interested in all assets:

• where the OS is Windows Server 2019

AND

• the department is HR

Any asset that matches both criteria will be included in the Query Results page.

• On this Lucidum system, 747 assets are running Windows Server 2019.

• However, only 44 assets have both are running Windows Server 2019 and are in the HR department.

• Therefore, the Query Results page displays 44 assets.

OR Condition #

An OR condition specifies that Lucidum should retrieve all records that match at least one of multiple conditions. You can specify as many conditions as you choose.

For example:

This query specifies that we are interested in all assets that:

• have an Operation System of Windows Server 2016

OR

• have an Operation System of Windows Server 2019

Any asset that matches either of these criteria will be included in the Query Results page.

For example, using the query above, the Query Results page looks like this:

• 722 assets are running Windows Server 2016

• 747 assets are running Windows Server 2019

• The Query Results page displays 1469 assets

Queries with Nested Lists #

Lucidum includes fields of type “nested list”. These fields behave differently than list fields.

Nested Lists are sometimes called arrays and organize data in a table structure.

For example, Applications is a field of type “nested list”. In the Query tool, the field looks like this:

Note that Applications is the nested list. And Applications::Name, Applications::Source and Applications::Version are sub-fields. You can think of the sub-fields as columns in a table.

It is important to understand how Lucidum performs queries on Nested List fields.

• If you select the field Applications, Lucidum will run the query against each row (element) in the table.

• If you select the a sub-field, for example Applications::Name, Lucidum will first extract all the values from the Name column, create a list, and then run the query against that list.

Example Data for Applications #

For example, suppose we have two assets, Asset-1 and Asset-2.

Asset-1 has the following applications:

• Each row is an element.

• Each column is a sub-field.

So for Asset-1:

• Applications::Name = Python, Docker, Git

• Applications::Version = python (ver 2.7.10), None, git (ver 3.8.10)

• Applications::Source = AWS, Datadog, SentinelOne

Asset-2 has the following applications:

• Each row is an element.

• Each column is a sub-field.

So for Asset-2:

• Applications::Name = Jenkins, Docker, Git

• Applications::Version = None, None, git (ver 3.1.1)

• Applications::Source = AWS, Datadog, SentinelOne

Example 1: Difference Between Nested List Field and Its Sub-fields #

Suppose you wanted to find all assets where the Application name is “Python” and the Version is “3.8.10”. Using the tables above, you would expect to find zero results.

Suppose you wrote a query that searched for Application::Name is “Python” and Application:Version is “3.8.10”.

This query is wrong.

This query returns Asset-1.

• Lucidum searches the name list for Asset-1, which is “Python, Docker, Git”.

  • Asset-1 matches Application::Name is “Python”.
• Next, Lucidum searches the version list for Asset-1, which is “python (ver 2.7.10, none, and git (ver 3.8.10).
  • Asset-1 also matches Application::Version is “3.8.10”.

• Because Asset-1 matches both parts of the query, the query returns Asset-1.

• Lucidum searches the name list for Asset-2, which is “Jenkins, Docker, Git”.

  • Asset-2 does not match Application::Name is “Python”.

• Lucidum search the version list for Asset-2,. which is “None, None, git (ver 3.1.1).
  • Asset-2 does not match Application::Version is “3.8.10”.

Instead, you want Lucidum to apply the query to each row (element) in the table, so you must use the Applications field (not the sub-fields).

And a row must match all parts of the query, so you should use the “element matching all” syntax.

The correct query is:

</p;>

This query is correct.

This query returns no results.

• Lucidum would search each row (element) in Applications for Asset-1.
  • There is no row where Application Name is “Python” AND Application Version is “3.8.10”.
• Lucidum would search each row (element) in Applications for Asset-2.
  • There is no row where Application Name is “Python” AND Application Version is “3.8.10”.
• Therefore, the query returns no results.

Example 2: Difference Between Nested List Field and Its Sub-fields #

Suppose you wanted to find all assets that are not running Python. You would expect the query to return Asset-2.

Suppose you wrote a query that used “element matching all” and “Name is not Python”.

This query will examine each row in the Applications table.

This query is wrong.

This query returns Asset-1 and Asset-2.

When you use the “Applications” field and “Element matching all”, Lucidum searches each row (element) in Applications for Asset-1. And then searches each row (element) in Applications for Asset-2.

• For Asset-1, row one has an Application Name of “Python”.
  • Row one does not match the query.
• For Asset-1, row two does not have an Application Name of “Python”.
  • Row two matches the query.
• For Asset-1, row three does not have an Application Name of “Python”
  • Row three matches the query.

• Asset-1 satisfies the query because two rows (elements) match the query. Therefore, Asset-1 matches this query.

• For Asset-2, none of the rows (elements) have an Application Name of “Python”.

  • Asset-2 matches this query.

Instead, you want to use a query that looked at the column Application::Name.

This query is correct.

• First Lucidum would search the Name column for Asset-1, which is “Python, Docker, Git”.
  • Asset-1 does not match, because one of the values of Application::Name is “Python”.
• Next, Lucidum would search the Name column for Asset-2, which is “Jenkins, Docker, Git”.
  • Asset-2 matches the query because none of the values of Application::Name is “Python”.
• The query returns Asset-2.

Example Data for Tags #

Suppose Asset-1 has the following Tags:

• Each row is an element.

• Each column is a sub-field.

So for Asset-1:

• Tag::Key= Environment, Name, Purpose

• Tag::Value = Prod, Lucidum-Public-VM, Pentest

Suppose Asset-2 has the following Tags:

• Each row is an element.

• Each column is a sub-field.

So for Asset-2:

• Tag::Key= Environment, Name, Purpose

• Tag::Value = Dev, Lucidum-Dev-VM, Test

Example 3: Syntax for “element matching all” #

Suppose we query the Tags nested field, which means the query will examine each row.

Suppose we look for assets where the tag Key matches Environment and the tag Value matches Prod.

Suppose we use the “element matching all” syntax. This syntax searches for assets with at least one row that matches both criteria where the tag Key is “Environment” AND the tag Value is “Prod”.

We would expect this query to return Asset-1.

This query returns Asset-1.

• For Asset-1:

  • row one has both a tag Key “Environment” AND a tag Value “Prod”. So that row (element) does match.

  • However, row two does not match and row three does not match.

  • Asset-1 satisfies the query because one row (element) matches the query.

• For Asset-2, none of the rows (elements) have both a tag Key “Environment” AND a tag Value “Prod”.
  • So Asset-2 does not match the query

Example 4: Other Syntax #

• element matching any. This syntax says that at least one row has the tag Key “Environment” string OR the tag Value “Prod”.

  • From our examples, row one for Asset-1 matches both these criteria.

  • Row one for Asset-2 matches the tag Key “Environment”

  • This query returns Asset-1 and Asset-2.

• no element matching all. This syntax says that no rows in the table match both the tag Key “Environment” string AND the tag Value “Prod.

  • For Asset-1, row one has both a tag Key “Environment” AND a tag Value “Prod”. Asset-1 does not match the query.

  • For Asset 2:, no rows have both a tag Key “Environment” AND a tag Value “Prod”. Asset-2 matches the query.

  • The query returns Asset-2

• no element matching any. This syntax says that no rows has the tag Key “Environment” string OR the tag Value “Prod”.

  • For Asset-1, row one has both the tag Key “Environment” string OR the tag Value “Prod”. Asset-1 does not match the query

  • For Asset-2, row one includes the tag Key “Environment”. Asset-2 does not match the query

  • The query returns no assets

Viewing Query Results #

The Show Results (checklist) icon opens the Query Results page. The Query Results page displays a table populated with the results of the query for the chart.

Using an example query:

• This query specifies that we are interested in all assets that:

  • have an OS and Version that matches Windows Server 2016

OR

• • have an OS and Version that matches Windows Server 2019

• Any asset that matches the criteria will be included in the Query Results page.

• For example, using the query above, the Query Results page looks like this:

  

• The Query Results page displays a list of assets that are running either Windows 2019 or Windows 2016.

  • You can sort the page by the column headings.

  • To see additional details about an asset, user, or vulnerability, click the > (right arrow) to see the Details page.

  • Notice the Changed Fields column. If a value appears in this column, you can view details about the changed fields in the Field Change History tab in the Viewing Details page. These are the changed fields between the latest ingestion and the ingestion before the last ingestion.

In the Query Results page, you can perform the following:

For the current page of results, you can:

• Columns. Add or remove columns from the page.

• Export. Download the results as a CSV file..

For each column name, you can:

• Sort by ASC. Sort the results by this column, in ascending order.

• Sort by DESC. Sort the results by this column, in descending order.

• Pin to left. The column is pinned to the left border. When you scroll left to right to view all the columns, this column stays on the left border.

• Pin to right. The column is pinned to the right border. When you scroll left to right to view all the columns, this column stays on the right border.

• Filter. Allows you to filter the table by one or more columns in the results table.

• Hide Column. Removes the column from the page.

• Manage columns. You can include or not include one or more columns in the results table.

If you select one or more checkboxes in the Query Results page, you can also:

• Create a recurring action with this query. Use the results of the query to create an action. Upon selecting this option, Lucidum displays the Create a new action page, where you can define the action. For details on actions, see Actions.

• Create a one-time action with selected data. You can select one or more records and create an action that uses selected records and runs only once. For details on actions, see Actions.

• Assign a Tag. Apply a tag. Tags are defined in the Tag Management page (Settings > Tag Management), can be manually applied to assets and users, and can be selected as a fields in a query. For details, see the section on tags in the manual Streamlining Queries with Smart Labels and Tags.

Details page #

In Lucidum, you can view details about the data for a single asset, user, or vulnerability. When we go to the row for asset MOSGOOD-ZXBTNV5 and click on click on the > icon, we see the Details page.

There are three types of data in Lucidum, all of which you can view in Lucidum and use in dashboards:

• Data Source. Data sources map to connectors. For each asset, user, and vulnerability, you can view all the data sources from which Lucidum ingested data about the asset, user, or vulnerability. You can select a Data Source to exactly see which raw data was provided by that data source.

• Lucidum Data Group. Enriched data about assets, user, and vulnerabilities. Lucidum ingests data from multiple data sources and uses machine learning to enrich and normalize that data. The Lucidum Data Group tab allows you to view this enriched data for each asset, user, and vulnerability. Data in the Lucidum Data Group appears as fields in the Query tool.

• Field Change History.  Displays fields that have changed, each field’s current value and each field’s previous value. These are the fields that have changed in the last seven days.

For more information on the Details page and its tabs, see Viewing Details about Individual Assets, Users, or Vulnerabilities.

Exporting a Query to a .CSV File #

From the Query button, you can export queries to a .CSV file for use in reports or analysis outside of Lucidum.

To do this:

1. 1. Create a query or choose a Saved Query or choose a query from Query History.

   2. To see the results of the query, click the Show Result button. The Query Result page appears.

   3. In the Query Results page, click the Export icon in the upper right corner. Choose Export.

      

1. The query and its results are saved to a .CSV file on your local computer.

Using Saved Queries and Query History #

The Query page includes fields for re-using queries:

• Saved Query. Leads to a list of saved queries. Saved queries are associated with your login. You cannot view the saved queries from other Lucidum users.

• Query History. Leads to a list of cached queries. Query history is not associated with your login but instead includes all queries from all Lucidum users on the current Lucidum system. Query history is deleted when the Lucidum system is rebooted or restarted.

For details on creating, using, and managing saved queries and historical queries, see Saved Queries and Historical Queries.

Using Smart Labels and Tags #

Lucidum includes two types of user-defined, query-based fields to make repetitive tasks easier and to help organize assets and users. For details on creating, using, and managing Smart Labels and tags, see Streamlining Queries with Smart Labels and Tags .

Creating a Global Filter #

A global filter is a parent filter that is applied to all queries and all charts in a specific Dashboard.

1. To create a global filter:

2. Open the dashboard you want to add a filter to.

   

3. Click on the Refine Scope (funnel) button.

4. In the Build a Query page, create a query as you normally would.

   

5. Click Apply to apply the query to all charts in the current dashboard.

6. The new filter appears to the left of the Refine Scope button.

7. The new filter is applied to all the charts and queries in the dashboard.

   

8. In our example, we created a query that filtered assets by the Country Code match United States. Notice how the value in the “Workstations” chart, the “Servers & VMn” chart, and the Containers & Microservices chart has changed after we applied the global filter. This is because all queries and charts include only assets with the Country Name “United States”.