The Query tab lets you build a resource query. The standard template includes a base query that selects all resources. You can use the Query Builder to refine the base query using the building blocks provided. You do not need to understand or write any SQL code.
The left panel of the Query Builder shows the structure of the query. The right panel lets you add and modify items in the query structure. The Query Builder lets you build the query from the resource structure (the resource classes, types, and associations) that is defined in the CMDB. The resource query is converted into SQL code in the background. You can view the SQL code in the Resolved Query tab.
To build a resource query for a custom report or filter
In the Reports or Filter page, on the Query tab, in the left panel, select the Base Query.
In the Report page the Query tab is on the Data Source tab.
(Optional) If you want to change the base resource type, in the right panel, in the Base Resource Type drop-down list, select the appropriate resource type.
If you created the report as a Computer Report or User Report, the base resource type is automatically set to computer or user.
(Optional) If you want to change the default settings, specify the following:
Returns only the unique rows when there is more than one row for a particular resource.
Specifies how many table rows are returned. You can specify the number of rows, or a percentage of the table.
The default value is ALL, which returns the entire table.
This setting is relevant only to report queries. It is not used in filter queries. A filter query always returns the entire table regardless of the setting that you specify here.
In the left panel, select the base resource type.
(Optional) In the right panel, specify the following:
The resource type alias. The alias lets you substitute a different name for the table name. For example, when the same table is used multiple times in joins.
Specifies whether or not the query results are scoped according to the role of the user that runs the query.
Use the Query Builder to build the query to extract the resources that you want. You can do the following:
Add Fields and Data Class Attributes
Lets you add fields to the query. You can select fields from the data class (such as Resource.Name or Resource.CreatedDate). Alternatively, you can select fields from other tables (for example, the Domain field from the Inv_AeX_AC_Identification table).
The Fields tab shows the fields that are defined in the query result set, and lets you configure their appearance in the result grid.
Lets you add resource associations to the query. These are predefined associations between two types of Notification Server resources (such as the Computer User association that links a computer to a user).
You can view the list of available predefined resource type associations under the Settings folder in the left pane. For example, click Settings > Notification Server > Resource and Data Class Settings > Resource Associations.