asked
What action id's from an Audit Log warrant review
We are performing audits of all production DB servers in the Environment and are capturing transactions that meet the criteria of a set of audit groups that we defined, those logs are then moved to a central audit table (this is purged every 5 days). We currently have 544,593,118 rows in that table. Because of that size, I need to focus my review and reporting on areas/transactions that truly need the attention. Reviewing the entire log is not practical or a good use of someones time. The report I currently run is filtered on several of the "obvious" usual suspects, but this still generates enough rows to "gag a maggot " and the server. I have resorted to doing a random selection of 5% of the total rows that meet the filtered criteria. This is not optimal and potentially misses transactions that should be reviewed, but it is the only current way I have of optimally generating a report. We have started using triggers (starting with failed logins) and dropping the trigger results to a different table, but I am not a big fan of triggers (or the amount that you would need to grab each potential event) and the potential performance impact it could have on the server during normal business. **So the question is, is there a (easier/more optimal)) way to screen the logs in an efficient manner and look for the "anomalies" among the huge pile of data that is in our table? We are not able to spend any money at this time on a commercial application/utility so I am hoping someone out there has an alternative process that I could explore ** I am listing the current code I am using in the report for edification purposes: SELECT Distinct [server_instance_name],[database_name],[object_name],[session_server_Principal_name],a.[action_id],b.[name] AS 'action_name',[class_type],[event_time],[statement] FROM [SQLAudit].[dbo].[SQLAuditLogsII] a (NOLOCK) Join sys.dm_audit_actions b ON a.action_id = b.action_id WHERE (ABS(CAST( (BINARY_CHECKSUM (server_instance_name, NEWID())) as int)) % 100) < 5 AND database_name NOT IN ('master') AND a.action_id IN ('ALCN','ALST','ALTR','AS','AUSC','CO','CR','DR','G','LGIF','RC','RF','SN','SVSD','SVSR','VDST') AND a.server_instance_name In (@server_instance_name) AND datalength(statement) > 1 --order by server_instance_name *** This is using SQL Server 2014, Enterprise edition(64bit) running on a Windows Server 2012R2 VM WITH 16 gig of memory and 4 processors Thank you for your time, support and consideration.
When you are reviewing the 5% result set, what are you looking for? Just wondering how, once you have them filtered down, you specifically identify events of interest. It might just be a matter of coding those criteria into the filter. Another thing about anomalies, is you might not know ahead of time what they would be. Could it make sense to keep a table of known 'good' events, then only select audit events that don't occur in that table. As you come across false positives, you could add those as rows to the 'good' events table to reduce the list of potentially anomalous events. If the action_id list you have is the only thing you can use, I'm not sure how you can reduce the number of transactions to be reviewed.
17 People are following this question.
