There is a distinction between queries and reports:
A query describes a set of information to be obtained from the collected data; for example: “find all clients updating a specific database during weekend hours.”
A report describes how the data returned by a query is presented. Most often, the report is in tabular form, but Guardium provides extensive graphical reporting capabilities as well.
Use the Query Builder to define and modify queries. There is a separate Query Builder for each domain, and it is opened from the Query Finder for that domain (see Open the Query Finder). By default, the Query Builder panel name is Custom Reporting for a user portal, but for admin role users, the Query Builder panel takes its name from the menu selection used to open the query builder (Access Tracking, Exceptions Tracking, Alert Tracking, etc).
The query builder contains three panes:
The Entity List pane on the left-hand side identifies all entities and attributes contained in the domain. Entities are represented as folders, and attributes are the items within. 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 appendix.
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 the Query Fields Overview, below.
The Query Conditions pane specifies any conditions for selecting the fields listed above (for example, “where VERB = UPDATE”). For more information about using this pane, see the Query Conditions Overview, below.
The title line for the query displays the query name and the main entity, and contains two controls:
Once the query has been saved, the Item
comments 
button appears to the left of the query title. Click
the button to add comments to the query definition (see Comments
if you need additional help defining comments).
The Sorted by occurrences checkbox can be marked to indicate that the rows of data returned by the query are to be sorted by occurrences, from most to least.
The entities within a domain are related to one another, usually in a hierarchical order. For example, in the Data Access domain, the Client/Server entity contains Session entities, which contain Access Period entities, and so forth.
The main entity that you select for a query determines:
The level of detail for the report. There will be 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 below the main entity can be counted, but not displayed (since there may be many occurrences for each row).
The total count, added as the last column of the report, which is a count of instances of the main entity included on that row of the report.
The timestamp against which the from-date and to-date run-time parameters will be compared to select the rows of the report. For reports in the access domain, there are three options for those cases where the Session entity is selected as the main entity: Session, Session Start, and Session End. All three of these options select Session as the main entity. When Session is selected, the timestamp used is for the last update made to the Session entity, and when Session Start and Session End are selected, the starting and ending times (respectively) of the session will be used. This may be important for long running sessions (as when pooled sessions are kept open by an application server). For more information about Timestamps, see About Timestamps, later in this chapter.
By default, query data is sorted in ascending order by attribute value, with the sort keys ordered as the attributes appear in the query. If aliases are being used, they 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:
Mark the Order-by box.
Enter a number in the Sort Rank box (1 is the most major sort key).
Optionally, mark the Descend box to sort the values of that attribute in descending sequence.
As mentioned elsewhere, 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 checkbox above the Query Fields title bar.
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 (e.g., 2005-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).
When creating a query in the Group domain, if you include an attribute from the Group Member entity in either the Query Fields or Query Conditions, you must also include an attribute from the Group entity (the Group Description, for example).
The Query Fields pane basically lists the columns of data to be returned by the query.
There are two ways to add a field to the Query Fields pane:
Pop-Up Menu Method:
Click on the field to be added.
Select Add Field from the popup menu.
Drag-and-Drop Method:
Click on the icon to the left of the field (not on the field name).
Drag the icon to the Query Fields list and release it.
Regardless of the method used, the field will be added to the end of the list.
To move a field in the Query Fields pane:
Mark the checkbox in the left-most column for the field.
Use the following buttons to move the field to the desired location:
Click 
to move the field up one row.
Click 
to move the field down one row.
To remove a field from the Query Fields pane:
Mark the checkbox in the left-most column for the field.
Click 
to remove the field.
The Field Mode list selection indicates what to print for the field: its Value, or the Count, Min, Max, or Average (AVG) for the row. The Value option is not available for attributes from entities lower than the main entity in the entity hierarchy for the domain.
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.
The Query Builder allows you to create complex query conditions by adding multiple attributes to the Query Conditions pane. Each attribute added defines an additional test that will be AND'd or OR'd with the other tests, as described below.
To remove a query condition, mark the checkbox in the
row for that condition, and click
in the Query Conditions pane title bar.
To add a condition, first create a row in the Query Conditions list for the appropriate field from the Entity List pane. The way you do this depends on whether you are creating an AND condition or an OR condition.
To add an AND condition, do one of the following:
Click on the field in the Entity List pane and then select Add Condition from the pop-up menu.
Drag the field icon (not the field name) from the Entity List pane, and release it on the Query Conditions pane title bar.
To add an OR condition, do one of the following:
Drag the field icon (not the field name) from the Entity List pane, and release it on top of the condition for which it will be an OR condition.
Mark the checkbox for the condition to which you want to add the OR condition, click on the field in the Entity List pane, and then select Add Condition from the pop-up menu.
Optional. Use the Aggregate drop-down to select an aggregate of the attribute to be used for the query condition: Count, Min (minimum value), Max (maximum value), or AVG (average value). Restrictions apply, as follows:
You cannot use an aggregate in an OR’d condition.
You cannot add an OR’d condition to one that contains an aggregate.
Select the operator for the new condition from the list below. Not every attribute type will have the same set of operators available; for example, attributes that cannot be associated with groups will not have any of the group options (IN GROUP, LIKE GROUP, etc.)
|
Operator |
Description |
|
< |
Less than |
|
< = |
Less than or equal to |
|
< > |
Not equal to |
|
= |
Equal to |
|
> |
Greater than |
|
> = |
Greater than or equal to |
|
CATEGORIZED AS |
Member of a group belonging to the category selected from the drop-down list to the right, which appears when a group operator is selected. |
|
CLASSIFIED AS |
Member of a group belonging to the classification selected from the drop-down list to the right, which appears when a group operator is selected. |
|
IN DYNAMIC GROUP |
Member of a group that will be specified as a runtime parameter. |
|
IN GROUP |
Member of the group selected from the drop-down list to the right, which appears when a group operator is selected. |
|
IS NOT NULL |
Attribute value exists, but may be blank or unprintable |
|
IS NULL |
Empty attribute |
|
IN PERIOD |
For a timestamp only, is within the selected time period |
|
LIKE |
Matches a like value specified in the boxes to the right. A like value uses the percent sign as a wildcard character, and matches all or part of the value. Alphabetic characters are not case sensitive. For example, %tea% would match tea, TeA, tEam, steam. If no percent signs are included, the comparison operation will be an equality operation (=). |
|
LIKE GROUP |
Matches any member of a group that may contain wildcard member names. For example, if the group contained a member named %tea%, it would match tea, TeA, tEam, steam, etc. |
|
NOT IN DYNAMIC GROUP |
Not equal to any member of a group, which will be specified as a runtime parameter |
|
NOT IN GROUP |
Not equal to any member of the specified group |
|
NOT IN PERIOD |
For a timestamp only, not within the selected time period |
|
NOT LIKE |
Not like the specified value (see the description of LIKE, above) |
|
NOT REGEX |
Not matched by the specified regular expression |
|
REGEX |
Matched by the specified regular expression Note:
The Guardium implementation of regular expressions conforms with POSIX
1003.2. The specification can be viewed from: |
For a group operator, select a group from the list that
appears to the right of the operator.
For most other operators, you must supply a value for the condition,
or indicate that a runtime parameter value will be supplied later (when
the query is executed). In these cases, a drop-down with three options
appears to the right of the operator. Do one of the following:
Select Value and enter an exact value in the box to the right.
Select Parameter and enter a name for the runtime parameter in the box to the right (the name must not contain spaces).
Select Attribute and select another attribute to match the selected one (for example, this can be used to test for local traffic by matching the client and server IP addresses).
When you are done adding all conditions, remember to save the definition.
There is a separate Query Builder for each reporting domain, so it is important to open the correct Query Builder. Otherwise, you will not see the information you want! All domains are described in the Domains topic of the Domains, Entities and Attributes Appendix.
After determining which domain to use, do one of the following to open the Query Finder for that domain:
Users with the admin role: Select Tools > Report Building, and then select one of the Query Builders from the menu. The Query Builders all end with the word Tracking (Access Tracking, for example).
All Others: Select Monitor/Audit > Build Reports, and select one of the Query Builder buttons from the left column of the panel.
Either one of these options opens the Query Finder for the selected domain. If not, see the Problems topic, just below. Otherwise return to the top of this topic to begin defining a new query, or modifying an existing one.
If you attempt to open a Query Builder for which your Guardium account is not authorized, you will receive an error message:
Error: You do not have the privileges to run this application.
Check with your Guardium administrator if you receive this message, but believe you should have access to the domain.
If you do not see the Query Builder you want to use, you may need to add it to a custom tab. Not all Query Builders are included on the default user layout. See Portal Customization for instructions on how to add a Query Builder to your layout.
To locate and view a query definition in the Query Builder, there are several options:
Use the Query Finder - see Use the Query Finder, below
From a report portlet based on the query, click 
(Edit this Report's Query) in the tool
bar at the bottom of the report.
If the query is used in a report on your portal, and
you know some portion of the report name, use the 
(Portal
Search) tool, and then open the query as described in the bullet
above. See Search for a Report in the Reports
topic.
From the Customize Portlet panel for a report based
on the query, click (Edit this Query)
beside the query name at the top of the panel.
Open the Query Finder for the appropriate domain (see Open the Query Finder, above).
Optional. If you know the Main Entity for the query, select it from the list.
Click Search.
If there is only one query defined for the selected Main Entity, that query opens immediately in the query definition panel.
If there are multiple queries defined for the selected Main Entity, or if no Main Entity was selected, a list of queries will display in the Query List panel.
If a Main Entity was selected for which no queries have been defined, you will be informed.
Do one of the following:
To open the Query Builder panel for one of the listed queries, click on it.
To define a new query, click New.
Open the Query Finder for the appropriate domain (see Open the Query Finder, above).
Click the New button to open the New Query – Overall Details panel.
Type a unique query name in the Query Name box. Do not include apostrophe characters in the query name.
Select the main entity for the query from the Main Entity list. Remember that the main entity controls the level of detail that will be 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.
Click the Next button. The new query opens in the Query Builder panel. To complete the definition, see one of the following topics:
Open the Query Finder for the appropriate domain (see Open the Query Finder, above).
Use the Query Finder to open the query to be modified.
Refer to the Query Builder Overview topic above to modify any component of the query definition.
Open the Query Finder for the appropriate domain (see Open the Query Finder, above).
Use the Query Finder to open the query to be cloned.
Click the Clone button at the bottom of the panel. The original query name will be replaced by a text box.
Type a unique name for the cloned query in the text box. Do not include apostrophe characters in the query name.
Click the Save button at the bottom of the panel.
To complete the definition, see one of the following topics:
Open the Query Finder for the appropriate domain (see Open the Query Finder, above).
Use the Query Finder to open the query to be removed.
Note: 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).
Click the Remove button near the bottom of the panel. You will be prompted to confirm the action.
Once a query has been defined, there are several options for adding a tabular report based on that query to an existing menu layout, quickly. These options apply only for tabular reports, and those reports can only be added to menu layouts.
Open the Query Finder for the appropriate domain (see Open the Query Finder, above).
Use the Query Finder to open the query to use for the report.
Do one of the following:
To add a tabular report to the end of an existing menu layout, click the Generate and Add to button at the bottom of the panel, navigate to the desired menu layout, and click on it.
To add a tabular report to the My New Reports tab, click the Add to My New Reports button at the bottom of the panel. (If no tabular report portal has been generated yet for the query, you will need to click the Generate Tabular button first.)
Note that the default user portal contains a My New Reports pane, but the default admin portal does not. If your portal does not contain a My New Reports pane (for whatever reason), you will receive an error message. If it does not exist, you can create this pane anywhere on your portal (see Portal Customization, in the Common Tools book). If you create a My New Reports pane, be sure to:
Use the exact spelling shown
Define the pane with a Menu pane layout