SQL Server Audit is an efficient way to track and log events that occur within the database engine. For on-premises or IaaS environments, those audits can only be stored in a binary file, the Windows Application log, or the Windows Security log. Most customers I have worked with over the years store the audits in a binary file because of the limitations of the Windows Event logs.
Reading the contents of an audit event file can be accomplished using either Management Studio or the T-SQL function, sys.fn_get_audit_file. However, sometimes a customer may want to use a third-party tool to read and aggregate audit records, and some of those tools do not have the ability to read the binary audit file. In that case, the customer will just use sys.fn_get_audit_file to write the audit records to a database and then use the third-party tool to ingest those records from the database.
What if that third-party tool cannot even read from SQL Server? That happened to me recently where the only option was to read from a text-based file. The SQL Server PowerShell module has a function, Read-SqlXEvent, that reads audit files; however, it only outputs the raw unprocessed data.
For example, action_id and class_type are output as text fields in sys.fn_get_audit_file.
However, in Read-SqlXEvent, those same two fields are displayed as integers.
That may seem a bit odd, but the values are actually the same. Let us use action_id as an example. Read-SqlXEvent will return an integer value such as 1397311308. That value is just the integer representation of “LGIS”. We just need to do a little bit of conversion to prove it.
First, the integer value 1397311308 needs to be converted to HEX which gives us: 53 49 47 4C.
Next, the HEX value is displayed backwards, so we just need to reverse it: 4C 47 49 53.
Finaly, we convert the HEX value to ASCII which gives us the characters: LGIS.
There are five more fields that just need to be formatted so they match the output from sys.fn_get_audit_file.
- Permission_bitmask, server_principal_sid, and target_server_principal_sid are output as a bit array and needs to be converted to a hex value.
- Sequence_group_id is output as a GUID and needs to be converted to a hex value.
- Event_time just needs to be formatted.
Once we convert those values, the output should match what you would see in sys.fn_get_audit_file, except for one missing piece. The audit_file_offset value is not exposed using Read-SqlXEvent, so there is no way to display it.
Now that we know which fields need to be converted, we just need to build a PowerShell script to automate it.
The entire script is posted on Github, but to keep from boring you, I have listed the Pseudocode below.
https://gist.github.com/PatrickKeisler/d34f29f943151d253dc6626a1fd5c08b
- Build a lookup table for action_id and class_type values.
- Import the SqlServer module.
- For each file in input path execute Read-SqlXEvent.
- Loop through each audit event to convert look up the pre-converted value or do the data conversion.
- Output the results using Export-Csv.
I know this script is processing row-by-row (RBAR) and could possibility be slow, but one way I am trying to mitigate that is by pre-calculating the values for action_id and class_type. Those are known values and do not change, unless new types are added to SQL Server. In my testing with one customer, this script can convert an audit file fast than their SQL Server can create a new one. Additionally, it is much faster than importing the audit file to a database and then exporting it out to a CSV.
We can run the script to convert a single audit file.
.\Convert-SqlAudit.ps1 `
-FileName C:\Temp\SystemAudit_AD5AA23A-E9BD-4AE9-8343-CAC5F714B41E_0_133729829335630000.sqlaudit `
-OutputPath C:\Temp\CSV
Then we can open it in Excel. Notice the values for action_id and class_type.
There may be more efficient ways to convert the audit data, but ultimately I am hoping that Read-SqlXEvent will be updated to output the data as seen in Management Studio or sys.fn_get_audit_file.
Reference:
https://learn.microsoft.com/en-us/powershell/module/sqlserver/read-sqlxevent?view=sqlserver-ps
https://learn.microsoft.com/en-us/sql/relational-databases/system-functions/sys-fn-get-audit-file-transact-sql?view=sql-server-ver16&tabs=sqlserver
1 Response
[…] Patrick Keisler finds a work-around: […]