Return
-
Title
Understanding the changes implemented in ApexSQL Audit 2021.01.2098 hotfix and using it to retrieve quarantined data information -
Description
ApexSQL Audit 2021.01.2098 hotfix utilizes a more comprehensive quarantine and processing mechanism which may result in an increase in the number of session files contained in the quarantine folder on the audited server as well as the overall count of events inserted into the central repository.
The included enhancements and fixes serve the purpose of handling parsing issues that might occur in specific cases when reading and processing session files.
-
Resolution
Securing the completeness of auditing data
The number one benefit of ApexSQL Audit 2021.01.2098 hotfix is the enhancement to the session processing job. This improvement adds an additional security layer in the event when parsing of the audited data is incomplete. The mechanism ensures that unparsed data is moved into the quarantine in pristine and original state which ensures that it can be parsed and processed at a later time, ensuring full compliance. It employs a mechanism which identifies a query with a parsing issue and flags it for quarantine, while processing all the other queries from the file that can be successfully parsed.
The queries which cannot be processed will remain in their respective session file as raw data, which will then be sent to quarantine along with another (.info) file containing positional information about the queries. Both of these files will be placed in the quarantine folder in the form of a zip file with the .quarantine extension, as shown in the image below.
Therefore, before being sent to quarantine, these session files will be processed and the majority of the queries within them will be forwarded to the central server, while, only certain queries will remain unprocessed due to specific parsing issues.
Extracting information about the unprocessed queries
As previously stated, the quarantined zip file has the .quarantine extension and includes two files.
The .info file which contains the information about the position of the problematic query
The session file which contains the unprocessed query
The following image displays the two files after they had been extracted from the .quarantine file.
The first step in identifying the unprocessed query is to open the .info file by using any text editor. The data which will be displayed provides the information about the position of the query. Since zero-based event positioning (meaning that the count starts from 0) is used, the actual position of the queries is determined by adding 1 to the numbers displayed in the .info file.
If the first number is taken from the example below, that would mean that the actual position of the query is 11.
To perform the next step, SQL Server Management Studio needs to be started and a New Query window should be opened.
In the step that follows, a query that will read the target session file should be run. The full path to the session file (which had previously been extracted from the .quarantine file) needs to be provided. Using the same example which has been shown in the images above, the query would have the following form:
SELECT event_data
FROM sys.fn_xe_file_target_read_file('C:\ProgramData\ApexSQL\ApexSQLAudit\Auditing\MSSQLSERVER\Quarantine\ApexSQLAuditDB_018AdventureWorks2019_0_133123882789550000.xel', NULL, NULL, NULL)
The result set will contain all the relevant event data information preceded by a number which represents the position of the events from the session file. Using our example and the information from the .info file (<int>10</int> + 1), the problematic query can be easily located.
After the query has been located, in order to view the data in a form that is more legible, select it in the result grid, copy and then paste the data into a text editor. All the relevant information about the event that could not be processed will be shown.
Finally, it is recommended that the .quarantine files should be kept in a secure location until pertaining parsing issues are resolved.