Applies to:
ApexSQL Trigger
Summary:
This article describes auditing reports in ApexSQL Trigger.
Description:
One of the key features of ApexSQL Trigger is creating auditing reports. This feature can be used to create detailed reports containing all changes made to audited objects.
ApexSQL Trigger has three report types, Standard, Aggregate and DDL.
Standard reporting
Standard reporting provides a direct way to view data (audited by DML changes) stored in the audit log. The data is transactional and not row based, therefore each cell – not database row – makes a row in the standard report. This is done to provide a granular, before and after analysis of the effect of the audited action.
The first step to create a report is to create a filter, if needed. After setting up values in filter fields click on the Apply button to generate a preview of all the changes made for the selected table.
To create an export from a report click the Export button and select the desired file format.
The following is an example of a PDF export:
Grouping of data by any column on the grid can be designed, by dragging the column header into the design box, for even deeper analysis. For example, if the data is grouped by table, primary key and modified date, the audit data is actually grouped as whole row of data affected
By using custom filter reporting data can be filtered further. To create a custom filter click on the the Edit button in the right corner of the Custom filter line
Aggregate reporting
Aggregate reporting was introduced to support additional views of the audit data (audited by DML changes), above and beyond simply querying of the audit log table
Unlike standard reporting which is field level based, aggregate reporting is transaction based. For example, where an insert could generate 10 rows in the standard table, one row for each field affected, it can only generate a maximum of 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 in the # field, the left most field in the results grid
The Grouping criteria pane is available for the aggregate reporting only. It allows further report filtering, in order to make a specific type reports
An example Aggregate report might be database traffic type reports, how many transactions by day or how many transactions by user by day. Development noise can be eliminated by limiting the report to show only applications for which we need the usage levels. By analyzing these traffic patterns database hotspots can be found and these areas can be scrutinized for deadlock or record collision problems
Using Group by date is a feature for creating database usage trend lines. It can be seen if the traffic is increasing by hour, day, month and even year over a specific time period.
DDL reporting
DDL reporting provides a direct way to view data (audited by DDL changes) stored in the audit log. The data is transactional and not row based, therefore each cell – not database row – makes a row in the DDL report. This is done to provide a granular, before and after analysis of the effect of the audited action
