Creating Queries with the Query Wizard
  • 01 Jun 2022
  • 13 Minutes to read
  • Dark
    Light
  • PDF

Creating Queries with the Query Wizard

  • Dark
    Light
  • PDF

You can create and save queries in the following modes:

  • Create queries on assets using the Query Wizard
  • Create queries on Activity logs and Fetch History using filters. For more information, refer to Creating Queries Using Filters.

This page explains how to create queries on assets using the Query Wizard.

Use the Axonius Query Wizard to create granular queries to understand how assets adhere to their policies. You can define a wide variety of filters, from which you can easily drill down to the assets that match the required search criteria. For example, you can use the Query Wizard to show only Windows device assets that have been seen in the last 7 days using filters where the value of the common field OS: Type equals Windows and the value of the common field Last Seen is within the last 7 days.
QueryWizard1

Working with the Query Wizard

From the Devices page, click Query Wizard on the top right corner above the Devices table. The Query Wizard definition box opens.
QueryWizard2

Each row in the Query Wizard is a filter expression. You need to define the following elements:

  1. WHERE/WHERE NOT switch
  2. AND / OR/ AND NOT/ OR NOT switch
  3. NOT Flag
  4. Source drop-down
  5. Adapter drop-down
  6. Field drop-down
  7. Operator drop-down
  8. Value field
  9. Bracket controls

Once you create a query you can save it for future use. Learn more about Saved Queries.

1. WHERE/WHERE NOT Switch

Each expression starts with the term WHERE.
You can choose WHERE NOT to negate the complete filter expression and only match assets that do NOT match the complete expression.

2. AND / OR / AND NOT/ OR NOT Switch

The AND/OR/AND NOT/OR NOT switch is only displayed on the second and subsequent filter rows. When you have more than one filter defined, use AND/OR to control whether all filters are required to match, or if just one filter can match. Use AND NOT/OR NOT to add an additional negation of the filter line, which can be either AND or OR, that is that all filters do not match, or just one filter will not match.

AND_NOT.png

3. NOT Flag

Use the NOT flag to negate the filter line, and only match assets that do NOT match the adapter, field, operator, and value specified.


For example:
image.png
means that Axonius will return all devices EXCEPT the devices where the OS type is Windows (including devices where the OS type is unknown or this field doesn’t apply, and not just devices that their OS type is defined and NOT Windows).

4. Source Drop-Down

The source drop-down contains the following options:

  • Aggregated Data (displayed as ALL)
    • Use Aggregated Data to query on all assets common fields fetched from any of the adapter connections.
    • Aggregated Data is selected by default.
  • Complex Field (displayed as OBJ)
    • Use Complex Field to query on assets with a specific complex field that meets the specified criteria.
      • Example: query on all devices that have installed software that meets the following criteria:
        • Installed Software:Software Name contains 'chrome'.
        • Installed Software:Software Version NOT later than 86.

ComplexEG.png

  • Asset Entity (displayed as ENT)
    • Use Asset Entity to make a query on a specific asset entity, that is, a device or a user entity fetched from a specific adapter connection.

    • Asset Entity is useful if assets in your Axonius environment have been correlated by several different asset entities from the same adapter connection, for example: Amazon Web Services (AWS), Microsoft Azure Active Directory (Azure AD), SolarWinds Network Performance Monitor and Tanium.

      • Example 1: query on all users that were fetched from Amazon Web Services (AWS) with a specific Adapter Connection Label and the Device Type is EC2.

EntEG1.png

  • Example 2: You can also create queries on Complex Fields.

The 'Complex Field’ is available as an option for the second row onwards. You can choose complex fields according to the adapter type selected. For example, you can find devices with an asset entity that has a specific software name and version and that were last seen in the last 7 days.

EntittQueryEx2.png

  • Field Comparison (displayed as CMP)
    • Use Field Comparison to compare between adapter field values, and only return devices which match the comparison.
    • The following field types are supported: String, Enum, Boolean, Numeric and Date.
    • For String, Enum, Boolean, and Numeric fields - Equals operand is supported (String comparison is case-sensitive)
    • For Date fields - Equals, <, >, <days and >days, <hours and >hours operands are supported.
    • When comparing date fields by days, the time is ignored and only the date is compared.
    • The >days and >hours operands let you query if the first date field is later than the second date field by more than the number of days or the number of hours specified.
    • The <days and <hours operands let you query if the first date field is sooner than the second date field by more than the number of days or the number of hours specified.
    • Example: query all devices whose last seen by the Amazon Web Services (AWS) adapter is more than 3 days after their last seen by the Microsoft Active Directory (AD) adapter.

FieldCmp1.png

  • Field Comparison by Preferred Field

    When you select Field Comparison, and then select Aggregated, the Field drop down will then contain preferred values.

