query the SQL SERVER Audit output

v2008R2, I recently implemented a SQL Server Audit, designed to monitor object changes, as well as server/database level setting changes.  It's been in place for about a week, and I like what I am seeing -- but, I am having a few problems reading the data.

The audit dumps the sqlaudit file to disk, and I upload it once daily to a table.  I then have a little reporting procedure which looks for the new audit events, and sends a summary email to me, briefly noting the changes.  All of the audit events are being captured correctly in the files, but my reporting procedure isn't returning them all to me.  Also, some of the events in the file are being loaded to the table in chunks, rather than in a single records.  For example, one record will have the first part of an alter or create statement, and the next record holds the rest of it.  (maybe my datalength is insufficient?)

The sql server audit is still fairly new to me, and I was hoping for some EE advice for how to properly reference the audit events - server and database separately -- and how to better filter, or manage the data, once i've loaded the sqlaudit file to my table, and am trying to report on the events.  Below I have pasted the piece that writes the file to a table, and the piece that I am using to read from it.


THIS IS THE PIECE THAT COLLECTS THE sqlaudit FILE AND LOADS TO TABLE
NOTE: My WHERE clauses are trying to separate server from database audit events.  I was also getting duplicates in my result-set, so they are geared to elimiate dupes.  Possibly not correct. You can see my case statement, this is to get the bulk out of the larger events, and just quickly reference the action and the object, rather than putting the whole create or alter statement in there.  It seems correct.
-----------------------------------------------------------------------------------------------------------
INSERT dbo.myTable (EventTime,AuditType,ActionID,ServerName,DatabaseName,SchemaName,ObjectName,ActionPerformed,UserName,AuditFile)
SELECT EventTime,AuditType,ActionID,ServerName,DatabaseName,SchemaName,ObjectName,ActionPerformed,UserName,AuditFile
FROM
(      
      -- Database Audit
      SELECT af.event_time [EventTime],aa.class_desc [AuditType],af.action_id [ActionID],af.server_instance_name [ServerName],af.database_name [DatabaseName],af.[schema_name] [SchemaName],af.[object_name] [ObjectName],af.[statement] [ActionPerformed],af.server_principal_name [UserName],af.[FILE_NAME] [AuditFile]
      FROM sys.fn_get_audit_file ('C:\Audit\SQLServer*.sqlaudit',NULL,NULL) af INNER JOIN sys.dm_audit_actions aa
        ON af.action_id = aa.action_id INNER JOIN sys.dm_audit_class_type_map ctm
            ON aa.class_desc = ctm.securable_class_desc
      WHERE af.[statement] <> ''
      AND aa.class_desc <> 'SERVER'
      AND af.database_name <> 'master'
      AND ctm.class_type_desc = 'DATABASE'
      AND af.action_id IN('AL','DR')
      AND af.[OBJECT_NAME] NOT IN ('dbo','')
      GROUP BY af.event_time,aa.class_desc,af.action_id,af.server_instance_name,af.database_name,af.[schema_name],af.[object_name],af.[statement],af.server_principal_name,af.[file_name]
            
UNION

      -- Server Audit
      SELECT af.event_time [EventTime],aa.class_desc [AuditType],af.action_id [ActionID],af.server_instance_name [ServerName],af.database_name [DatabaseName],af.[schema_name] [SchemaName],af.[object_name] [ObjectName],af.[statement] [ActionPerformed],af.server_principal_name [UserName],af.[FILE_NAME] [AuditFile]
      FROM sys.fn_get_audit_file ('C:\Audit\SQLServer*.sqlaudit',NULL,NULL) af INNER JOIN sys.dm_audit_actions aa
        ON af.action_id = aa.action_id INNER JOIN sys.dm_audit_class_type_map ctm
            ON aa.class_desc = ctm.securable_class_desc
      WHERE af.[statement] <> ''
      AND aa.class_desc <> 'DATABASE'
      AND ctm.class_type_desc = 'SERVER AUDIT'
      AND af.database_name = 'master'
      AND af.[OBJECT_NAME] NOT IN ('dbo','')
      GROUP BY af.event_time,aa.class_desc,af.action_id,af.server_instance_name,af.database_name,af.[schema_name],af.[object_name],af.[statement],af.server_principal_name,af.[file_name]
) x
WHERE
      x.EventTime > @MaxEventTime


THIS IS WHAT I USE TO CAPTURE NEW EVENTS AND EMAIL THEM
--------------------------------------------------------------------------------------------
DECLARE @dt DATETIME = CONVERT(VARCHAR(10),GETDATE(),101)
DECLARE @QueryOut NVARCHAR(MAX),
      @Header VARCHAR(1000),
      @List VARCHAR(1000),
      @Subject NVARCHAR(155) = (SELECT @@SERVERNAME + '  ' + '  Server Audit'),
                        @tableHTML NVARCHAR(MAX)

