- 20 Aug 2023
- 17 Minutes to read
- Print
- DarkLight
- PDF
Creating Queries with the Query Wizard
- Updated on 20 Aug 2023
- 17 Minutes to read
- Print
- DarkLight
- PDF
You can create and save queries in the following modes:
- Create queries on assets using the Query Wizard
- Create queries on Activity logs, Fetch History, and Asset Investigation 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 were 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.
Working with the Query Wizard
From the relevant assets page, click Query Wizard on the top right corner above the asset table. The Query Wizard definition box opens. The Query Wizard presents options appropriate to the selected asset type.
On the Vulnerabilities and Software pages, the Query Wizard is multi-level to provide vulnerability and software contextualization to help inform prioritization, refer to Creating Queries on Vulnerabilities and Creating Queries on Software.
Each row in the Query Wizard is a filter expression. You need to define the following elements:
- WHERE/WHERE NOT switch
- AND / OR/ AND NOT/ OR NOT switch
- NOT Flag
- Source drop-down
- Adapter drop-down
- Field drop-down
- Operator drop-down
- Value field
- Bracket controls
Once you create a query you can save it for future use. Learn more about Saved Queries and adding multiple values from an external file.
For Devices queries you can use the AI Query Assistant if it is enabled on your system.
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.
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:
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 asset 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.
- Example: query on all devices that have installed software that meets the following criteria:
- Use Complex Field to query on assets with a specific complex field that meets the specified criteria.
- Asset Entity (displayed as ENT)
Use Asset Entity to make a query on a specific asset entity, that is, an asset 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.
- 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.
- Field Comparison (displayed as CMP)
- Use Field Comparison to compare between adapter field values, and only return assets which match the comparison.
- The following field types are supported: Enum, Boolean, Numeric, Date, and List.
- 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.
Field Comparison by Aggregated Values
When you select Field Comparison, and then select Aggregated, the Field drop-down allows you to select either by a preferred value, or by Latest Used User Email, Total CVE Count (high, low etc), First Seen or Last Seen.
4. Field Comparison by List Field
You can compare a list of values to receive an exact or partial match. For example, if you want to compare an Asset Name between devices or compare lists of IP addresses.
Use either the ‘in’, ‘contains’, or ‘equals’ operator to obtain the desired result.
- Using the 'in' operator
When you select Field Comparison by adapter and compare between the list of values in the top and lower rows of the query using the in operator, the mutual values from the top row will appear in the results.
For example, if the device list in the top row contains Public IPs and the lower row contains a device containing Network Interface IPs, the results are the devices with the common IPs.
If NOT is selected, and if the top row contains a device with list values of 10.0.0.1, 10.0.0.2, and 10.0.0.3
and the lower row contains a device with list values of 10.0.0.1, 10.0.0.2, 10.0.0.3, and 10.0.0.4
the resulting devices displayed are all devices with values that aren't mutual between the top row and lower row. For example, 10.0.0.4 is returned.
The fields in the lower row are available by the top row's field type and operator. For example, comparing a single value with another single value by using the equals operator won't offer lists to compare. If you use the in operator, the list fields are available to compare.
List field values using ‘in’ or ‘equals’ are case-sensitive.
Using the 'contains' operator
Use Field Comparison with the contains operator to compare mutual values that aren't case-sensitive, such as email addresses or partial matches between lists of IPs.
- Using the 'equals' operator
Use Field Comparison with the equals operator to compare lists of case-sensitive values with exact matches.
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 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 icon will be replaced by
, to indicate that specific sources have been selected. By default, all adapter sources are queried.
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.
Select the common field option (
icon, default option) to search any generic field. Common fields are asset properties retrieved from multiple adapters.
Click the Adapter drop-down list to select the property on the asset 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.
- In
- Two sorts of In values are available. regular in or in (plain text) For more information, see Adding Multiple Values to Query Expressions. The system limits the number of assets fetched using the 'in' operator. When the limit is exceeded, an error message is displayed.
- In
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.
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.
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.
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.
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 NO, 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 or Remove Field from Column
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 asset 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 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
Toggle the (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.
Refine Field Values
You can either refine the field values by condition or by Asset Entity
Click the Refine Field Value icon, the Refine values menus appears. Select either Refine value by condition or Refine asset entities by condition.
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 value by condition button, the asset display page shows only the values chosen in the column which is filtered.
Once you choose a field, the value is automatically added to the assets page, and saved as part of the saved query, when you save it.
To refine a field value, the field you want to filter on must appear in the results table.
Click the Refine Field Value icon . 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.
Refine Asset Entities by Condition
You can use refine asset entities by condition to set the query to refine the contents of the asset entity that are displayed according to the rows, for instance, you can hide a row, an asset entity, which contains a Software Version with a specific value. Once you choose a field, the field is automatically added as a column to the assets page and saved as part of the saved query, when you save it. The query result will only show the asset entities that have this field with this value. For instance, if you select Host name that contains Win, the query result will only show rows that contain this hostname, only the rows that contain the fields and values defined in the query.
It is possible to select both Refine value by condition or Refine asset entities by condition.
- Once you click the Refine Field Value icon, it is displayed as
. Click the Remove Filter icon to remove the field value refinement, and display all information about all assets received in the query.
The refining field values does not affect the data which is fetched, only the display.
Refine by All Values
You can use Refine by All Fields to refine all the conditions in the Query Wizard using a single button, without having to configure a specific data refinement for each row. All columns in the query are automatically added to the asset table, and saved as part of the query when you save it. When you select Refine all Fields, you can select whether to refine the values by condition, or by asset entities, or both and all the columns are added to the table, and the data is refined for all of the rows in the query in one go.
When you select Clear all refinements, the filters set are removed, but any columns added to the Assets table remain, and are saved as part of the query.
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:
Running the Query
When you complete a filter expression, the contents of the asset 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 asset 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
- To remove an expression, click
- To reorder the expressions, hover over the expression to use the drag and drop functionality
For each added or removed expression the Devices/Users 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, Update Details is available as a drop-down from Save As.
Click 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 assets 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 for Devices and Users:
- Devices page: Host Name, Network Interfaces: MAC, Device Manufacturer Serial, Last Used Users.
- Users page: User Name, Email.
- 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.
- Click the search bar dropdown button or press the down arrow key to select one of the predefined 'search by' options:
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.
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.