In what format are the *.sqlaudit files?

  • 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....

  • 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

  • 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...)

  • 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

  • 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

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

  • Bingo:

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

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

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

  • 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

  • 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

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

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