PreferedFields.png

5. Adapter Drop-Down

This Adapter drop-down contains a list of all adapters that have fetched data for assets. The adapter that is selected in this drop-down controls the list of fields displayed in the Field drop-down. You can quickly search for adapters using the search bar at the top of this drop-down.

The first entry and default selection in this drop-down is Aggregated represented with the image.png icon. All of the fields for Aggregated are the collated values from all of the data that was fetched for all adapters, considered as common fields.

You can control the adapter data sources that are used when evaluating a filter for a common field. By default, all adapters are selected. If you choose to only evaluate the operator and value query data in a common field from a subset.

The adapter source of an Aggregated Data / common field can be specified. If specified, the image.png icon will be replaced by image.png, to indicate that specific sources have been selected. By default, all adapter sources are queried.

AdapterDropDown.png

6. Field Drop-Down

The field drop-down contains a list of all the fields that have been fetched for a specific adapter. In the case of the Aggregated selection in the Adapter drop-down, this is a list of all fields whose data is collated from all adapters.

  1. Select the common field option (image.png icon, default option) to search any generic field. Common fields are device properties retrieved from multiple adapters.

  2. Click the Adapter drop-down list to select the device property to be queried. Use the search bar to search for a specific field name.

7. Operator Drop-Down

Once a field is selected, you need to select a comparison function from a drop-down list. For each field type there is a list of possible functions:

  • Enumerated – 'equals' function along with a selection of a values from a list of pre-defined values.

  • Text – 'contains', 'equals', 'in', 'starts', 'ends' and 'regex' functions, all refer to a text value to compare to.

    • When choosing 'in', the text value should be a comma-separated list of values.
    • When choosing 'in', the comma (,) character can be escaped by using a double backslash (e.g \\,)
  • Numeric – 'equals', '>' (greater) and '<' (lesser) functions, all refer to a numeric value to compare to.

  • Date – '>' (after), '<' (before),'=' (is) 'last days', 'next days', 'next days from now', 'last hours', 'next hours', 'next hours from now' 'previous month' and 'current month' functions, all refer to a date expression to compare to.

    • Use date expressions as follows:

      • '=', '<' or '>' the value to compare is a chosen calendar date expression.
      • 'last days', 'next days' and 'next days from now' the value to compare to is the relative number of days (integer).
      • 'last hours', 'next hours' and 'next hours from now' the value to compare to is the relative number of hours (integer).
      • ‘previous month’ and ‘current month’ the value to compare to is the previous calendar month, and the current calendar month. For instance, when you are in the month of June (using the UTC time zone) and choose ‘previous month’ the query will either fetch assets which fulfill the query for the month of May, or if you choose NOT, will not fetch assets for the month of May.
  • IP – 'size', 'exists', 'in subnet', 'not in subnet', 'contains', 'regex', 'equals', 'isIPv4' and 'isIPv6' functions, all refer to an IP address value to compare to. For 'in subnet', 'not in subnet', you can specify multiple comma separated subnets.

  • Version - 'contains', 'equals', 'regex', 'earlier than', 'later than' functions, all refer to a version value to compare to.

Note:
The Adapter Connections field has unique actions to filter by the number of the adapters: 'count =', 'count <', 'count >'
Note:
The OS: Distribution field has unique actions to filter devices with Windows or MAC versions lower or higher than the compared version: '<', '>'
Note:
The 'exists' function can be used to filter the existence of any property value and is available for all field types.
Note:
A single query expression using the 'in' function can replace multiple query expressions using the 'equals' function with 'or' operands between them.

8. Value Field

Specify the value to be compared by the field and function. Different relevant value options are enabled according to the field type and the operator/function chosen.

ValueField.png

Autocomplete Suggestions for Equals Values

On certain fields, when the operator is set to equals the value drop down box displays all of the values that exist in the system, so that you will easily be able to formulate a valid query, without having to guess the exact name of the component on your system.
Once you type 3 letters, the system presents possible values for the field.
For instance, for “Installed Software: Software Name” if you are looking for Chrome start typing 'chr', and a drop down box will appear, showing all the installed software with these letters in it.
Enum eg.png

Note:

Up to 100 values can be displayed in the Value drop down list.

9. Parentheses Controls

When defining multiple expressions with a combination of "OR" and "AND" operands between them, usage of parentheses impacts the query definition.

Parentehse controls.png

In some complex expressions, multiple parentheses may be required. To increase/decrease the number of parentheses, click the up/down arrows in the Parentheses Control.

When you use OR NOT and AND NOT , use of parenthesis is mandatory. You have to close the brackets, otherwise the expression will not work.

Adding Field to Column / Removing Field from Column

