Queries
Use one of the many predefined queries that come with Guardium to get information about your data. Use the Query Builder to work with queries.
Use queries to ask questions of your data such as, what are all the clients updating a specific database during weekend hours?
Queries are different from reports. A query describes a set of data, whereas a report describes how the data returned by a query is presented.
Once a query is completed, present the results of the query using reports. Reports usually are presented in tabular form, but you can customize the layout of a report as you like.
To use queries, open the Query Builder by clicking . Choose a domain to query, select a main entity, and then use the query as needed.
You cannot modify the predefined queries, but you can create a clone of a query and modify the clone.
The Main Entity
The main entity that you select for a query determines the following:
- The level of detail for the report. There is one row of data for each occurrence of the main entity included in the report. The location of the main entity within the hierarchy of entities is important in terms of what values can be displayed. The attributes for any entities under the main entity can be counted, but not displayed (since there might be many occurrences for each row). To choose this level of detail, check the Sort by Count check box.
- The total count is a count of instances of the main entity included on that row of the report, added as the last column of the report. To add or drop the count column of the report, click the Add Count check box. This can result in the query/report performance boost in some cases.
- To add or drop the ability to display one-row-per-value in the report, (which can result in the query/report performance boost in some cases), click the Add Distinct check box. This selection yields condensed reports.
- Partition optimization is enabled by default and improves query performance with partitioned database tables. On Guardium V10.1.2 and later, this feature can be disabled by deselecting the Partition optimization check box. Partition optimization should not be disabled without the direction of IBM support.
- The time fields against which the Period From and Period
To runtime parameters are compared to select the rows of
the report. The Query Builder uses the main entity
(among other parameters) to determine which time fields are used when
defining the Period From and Period
To values. This can be important for long-running sessions,
such as when pooled sessions are kept open by an application server.
When applicable, the Period Start/Period End from the Access Period
entity is used, in other cases it will choose period values according
to the main entity:
- Session - the time stamp used is for the last update that is made to the session entity
- Session Start - the starting time of the session entity is used
- Session End - the ending time of the session entity is used
- Full SQL - time stamp from Full SQL domain; query includes rows from the Full SQL domain even if not linked to values (for example - when Log Full Details is set, there are no values)
- Full SQL Values - time stamp from the Full SQL domain; query includes rows only if they have values from the Full SQL domain even if not linked to the Field domain
- Field SQL Values - time stamp from the Full SQL domain; query includes rows only if they have values from the Full SQL domain and they are linked to the Field domain
- In the Main Entity screen is the selection Run in Two
Stages.
Use this selection for two-stage execution for Audit tasks of type report.
This applies to reports on queries on specific tables only. This two-stage mechanism applies to running queries as audit processes with columns and conditions only on the following entities: Access (client/server), Session, Access Period, Construct (SQL), Object, and Sentence (Command).
This two-stage mechanism is not used if the query contains a condition with the Like Group operator or any alias-related operator (such as In Aliases Group) or the condition uses Having.
In addition to using the query builder, each query can be set to run in two stages. By default queries run using the old method. In order for a query to run in two stages, a flag must be set in the query builder. In addition, this method of running queries can be disabled (system-wide) to make all audit tasks use the old method by creating the file: /var/log/guard/DontRunInTwoStages. Existence of this file indicates that the new two stages method should NOT be used.Note: Fields containing tuples (combined fields) in the Two Stages execution is not supported in this release.
Sorting
By default, query data is sorted in ascending order by attribute value, with the sort keys ordered as the attributes appear in the query. Aliases are ignored for sorting purposes. The actual data values are always used for sorting. Attributes for which values are computed by the query (Count, Min, Max, or Avg) cannot be sorted.
To change the default sort order:
- Check the Order-by check box.
- Enter a number for Sort Rank (1 is the most major sort key).
- Optionally, check the Descend check box to sort the values of that attribute in descending sequence.
The last column of a tabular report is a count of main entity occurrences. To sort on this count in descending sequence (in other words, listing the greatest number occurrences first), mark the Sorted by occurrences check box.
Timestamps
A timestamp (lowercase t) is a data type containing a combined date-and-time value, which when printed displays in the format yyyy-mm-dd hh:mm:ss (for example, 2012-07-17 15:40:25). When creating or editing a query, most attributes with a timestamp data type display with a clock icon in the Entity List panel.
A Timestamp (uppercase T) is an attribute defined in many entity types, containing the time that the entity was last updated. For many timestamp attributes, you can print the date, time, weekday or year components separately, by referencing additional Timestamp attributes (Date, Time Weekday, or Year).