If you are auditing a SQL server that is running extended event traces, you may be seeing events come through in SQL Compliance Manager of category DDL and event type “DDL INVALID”. When you see an invalid type, that means it isn’t a type currently listed in our SQLCompliance database. While this is being fixed for future releases, here is how you can fix it now.
Open management studio and connect to the instance housing your SQLCompliance database. This is where SQL CM is installed… Run the following Query
USE SQLcompliance
Insert into [SQLcompliance].[dbo].[EventTypes]
values (233,2, ‘Audit Server Object Management Event’, ‘DDL’,0,1)
This will insert the missing event. Now, restart you compliance manager collection service and any open consoles. You should now see the INVALID turn into Audit Server Object Management Events. That’s it. If you want to know more about this table, read on.
This table contains event definitions for sp_trace_setevent. You can find out about this special procedure and the events type in this MS KB article: msdn.microsoft.com/…/ms186265.aspx
In this table, there are a few options you may find useful in fact for your daily operation of Compliance Manager. Columns include
[evtypeid] : The event type ID as referenced in the above MS BOL article
[evcatid] and [category]: References the event type such as DDL, DML, SELECT etc. Currently there is:
category evcatid
Invalid: -1
Integrity Check: 0
Login: 1
DDL: 2
Security: 3
DML: 4
Select: 5
Admin: 6
Trace: 6
Broker: 8
User Defined: 9
[name]: The display name of the event
[is2005Only]: Binary – 1 = 2005 only event, 0 = any other version of SQL
[isExcludable]: Events type we will exclude from auditing get a 1, everything else gets a 0.
A note on this last column. If for some reason there is an event type you wish to be excluded, or ones you DON’T want to be excluded that currently are, you can modify the value here to change it to your liking.
There you have it.
DISCLAIMER: As usual (I have to say it) any direct modification of tables within our databases will not necessarily be officially supported by Idera Support. Use at your own will.