Building Queries

Introduction

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.

Filters include:

• Type of Data. This is the top-level category for each filter. 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. The default value is from 8 days old to 30 days old.

• Field. 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

• Operators. After selecting a field, the operator field appOperators 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

Lucidum 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. Lucidum then stores the enriched data in the Lucidum master database tables.

Lucidum includes the following master tables:

• Assets

• Users

• Vulnerabilities

The master tables store data that has been transformed by Lucidum.

Assets

To view data that Lucidum has ingested about assets:

1. Click the Asset icon in the left navbar.

2. The Asset page appears. In this page, you can view and sort data about assets.

• To view one or more additional columns in this page, or to remove a default column from this page, click on the Edit Column button.

• To sort by a column value, click the up and down arrows next to the column name.

• To filter by a column value, click the funnel icon next to the column name. You can filter by more than one column. The first column you use as a filter will be the primary filter. You can further filter by additional columns.

• After creating a filter, you can click the Query Builder button to see a query that reflects the currently applied filters.

Users

To view data that Lucidum has ingested about users:

1. Click the User icon in the left navbar.

2. The User page appears. In this page, you can view and sort data about users.

• To view one or more additional columns in this page, or to remove a default column from this page, click on the Edit Column button.

• To sort by a column value, click the up and down arrows next to the column name.

• To filter by a column value, click the funnel icon next to the column name. You can filter by more than one column. The first column you use as a filter will be the primary filter. You can further filter by additional columns.

• After creating a filter, you can click the Query Builder button to see a query that reflects the currently applied filters.

Vulnerabilities

To view data that Lucidum has ingested about vulnerabilities:

1. Click the Vulnerability icon in the left navbar.

2. The Vulnerability page appears. In this page, you can view and sort data about CVEs.

• To view one or more additional columns in this page, or to remove a default column from this page, click on the Edit Column button.

• To sort by a column value, click the up and down arrows next to the column name.

• To filter by a column value, click the funnel icon next to the column name. You can filter by more than one column. The first column you use as a filter will be the primary filter. You can further filter by additional columns.

• After creating a filter, you can click the Query Builder button to see a query that reflects the currently applied filters.

Creating a Query

You can create a in query in three ways:

• Create a query from the Locate button

• Create a query in the Add Chart page

• Create a query in the Master Tables. For details, see To Saving Filters as Queries.

1. To create from the Locate button and save it for use in a chart, click the Locate button in the upper right of the Dashboard page (or any page in Lucidum). The Locate page appears.

2. To create a query from the Add Chart page, click the Configure Filters button. The Refine Scope page appears.

3. To create a query from the Edit Chart page, click the Configure Filters button. The Refine Scope page appears, with the current query loaded.

4. In the Locate page, the Add Chart page, and the Edit Chart page, you must specify the following:

• Type of Data. Select the type of Lucidum object you want to retrieve data about. Choices are:

  • Asset. Retrieve information about assets.

  • Asset-IP Mapping. Lucidum uses proprietary machine-learning algorithms to align each IP address with an asset. 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 IP address with a user. You can retrieve information about these user/IP pairs.

  • Vulnerability. Retrieve information about vulnerabilities.

• Time Range. Select the time range you want to include in the query. Choices are:

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

  • History. The default value is from 8 days old to 30 days old.

4. If you are working in the Locate page, click the Next button. The Locate a Current Asset page appears.

5. If you working in the Add Chart or Edit Chart page, Click the Configure Filters button. The Refine Scope page appears.

4. The Locate a Current Asset page and the Refine Scope page behave identically when building a query.

5. To build the query, enter 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.

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

• Build Query about. Asset. We are interested in assets.

  • With the time range of. Current. We will collect data from the database for the current time range.

  • 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 time range. We chose 15.65. So we are looking for values of “Risk Score” that are greater than or equal to 15.65.

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

  

Fields

The Build Query from field specifies an object to examine. Choices are:

• Asset

• User

• Asset-IP Mapping

• User-IP Mapping

• Vulnerability

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.

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 operator, see the appendix on 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:

• 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

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

In the New Query page, there are two links for Add Condition.

• The link for Add Or Condition inside the purple line creates an OR condition.

• The link for Add And Condition outside the purple line creates an AND condition.

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:

• 314 assets are running Windows Server 2016

• 337 assets are running Windows Server 2019

• The Query Results page displays 651 assets

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 that:

• 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.

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

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

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

