A recent discussion got me to thinking about Auditing. To be honest, it got started with a complaint about some documentation that seemed overly light about the various fields related to auditing as it stands in SQL Server.
In talking to the person who raised the valid concern about the lack of good documentation, I was curious why he suddenly had so many questions about auditing and its functionality within SQL Server. Reflecting on the answers, it seems that it made good sense and it all kind of fell into place with the whole Audit Life Cycle. I hadn’t previously considered the Life Cycle, but it makes sense to map it out. Here is a simple rendition of what an audit Life Cycle might entail.
In order to audit anything, it is necessary to know what you really want to audit, why you want to audit it and how to achieve those goals within the tools given to you. In that vein, it makes sense that one would need to study up on the topic to figure out what different things meant within the tool.
Of course, once you start collecting that data, then you also need to figure out how to measure it and then to determine if adjustments to the auditing plan need to be made. In the end, it boils down to what is the data to be collected, what are you doing with that data and what does that data represent.
In our simple discussion, the data trying to be understood was related to the Event Subclass field in this View (sys.trace_subclass_values) and in this Microsoft document (one of several). The beauty of this field is that it is not just tied to Auditing, but you will also find it in Profiler, server side traces, and Extended Events.
With so little information to help understand what the field values represent, maybe it is better to just turn to the data to help understand what the values might represent or how to interpret them. To do this, we can query a few catalog views as in the following query.
[codesyntax lang=”tsql”]
DECLARE @ParentEvent VARCHAR(20) = '%Audit%'; SELECT TE.name AS event_name, TSV.subclass_name,TSV.subclass_value FROM sys.trace_events AS TE INNER JOIN sys.trace_subclass_values AS TSV ON TE.trace_event_id = TSV.trace_event_id INNER JOIN sys.trace_columns AS TC ON TSV.trace_column_id = TC.trace_column_id AND TC.name = 'EventSubClass' WHERE TE.name LIKE @ParentEvent ORDER BY event_name;
[/codesyntax]
With the above query, I can filter down to just the Event Types that have Audit in the name. Or I could add a different filter so I can try and better understand the different subclasses in a more focused effort.
I hope this helps in your efforts to provide a better auditing or “profiling” type of experience in your environment.
