Applies to
Summary
This article explains how to retrieve collected raw SQL audit data using the built-in application API.
Description
ApexSQL Audit has the capability for very high customization to extract SQL auditing data, with high precision and granularity, and save it to reports supporting most common file formats (Word, Excel, PDF, CSV). However, it lacks options to customize the formatting of those reports and provide some analytics. For that reason, a 3rd party software that supports web data retrieval, like Power BI, or a custom database created from that data, could come in handy and serve the purpose. Since the SQL auditing data is stored in a database with the proprietary format and data distribution, along with encryption applied, the web-based ApexSQL Audit API is available to extract raw data in the most capable JSON format, and use it in a 3rd party software for additional processing.
Enabling web server
The SQL audit reporting API calls are being processed through the integrated web server. This means that no additional components are required for installation, simply enabling the web server functionality from the application GUI is the only requirement besides creating a Windows certificate that will allow encrypted communication.
To enable and configure the web server, open the Options window and switch the view to the API reporting tab. From here, for the Web server state, click the On radio button to enable the configuration:
The window will display the following controls:
- Server port – customizable value that should be set to define the port through which the communication between API client and web server will be conducted (default value is 49152)
- Host name – the name of the machine hosting the ApexSQL Audit central service (and web server); this is configurable in case remote GUI access is used to connect to the central instance and configure the web server
- SSL certificate – the dropdown list of currently available trusted certificates; one certificate should be chosen here to enable encrypted communication between an API client and web server
- Create firewall exception for the specified port – this will write the entry into the firewall exception list to open the communication through the port configured with the Server port control
- Test – clicking the button will check all settings above, e.g. if the port is available, if the firewall exception can be made, if the certificate can be imported…
Confirming these settings with OK will start the server, apply the settings and close the window.
SSL certificate setup
In case there is currently no certificate available for this purpose, a new one can be created. To generate a new certificate the PowerShell console can be used. On the system hosting the SQL audit central repository start the PowerShell in administration mode and in the command prompt, type the following command:
New-SelfSignedCertificate -Type SSLServerAuthentication -Subject "CN=$env:COMPUTERNAME.$env:USERDNSDOMAIN" -FriendlyName 'ApexSQL Audit' -DnsName "$env:COMPUTERNAME","$env:COMPUTERNAME.$env:USERDNSDOMAIN" -KeyAlgorithm 'RSA' -KeyLength 2048 -Hash 'SHA256' -TextExtension '2.5.29.37={text}1.3.6.1.5.5.7.3.1' -NotAfter (Get-Date).AddMonths(36) -KeySpec KeyExchange -KeyUsage DataEncipherment,KeyEncipherment -Provider 'Microsoft RSA SChannel Cryptographic Provider' -CertStoreLocation Cert:\LocalMachine\My
This will create a new certificate that can be dedicated to the SQL audit reporting API. The certificate will be placed among Personal certificates and can be seen there with the Certificate Management console:
This certificate now has to be placed in the Trusted Root Certification Authority folder. To do that, right-click on the created certificate and choose the Copy option from the context menu:
Change the window focus to the Trusted Root Certification Authority\Certificates folder, right-click on the blank space and click Paste:
This certificate will have to be installed also on the machine from which the SQL audit reporting API calls will be run. To do that right-click on the certificate, and from the All tasks submenu click Export:
The action will start the wizard with the following steps:
-
Export Private Key – no private key is needed here:
-
Export File Format – any format is acceptable, for example, choose the basic DER Encoded binary X.509 (.CER) file format:
-
File to Export – enter the path and filename in which the certificate will be saved:
-
In the final step review the settings and click Finish to save the certificate:
To proceed with the certificate installation, copy the certificate file to the remote system and from the new location right-click on it and select the Install Certificate option:
This will start the wizard with the following steps:
-
Store Location – select the Local Machine option to enable this certificate for all users on the machine:
-
Certificate store – make sure that the Trusted Root Certification Authority location is chosen:
Review the settings and click Finish to complete the installation:
User permissions
Since the central repository database has protected access it is necessary to define a set of users that will be using the API for data extraction:
More information about managing users can be found in the article: How to allow or deny certain user access to the ApexSQL Audit GUI
These users will also need certificate access. To configure it, from the Certificate Manager console, find the created certificate in the Personal folder, right-click on it, and from the All tasks submenu choose the Manage Private Keys option:
This will open a standard security permission window where the Add button should be clicked to add new users for access:
In the opened dialogue, type in the name of a domain user and click OK to confirm adding the user, close the dialogue and go back to the permissions window:
Repeat this until all required users are added to the certificate access list and repeat the entire procedure on the remote system, where the certificate was copied.
This concludes the preparation for the SQL audit reporting API usage.
Creating report definitions
The API calls work in a way that they retrieve data based on existing report definitions. In other words, it is not possible to customize the scope of data that is going to be retrieved directly through API calls, but that scope will have to be pre-defined with saved reports. After that, the API call string, i.e. the URL, will contain the reference to a saved report, and during the run, it will apply the filter/time range settings saved with the said report:
More details about creating reports can be found in the article: SQL auditing tool reporting feature overview, and for advanced customization see the article: Walkthrough examples for SQL audit report customization
Generating and using the API call
If report definitions are prepared, to generate the URL, select one of the reports from the save list and click the Generate menu button, just like with any other export, and select the API call option. This will bring the pop-up window containing the URL:
From here, the URL can be edited to adapt it to a 3rd party application requirement, and then copied to Clipboard, using the Copy button, to be placed in the said application for data retrieval, or directly opened in a local web browser, using the Open in browser button, where data will be immediately displayed in JSON format.
Alternatively, instead of data, the report summary can be generated for a quick review:
When the URL is placed in the Clipboard it can be used in any application that supports web-based data retrieval. For example, using the following commands with the PowerShell:
$WebCall = New-Object System.Net.WebClient $WebCall.UseDefaultCredentials = $true Write-Output $WebCall.DownloadString("https://Q4V4Y6Y2:49153/report?name=Q4V4Y6Y2%5cMilan%2fGDPR-Monthly&last=1&unit=Week&page=1&range=10000")
Will generate the output like this:
Or, in the case of Excel, the Data import From Web can be used with the copied URL:
After which the Power Query Editor will be opened from where the JSON type data can be converted to the table:
And formatted to show required record columns:
After that it can be loaded to the Excel sheet formatted like it was configured:
Analogous to Excel, the Power BI can be used in the same manner as they both use the same Power Query Editor.
Using this reporting API with other software can open numerous possibilities for customized report formatting and in-depth statistical analysis from the collected SQL auditing data.
