Aggregate Reporting

Aggregate reporting was introduced to support value added analysis of the audit data, above and beyond simply querying the Audit_log table.

Unlike Standard Reporting which is Field level based, Aggregate reporting is Transaction based. For example, where an insert could generate 4 rows in the Standard table, one row for each field affected, it can only ever generate a maximum one row in Aggregate reporting.

In Aggregate reporting, though, transactions are always grouped rather than just displayed individually and the Count of the transactions is displayed as the # field, left most in the results grid.

To initiate the report generating process hit button in the Report interface area. To reset all custom filter selections in the current Report, press button.

Note: You should get data by the button before you can make any report. The Data Report command (and other report commands) displays a report based on data which has already been queried and shown in the bottom grid.

Fields to display and Group by: In the group by section, you are presented with a list of fields to select from which currently include Database, Table Name, Modified By, Action, Computer, Application and Date. Selecting Table_Name for example will display the Table name in the report results and will also group by Table Name. So, if you simply choose Table_Name and no other field, your audit report would display the number of Audit Transactions that have occurred for each table in the database. See the following example.

If you then added Action_ID, you would see the number of Audit transactions grouped by the Type of Audit Action (Insert, Update or Delete) for every table in the database. See the following example:

For Date, you have 4 additional choices indicating the time based grouping that you would prefer. To conclude this example, if you choose Hour you would see all of the above information but grouped again by the hour they occurred.

Criteria: This section functions exactly as the filter in Standard Reporting, in that it adds a "Where" criteria to the queries to filter the results.

Putting it All together:

Some common Aggregate reports would be database traffic type reports, how many transactions by day or how many transactions by user by day. You can eliminate development "Noise" by selecting only certain applications like client applications you are trying to determine usage levels on and eliminating Query-Analyzer. You could look for database hotspots by analyzing these traffic patterns and scrutinizing these areas for deadlock or record collision problems.

Using Dates to group by is a powerful feature for creating database usage trend lines as you can see if traffic is increasing by day, month and even year over a specific time period.

Aggregate reporting allows more powerful tracking of individual usage patterns for error diagnostics or to track missing/bad data to the source. The latter example can be helpful in sorting out training issues if data ownership isn't enforced by the application or database already.

The image below is an example of Data Report output:

Custom Filter

Expression: Clicking will open a Edit Where SQL window which allows you to edit your search criteria.