SQL Server Audit Log Reporting
There's no built-in audit log reporting in SQL Server. (See Recommended Reports for SQL Server via LOGbinder for SQL Server.)
However you may consider creating your own reports with SQL Reporting Services if
you configure your SQL Audit to use binary log files instead of the Windows event
log. (More
reasons to use binary log files.)
To access a SQL audit log you must use the fn_get_audit_file() system stored procedure
which opens the audit log file and returns a result set based upon it. You can perform
SQL Selects against the audit log result set to find the events of interest to you.
Below is an example:
Before attempting to building reports by querying SQL audit logs consider the following
issues:
- Performance: - There are no indexes available on SQL audit log
files.
- Security - Commonly accepted security best practice mandates that
audit logs be moved from the system where they are created to a different system
to protect the integrity of the audit trail against intruders or malicious admins.
Note that you can access SQL audit log files from a different SQL server and while
only premium editions of SQL server can generate audit logs, all versions of SQL
server can read them.
- Learning Curve - As you can see from the screen print above, the
SQL audit log is very cryptic and requires a strong knowledge of SQL and the SQL
auditing system in particular to decode and interpret SQL audit events.
Instead of trying to manage SQL audit logs manually and build reports based on them,
I believe SQL audit logs belong in your SIEM/log management solution where you can
take advantage of centralized collection, secure archival and built-in alerting
and reporting - not to mention correlation.
You might consider sending SQL audit logs directly to the Windows event log since
your log management/SIEM can probably collect them from there. But remember that
all of the hundreds of different operations that can be performed in SQL Server
are written to
just one event ID with the same message format for all actions.
To allow SQL audit logs to be managed by your existing log management/SEIM and to
obtain easy to understand, clearly formulated audit events I designed
LOGbinder for SQL Server.