Continuing our learning series on auditing, today we look at the next logical step:  Reporting.  We have created audits for the server and for the database as well as being able to query the audits, but what good is an audit if you cannot print a report and hand it to the auditors?

Having been a developer and admin in my past life, you may find it strange that I have never before today created a SQL Server Reporting Services report.  Now don’t get me wrong, I have written a boat load of reports in many other formats (crystal, web, actuate, etc) but have never really had the opportunity or the pleasure to write in my preferred environment.

Given the following function, I thought reporting would be a little more difficult:

SELECT * FROM fn_get_audit_file (‘g:\auditing\*’, DEFAULT, DEFAULT)

This arguments for this function are (file_pattern, initial_file_name, audit_file_offset).  The file_pattern argument must include a path and a filename but can include a wildcard.    The initial_file_name argument must specify the path and name of a specific file in the file set to start reading the records from or the DEFAULT|NULL value.  The audit_file_offset argument will beginning reading the first record after the specified offset (which is a field that can be queried with the function).

Now on to the report….

1. Open Visual Studio and choose  File –> New Project.
2. Select Report Server Project Wizard and name the project appropriately.
3. Enter your connection information and credentials.
4. When prompted to design the query enter the query above as shown in Figure 1.
5. Select additional reporting options as your preferences allow and then save the report.

Figure 1 – Report Wizard Query String

This concludes our series on auditing, I hope you found it helpful!