SELECT @QueryOut = CAST(
            (
            SELECT
            td = CONVERT(VARCHAR(16),DATEADD(minute, DATEDIFF(minute,GETUTCDATE(),GETDATE()),EventTime)),'',
                                    td = AuditType,'',
                                    td = ServerName,'',
                                    td = DatabaseName,'',
                                    td = SchemaName,'',
                                    td = ObjectName,'',
                                    td = CASE WHEN ActionPerformed LIKE 'CREATE TABLE%' THEN SUBSTRING(ActionPerformed,1,31)  
                                                  WHEN ActionPerformed LIKE '%CREATE PROCEDURE%' THEN SUBSTRING(ActionPerformed,1,50)
                                                  WHEN ActionPerformed LIKE 'CREATE LOGIN%' THEN SUBSTRING(ActionPerformed,1,27)
                                                  ELSE ActionPerformed END,'',
                                    td = UserName
                              FROM
                                    dbo.MyTable sa
                              WHERE
                                    sa.Time >= @dt            
                              FOR XML PATH('tr'), TYPE)
                              AS NVARCHAR(MAX))

                  SET @tableHTML =
                        N'<STYLE TYPE="text/css">' +
                        N'TD{font-family: Verdana; font-size: 8pt; color:Black;}' +
                        N'table {border-width:4px; border-style:outset; text-align:left; color:Black;}' +
                        N'th {border-width:1px; border-style:inset; font-size:16px; font-weight:bold; padding:5px 20px 5px 20px;color:Black;}' +
                        N'td {border-width:1px; border-style:inset; font-size:12px; white-space:nowrap; padding:3px 20px 3px 20px;color:DarkBlue;}' +
                        N'H3 {font-size:16px; white-space:nowrap; color:Black;}' +
                        N'</STYLE>' +
                        N'<table border="1">' +
                        N'<tr>' +
                        N'<th>EventTime</th>' +
                        N'<th>AuditType</th>' +
                        N'<th>Server</th>' +                  
                        N'<th>Database</th>' +
                        N'<th>Schema</th>' +
                        N'<th>Object</th>' +
                        N'<th>Action</th>' +
                        N'<th>ByWhom</th>' +
                        @QueryOut +
                        + N'</table>';

                        IF(@tableHTML IS NULL)
                        BEGIN
                              RETURN;
                        END
                        ELSE
                        BEGIN
                              EXEC msdb.dbo.sp_send_dbmail
                                @profile_name = 'Profile',
                                @recipients = 'email',
                                @subject = @Subject,
                                @body = @tableHTML,
                                @body_format = 'HTML' ;
                        END
LVL 18
dbaSQLAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dbaSQLAuthor Commented:
Are there any SQL Server Audit Experts out there?
0
dbaSQLAuthor Commented:
Also, Is anybody familiar with Disabling/Enablilng the alerts?  Mine timeout when I attempt to do so.  They are fairly small -- only a few events being captured at the server level, and within each of the targeted databases.  I wanted to change the max_rollover_files, I attempted to alter, it never completed.  I attempted to disable, it timed out.

Any ideas?
0
Aneesh RetnakaranDatabase AdministratorCommented:
>Also, some of the events in the file are being loaded to the table in chunks, rather than in a single records.  For example, one record will have the first part of an alter or create statement, and the next record holds the rest of it.  (maybe my data length is insufficient?)

Can you paste a sample here
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

dbaSQLAuthor Commented:
well, i would have to genericize it.  i'm looking at one right now -- it is three records.  The statement captured in the 1st record begins with 'ALTER PROCEDURE' and ends with this:

       */    IF(@Venue IS NOT NULL AND @ProductCode IS NULL

The statement captured in the 2nd record begins with this, which is end of the statement above:

     )    OR(@Venue IS NOT NULL AND @FutMonth IS NULL)

And it ends with this:    

     END         /* Update acti

The 3rd statement value begins with this, which is the end of the above statment:    

     e price to deactive, if exists. */






i just figured it out, aneesh.  
select len(actionperformed) from myTable where auditid in(4310,4311,4312)

those 3 id's are the identifiers for the three records I was describing just now.  LEN of the first one is 4000, the 2nd one is 4000 and the third one is 907.

My datalength is insufficient.

Do you have any ideas on the actual filters I am using, or the way I am targeting the server audit, vs the database audit?
0
dbaSQLAuthor Commented:
Or the timeouts when attempting to stop/disable the audit(s).  that is troubling me.
0
dbaSQLAuthor Commented:
odd.  now they disable just fine.  i haven't been able to do it for a couple of days without a timeout.  hmmm...
0
Aneesh RetnakaranDatabase AdministratorCommented:
I have server audits, for these kind of DML changes I have Service broker too. Not too strong in this area, really hasn't faced issues other than huge audit files.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dbaSQLAuthor Commented:
well, thank you for looking this way, aneesh.  hopefully somebody else will take a look, too.
0
dbaSQLAuthor Commented:
I will close this now, Aneesh. Please excuse the delay, and thank you for looking.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.