On each row in the Query Wizard, the Add Field to Column image.png or Remove Field from Column image.png button is present. This button is enabled once a field is selected for the expression.
This button lets you easily add or remove fields as columns in the Devices/Users table directly from the Query Wizard.
A column will be always added as the second column in the table.
The state of this button (Add or Remove) is determined by whether the field already exists as a column in the table.

Duplicating Rows

The Duplicate Row button image.png is displayed on each row in the Query Wizard.

  • Click Duplicate on the first row to duplicate the entire block.
  • Click Duplicate Row on any of the inner rows to duplicate only the selected row.

Disabling / Enabling Query Expression

ToggleEye_Query

Toggle the Eye_icon (Eye) icon to disable/enable the query expression in a row. This gives you the ability to compare the results of a query expression without needing to delete it.

Refining Field Values by Condition

Use Refine Field Values to filter which values appear in the defined column. Sometimes the result of a query returns assets which have more than one value in some of the columns, for instance, installed software. You can create a filter for such columns when you create a query in the query wizard, and define which values will be searched for in these columns. When you click the Refine Field Value button, the device display page shows only the values chosen in the column which is filtered.

To refine a field value, the field you want to filter on must appear in the results table.
Click the Refine Field Value icon columnFiltericon.png. The expression you created is now applied as a refinement rule for the items in the columns selected.
For instance the query below, shows Installed Software of a certain name.

ColumnFilterUpn.png

  • Once you click the Refine Field Value icon, it is displayed as ColumnFitlerIcRemove.png. Click the Remove Filter icon to remove the field value refinement, and display all information about all assets received in the query.
Note:

The refining field values does not affect the data which is fetched, only the display

Note:

Refine Field Value is applicable for device and user queries.

Expression Indicative Error

When an error occurs for a given expression, an indicative text referring to that error appears in red in the bottom of the Query Wizard. Use it to correct the expression.
For example, missing right bracket:
MissingRightBracket_Query

Running the Query

When you complete a filter expression, the contents of the Devices table are refreshed with the results of your query. When you finish building the query expression, click Search. The final query expression is displayed in the Query Filter Bar.

Click Clear to clear all expressions in the wizard, which also clears the saved query, when used, refreshes the Devices table and displays all existing devices.

You can always toggle back to the Query Wizard, change and refine the query, and create a multi-filter expression, by adding AND/OR operators between them:

  • To add a new expression, click image.png
  • To remove an expression, click image.png
  • To reorder the expressions, hover over the expression to use the drag and drop functionality image.png

For each added or removed expression the Devices table results are dynamically updated.

Once you create a query you can save it for future use. Learn more about Saved Queries.

Saving Queries

After you create a new query, or update an existing one, select Save As to save the query and add it to the Saved Queries list for future and advanced use. Saved queries are also used in Enforcement Sets, Dashboard Charts and Reports.

Updating Query Details

Once you configure filters or a Search and saved them as a query, an arrow appears next to Save As.
Click the arrow and select Update Details to change any other query details, such as the name, description, tags and folder where it is saved.

Advanced Querying

Free Text Search in Table

The search bar lets you search free text and filter on devices with or without a predefined 'search by' option. By typing the required search text and pressing Enter, the results filter all entities in the table that contain the given text in any properties.

The available search options are:

  • Free text search - Axonius runs the following search logic on the specified search value:
    • 'Case sensitive exact match' search in any of the selected columns.
    • 'Case insensitive exact match/ start with' search in the following columns:
      • Devices page: Host Name, Network Interfaces: MAC, Device Manufacturer Serial, Last Used Users.
      • Users page: User Name, Email.

FreeTextSearch.png

  • Predefined 'search by' option
    • Click the search bar dropdown button or press the down arrow key to select one of the predefined 'search by' options:
      • Host Name - to run 'contains' search on 'Host Name' only.
      • Last Used Users - to run 'contains' search on 'Last Used Users'.
      • IP Address - to run 'contains' search on 'Network Interfaces: IPs'.
      • Installed Software Name - to run 'contains' search on 'Installed Software: Software Name'.
    • Each 'search by' option consists of a different set of columns, that can be personalized and saved as the user's search default view. For more details, see Editing Table Columns.

Searchby.png

SearchbyHostName.png


All searches are tracked and stored. Click the search bar drop-down button or press the down arrow key to browse the search History and the recent Saved Queries that were run.
Saved Searches.png

Using a Saved Query as a Filtering Condition in Query Wizard Expressions

You can use a saved query to create complex queries based on pre-defined queries. You can only select saved queries whose access is not private.

SavedQueryBased.png



First name must have atleast 2 characters. Numbers and special characters are not allowed.
Last name must have atleast 1 characters. Numbers and special characters are not allowed.
Enter a valid email
Enter a valid password
Your profile has been successfully updated.