Aggregate Report

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

ApexSQL Audit Viewer works only with the database where there is an Audit architecture.

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.

faq.jpg  FAQ: How can I build an Aggregate Report?

First select Aggregate Report and then set your filter criteria. Then click Filter.jpg button. For information about filter criteria, click here.

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.

image25.jpg

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

see_also.jpg See Also:

For more information about the context menu commands click here.

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 Table Name, Table Schema, 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:

image27_2.jpg

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:

 image27_1.jpg

For Date, you have 4 additional choices indicating the time based grouping that you would prefer.

image15.gif

To conclude this example, if you choose Day you would see all of the above information but grouped again by the hour they occurred.

image27.jpg

Expression: By clicking image159.gif, this will open a Edit Where SQL window which allows you to edit your search criteria.

image24.jpg

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 Grouping picklist is a powerful feature for creating database usage trend lines as you can see if traffic is increasing by hour, 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:

image26.jpg