SQL Server Auditing: A Learning Series Part Nine, Reporting

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.
rptwizquery

Figure 1 – Report Wizard Query String

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

Advertisements

About SQLGator

Florida Gator and Microsoft Data Platform MVP, Star Wars fanatic and is there anything else...oh yeah PS4! I am a geek and SQL Server DBA who dabbles with VMWare, there are other technologies greater than these? Not so fast my friend! I also love to travel to new and exotic places.

Posted on January 29, 2013, in Auditing and tagged , , , . Bookmark the permalink. 1 Comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: