Using the Query-Report Builder

If the predefined reports do not meet your needs, create a query from scratch, or clone and modify an existing query.

Before you start writing a query, plan carefully what you want the report to describe. There are two general use cases:
  • You want to identify a specific occurrence in your system. This type of query has two parts. An event, or events, occurred in your system: this is defined by the conditions. What do you want to know about the system when this event occurred? These are the details (columns) presented in the report.
  • You want status on some part of your system. In this case, you probably only need to specify the columns you want in the report.

First find the domain that covers the data you want. A domain contains a set of data related to a specific function or purpose, for example: data access, exceptions, policy violations. Each query is based on, and returns data from, one domain. For a description of all domains, see Entities and Attributes in the domains. Each domain has one or more entities, which are groupings of attributes. Attributes are the fields that can be used as columns in the report. Some entities are included in more than one domain, to give you access to the relevant data. For example, the Session Entity is in both the Access domain and the Exceptions domain.

Data is tracking who did what, and when. Some details are static, some are dynamic. The entities, within the domain from top to bottom in the UI, start with static details, followed by non-static details. For example, in the Access domain, the first entity is client/server. Each client/server pair is saved once. The next entity is Session. The client/server pair has multiple sessions with non-static details, for example, session start and inactive flag. This creates a one-to-many relationship between the client/server and the sessions. To see each value of session start, you would need multiple rows. Instead of making your report unnecessarily long with each session start, you can use the count option to show the number of occurrences of session starts, and then drill down to see a more detailed report. The rule of thumb is: the higher up your main entity is in the GUI, the fewer rows and values you'll have in your report. The report is more manageable, and you can always drill down to see more details. For more information on how domains function in a query, and optimizing queries, see Optimizing queries.

If you need details from two entities that are not included in one domain, you can create a custom domain (see Custom Domains).

A query returns data from one domain only. When the query is defined, one entity within that domain is designated as the main entity of the query. Each row of data returned by a query contains a count of occurrences of the main entity matching the values returned for the selected attributes, for the requested time period. This allows for the creation of two-dimensional reports from entities that do not have a one-to-one relationship.

Once you identify the domain, check the predefined reports in that domain to see if there is one close to what you want. If yes, you can clone and modify it. If not, create the query from scratch.

Define the report data (columns). You can choose columns from all the entities in the domain.

Optionally, define the conditions. Conditions, if you use them, are triggers for including the specific data in the report. A query on status, for example, does not need a trigger; you simply want to know the status of an element in your system. On the other hand, if you want to identify specific actions by a specific user on a group of databases, these are the query conditions. The conditions use the attributes in the domain, with operators. There is no intrinsic relationship between the conditions and the report columns. You can choose to add the attributes in the conditions as columns, or not.

You can also optionally defined build expressions or Having clauses on SQL statements.

Reports are the presentation of that data, created when you save your query, with the same name as the query. Default reports are tabular reports that reflects the structure of the query, with each attribute displayed in a separate column. All runtime parameters and presentation components of a tabular report can be customized.

The query builder has six rows for configuring the various aspects of the query, explained in the following sections.

There are buttons at the bottom of the Query-Report Builder page:
  • Add to Dashboard: click to add the displayed report to a defined dashboard
  • Add to My Custom Reports: Click to add to Reports > My Custom Reports
  • Query Summary: Click to open a textual summary of the query

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.