In what format are the *.sqlaudit files?

Question

In what format are the *.sqlaudit files?

Ray Sotkiewicz

Right there with Babe

Points: 796
More actions
April 29, 2010 at 5:31 pm

#218349

There is scant information about these files on the web.
There's mention about a "Application Log File Viewer", which I'm not familiar with at the moment.
More specifically, does anyone import these files into a database via SSIS?
Thanks for any info....

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 1

I was looking at doing this. But the best way I could come up with was using fn_get_audit_file to get the file expressed as a table. I would much rather have a component in SSIS do this directly but I am not aware of one.

CEWII

Ray Sotkiewicz Right there with Babe Points: 796 More actions · Answer 2

Thanks for the info.. How strange that MS would provide this file but no real automated ETL process to extract the information... (At least not to my knowledge yet...)

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 3

They do provide fn_get_audit_file but you have to execute it on the server against files that can be reached from that server. Then again it isn't all that different from errorlog files or trace files. MS provides a sproc to get at them but not an SSIS component.

I was just thinking that maybe WMI could do it. I'm going to look at that.

Also some of my reading indicates that the sqlaudit files are either XML or compressed XML files.

CEWII

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 4

This is kind of interesting.. the code for that function is pretty straightforward:

CREAE FUNCTION [sys].[fn_get_audit_file] (

@file_pattern nvarchar(260),

@initial_file_name nvarchar(260) = NULL,

@audit_record_offset bigint = NULL)

RETURNS table

AS

RETURN SELECT *

FROM OpenRowSet(TABLE FN_GET_AUDIT_FILE, @file_pattern, @initial_file_name, @audit_record_offset)

CEWII

Ray Sotkiewicz Right there with Babe Points: 796 More actions · Answer 5

Probably compressed XML... Raw XML is fairly readable while the SQAudit files have all those whanky chars in them.

Ray Sotkiewicz Right there with Babe Points: 796 More actions · Answer 6

Bingo:

SELECT * FROM sys.fn_get_audit_file ('\\serverName\Audit\HIPPA_AUDIT.sqlaudit',default,default);

Mark Shepherd-435962 Mr or Mrs. 500 Points: 568 More actions · Answer 7

This solution may have the SSIS package you are looking for.

http://sqlcat.codeplex.com/wikipage?title=sqlauditcentral&referringTitle=Home

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 8

Mark Shepherd-435962 (6/3/2010)

This solution may have the SSIS package you are looking for.

http://sqlcat.codeplex.com/wikipage?title=sqlauditcentral&referringTitle=Home%5B/quote%5DI just looked at that, it looks like a nice framework, however internally it still uses sys.fn_get_audit_file..

CEWII

sunilsql4 SSC Journeyman Points: 95 More actions · Answer 9

Ray Sotkiewicz - Thursday, April 29, 2010 9:16 PM
Thanks for the info.. How strange that MS would provide this file but no real automated ETL process to extract the information... (At least not to my knowledge yet...)

It is due to Security reasons