Using the Query Builder

Use the Query Builder to create or modify queries. Specify the domain you want to query, choose a main entity, then use the Query Builder to define or modify a query.

  1. Open the Query Builder by clicking Comply > Custom Reporting > Custom Query Builder.
  2. Determine the domain you want to query. Select an item from the Domain Finder menu and click Search, or click New new icon to create a custom domain.
  3. Choose an existing query using the filter menus in the Query Finder, or click New to create a new query.
  4. There are three main components to the Query Builder screen:
    • The Entity List pane identifies all entities and attributes contained in the domain. Entities are represented as folders, and attributes are the items within the folders. Click on an entity folder to display its attributes, or click again to hide them. For a description of all entities and attributes, see Entities and Attributes in the Domains, Entities, and Attributes information.
    • The Query Fields pane lists all fields to be accessed, what is to be displayed for that field (its value, a count, minimum, maximum, or average), and the sort order. For more information about using this pane, see Query Fields Overview.
    • The Query Conditions pane specifies any conditions for selecting these fields (for example, where VERB = UPDATE). For more information about using this pane, see Query Conditions Overview.

Creating a Query

  1. Open the Query Builder for the appropriate domain.
  2. Click New to open the New Query – Overall Details panel.
  3. Type a unique query name in the Query Name box. Do not include apostrophe characters in the query name.
  4. Select the main entity for the query from the Main Entity list. Remember that the main entity controls the level of detail that is available for the query, and that it cannot be changed. Basically, each row of data returned by the query will represent a unique instance of the main entity, and a count of occurrences for that instance.
  5. Click Next. The new query opens in the Query Builder panel. To complete the definition, see one of the following topics:
    • Query Builder Overview
    • Modify a Query

Modifying a Query

You cannot modify the Guardium predefined queries, but you can clone a query and modify the clone as needed.

  1. Choose a domain and main entity to open the Query Builder for the query you want to modify.
  2. Click Clone, enter a new name for the query (apostrophes are not allowed), and click Save.
  3. Refer to the Query Builder Overview topic to modify any component of the query definition.

Removing a Query

You cannot remove a query that is being used by some other component. To delete such a query, you must first delete all components that use it (reports or correlation alerts, for example). When attempting to delete a query, the reports and correlation alerts dependent on the query will be listed.

  1. Choose a domain and query to open the Query Builder for the query you want to delete.
  2. Click Delete.

Query Fields Overview

The Query Fields pane lists the columns of data to be returned by the query.

The Field Mode menus indicate what to print for the field: its Value, Count (number of distinct values), Min, Max, Average (AVG) or Sum for the row. The Value selection is not available for attributes from entities greater than the main entity in the entity hierarchy for the domain.

There are two ways to add a field to the Query Fields pane:

  • Pop-Up Menu Method:
    1. From the Entity List, click on the field to be added.
    2. Select Add Field from the pop-up menu.
  • Drag-and-Drop Method:
    1. From the Entity List, click on the icon of the field name (not on the field name itself), drag the icon to the Query Fields pane and release it.

When a field is added, it will be added to the end of the list.

To move a field up or down in the Query Fields pane, check the field's check box and click the Up or Down icons to move the field up or down one row.

A Caution about Full SQL Attributes in Queries

Beware of using the Full SQL attribute in a query. It may produce excessively large reports, because each distinct value of the attribute (the complete SQL query string in this case) will be returned in a separate row.

On the other hand, the report may contain no information at all, or many blank columns where you are expecting Full SQL strings. Guardium captures Full SQL only when directed to do so by policy rules - and the rules may not have been triggered during the reporting period.

Do not confuse the Full SQL attribute with the ability to drill down to the SQL for most queries in the Data Access domain having anything to do with SQL requests.

Groups of Types other than Types defined in Attribute

Validation on group type is often restrictive. See Groups Overview for examples of Group Types. Using Query Conditions, Query Builder, a group of types other than the type defined for the attribute in the group condition is permitted. These additional choices are only for the operators IN GROUP and IN DYNAMIC GROUP. The selection of types other than the type defined for the condition is performed in the Run-time parameter of the tabular report.

  1. Create a group in the Group Builder by clicking Setup > Tools & Views > Group Builder. Specify a Group Name and choose OBJECTS for Group Type.
  2. Create an Access report in the Report Builder by clicking Setup > Reports > Report Builder.
  3. Specify a query name and click on the OBJECT folder from the Entity List in order to see more choices.
  4. Highlight Object Name and click once in order to get the ADD CONDITION choice. Click Add Condition so that a line is added to the Query conditions section in the main body of the menu screen.  
  5. Go to the drop-down selection next to the attribute Object name and choose, in the Operator column, IN GROUP or IN DYNAMIC GROUP. In the second drop-down selection (Run-time Parameter column), choose the group that you created in step 1.
  6. Save your work. Click Generate Tabular and then click Add to My New Reports.
  7. Go to the My New Reports tab and highlight the report you created.
  8. Click Customize next to the report name. This opens a tab called Customize Portlet (Run-time Parameters).
  9. Open up the drop-down selection and the groups of the type corresponding to the entity being tested will appear at the beginning of the list, then a double dash line, and then the rest of the groups. This is where different groups can be selected.
  10. Save your work by clicking Update.

Table 1. Buttons
Buttons Steps
  1. Select the query to be deleted.
  2. Click Delete.
  1. Select the query to be cloned.
  2. Click the Clone.
  3. Enter a new name for the cloned query.

Assigning roles to reports while in the Query Builder only assigns the role to the Query, not the report. Assign roles to reports in Report Builder. See Reports.


Click Save when you have finished all the tasks required on the menu screen.


Move back between menu screens of a multi-screen Guardium task or function using the Back button. The back arrow in the web browser does not work for navigation between menu screens.

Set as Data Mart

A Data Mart is a subset of a Data Warehouse. A Data Warehouse aggregates and organizes the data in a generic fashion that can be used later for analysis and reports.