• Therefore, the Query Results displays 23 assets.

Exporting a Query to a .CSV File

You can export queries to a .CSV file for use in reports or analysis outside of Lucidum.

To do this:

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 elipses (…) in the lower right corner. Choose Export Result.

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

Using Saved Queries and Query History

The Query Builder 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.

Saving a Query

If you want to save a query for use in a chart, a Dynamic Field, or an Action:

1. Follow the steps to Create a query.

2. In the New Query page, click the … next to the Show Result button. Choose Save Query.
   

   

3. The Save Query dialog page appears:
   

   

4. In the Save Query dialog page, supply values in the fields:

   • Name. The name of the saved query.

   • Description. Optional description of the query.

   • Group. Optional group to organized saved queries.

5. The saved query now appears in the list of Saved Queries in the Query Builder page.

Loading a Saved Query

To load a saved query for use in a chart, a Dynamic Field, or other parts of Lucidum:

1. To load a saved query, click the Explore button in the lower right of the Dashboard page. The Query Builder page appears.

2. To load a saved query from the Create Chart page, click the Add filters button. In the New Query page, click the < in the upper left. The Query Builder page appears.

3. To load a saved query from the Edit Chart page, click the Edit filters button. In the New Query page, click the < in the upper left. The Query Builder page appears.
   

   

4. In the Query Builder page, click on the Saved Query field.

5. The Saved Query page appears, with a list of all saved queries.
   

   

6. Select a query you want to use. The box around the query turns blue.

7. Click on the Load button in the lower right of the Saved Query page.

8. The New Query page appears, with the saved query loaded.
   

   

9. If you navigated to this page from a chart, you can select the Apply button to use the saved query in the chart.

Query History

If you would like to re-run a query that you ran earlier but did not save as a Saved Query, you can use the Query History feature.

To load a query history query for use in a chart, a Dynamic Field, or other parts of Lucidum:

1. To load a query from Query History, click the Explore button in the lower right of the Dashboard page. The Query Builder page appears.

2. To load a query from Query History from the Create Chart page, click the Add filters button. In the New Query page, click the < in the upper left. The Query Builder page appears.

3. To load a query from Query History from the Edit Chart page, click the Edit filters button. In the New Query page, click the < in the upper left. The Query Builder page appears.
   

   

4. In the Query Builder page, click Query History.

5. The Query History page appears.
   

   

6. Select a query you want to use. The box around the query turns blue.

7. Click on the Load button in the lower right of the page.

8. The New Query page appears, with the query from query history loaded.

   

9. If you navigate to this page from a chart, you could select the Apply button to use the query in the chart.

Creating Dynamic Fields

You can create queries and save the queries as customized Fields. These new Fields are called Dynamic Fields. For example, you can build a query and assign that query a name that follows your organization’s internal naming conventions.

When building queries, you can use the Dynamic Fields as you would use any of the default Fields.

To create a Dynamic Field:

1. Follow the steps to Create a query.
   

   

2. In the New Query page, click the … next to the Show Result button. Choose Add Dynamic Field.

3. The Add Dynamic Field dialog page appears:
   

   

4. In the Add Dynamic Field dialog page, enter the following:

   • Field Name. Enter a name for the Dynamic Field. This name will appear in the list of fields when you build a query.

   • Type. Select Text or Number. This field determines the Operators and Values Lucidum will associate with the new Field.

   • Query has result. If this query returns results,

   • Query has no result.

   • Confirm. Click to save the new Dynamic Field.

5. You can now use apply the Dynamic Field as an additional condition in a query.
   

   

Example

You could define the following security requirements for each user workstation in your organization:

• Managed in Active Directory

• Endpoint protection is installed

• Hard drive is encrypted

1. You could write a query like this:

   

2. You could then save the query as a Dynamic Field named “secure_workstation”:

   

   • For workstations that meet the custom security requirements, the value of “secure_workstation” field will be “1”.

   • For workstations that do not meet the custom security requirements, the value of “secure_workstation” field will be “0”.

3. You can use this Dynamic Field in dashboards and throughout Lucidum.

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 Add Global Filter button.

4. Create a query as you normally would.
   

   

5. Click Apply to apply the global query to the current dashboard.

6. The new filter appears to the right of the Add Global Filter 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 “CA” (Canada). Notice how the value in the “Count of Asset by All” chart and the “count all” chart has changed after we applied the global filter. This is because all queries and charts include only assets with the Country Code “CA”.