Custom Report Edit Page:
Data Source
Tab:
Query
Tab

When you define a resource query for a custom report or filter, you need to build a resource query on the
Query
tab.
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.
Options on the
Query
tab
Option
Description
Resource Type
Lets you change the base 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
.
Alias
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.
Scoped
Specifies whether or not the query results are scoped according to the role of the user that runs the query.
Use Fields & 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.
Use Resource Type Associations
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
.
Create Joins
Lets you add joins to the query. A join combines records from two different CMDB tables.
The
Joins
drop-down list lets you select the join type:
  • Cross
    Returns the cartesian product of the sets of records from the two joined tables.
  • FullOuter
    Combines the results of both left and right outer joins. The joined table contains all records from both tables, and fills in NULL values for any missing matches on either side.
  • Inner
    Requires that each record in the two joined tables has a matching record.
  • LeftOuter
    A join between two tables (A and B) such that the joined table always contains all records of the "left" table (A), even if the join-condition does not find any matching record in the "right" table (B).
  • RightOuter
    The reverse of the left outer join. Every record from the "right" table (B) appears in the joined table at least once.
The next drop-down list lets you select the database table that you want to use. The
On
drop-down list lets you select the column that you want to join.
Add Parameters
Opens the Query Parameters tab, which lets you specify the parameters that you want to use in the resource query. A parameter is a variable that is included in the query.
Add Filter Expressions
Opens the Filter Expressions tab.