Getting Data In

reading SQL Server 2008 log files for usernames, queries and stored procedures

Engager

Hi For our auditors we are looking into products to view transaction logs on our SQL Server 2008 database.

I've been looking around splunks website, faq's and testing the product itself and I'm not having any luck when trying to: 1. find a query, identify its user and when they added or updated the record 2. if a stored procedure was used report that also

Any experiences, PDFs, URls, etc would really be appreciated

George

Tags (3)
Highlighted

Re: reading SQL Server 2008 log files for usernames, queries and stored procedures

SplunkTrust
SplunkTrust

For most database engines, the "transaction logs" (redo logs, etc) are binary files with a less than well-documented format. Also, normally, the transaction logs are not structured in a way that actually describes the update in SQL-like syntax - it usually stores a binary image of the 'before' versions of the rows changed, so that during rollforward recovery it has easily usable data.

If you can find some tool that can render the database's transaction logs into a text format, this might be possible to index into Splunk. Without it, this is going to be difficult. This problem is of course not Splunk-specific, as it's a consequence of how the database engine works.

For DB/2 there is "recovery expert" that can take the transaction logs and parse them to re-create SQL statements. I know of no such tool for MSSQL, but one probably exists somewhere.

Highlighted

Re: reading SQL Server 2008 log files for usernames, queries and stored procedures

Motivator

As Dwaddle noted, transaction logs in MS SQL are binary, so indexing the actual log files will get you nowhere. You could however set up a scripted input into Splunk that runs a variation of DBCC LOG (<database>, <type>) and then index the output of that query in Splunk.

For some info on DBCC LOG see this site: http://www.mssqlcity.com/Articles/Undoc/SQL2000UndocDBCC.htm#part_2_8

Now I don't think you can actually filter on DBCC LOG so you probably have to put the results in a temp table and then work your mojo with them. I am also not sure how much extra load running this frequently would put on your system. Maybe you could just run it once a night, do your filters, and then index the results. Might be good enough for your auditors.

You could also use triggers or other SQL internal mechanisms to fire alerts.

I for one would sit down with my auditors and try to figure out why they want me to monitor the tranny logs and see if we couldn't agree on a little more efficient way of achieving what they want. For example indexing the web logs, or adding better logging to whatever custom apps hit your sensitive tables. Also restricting access as much as possible to your sensitive dbs/tables is a good way to go.