Applies to
Summary
The article explains how SQL audit reporting filters populate their values and clarifies possible misconceptions about expected values
Description
Although reporting filters correspond to the SQL auditing configuration options it is a common misconception about what values they should show. The behavior and values in reporting filters are presented dynamically and, in this article, it will be explained how these dynamics work.
SQL audit configuration
ApexSQL Audit provides lots of customization options to configure auditing. To mention some of them: creating a list of SQL Servers and databases for auditing, selecting specific logins or applications to monitor for events, etc.:
As well as configuring the list of objects and columns to monitor:
These settings will reflect on what SQL Server and database events will be collected and shown in reports.
SQL audit reporting filters
When auditing is deployed it is natural to check if, and how it works, and the best way to do that is to switch the Reports tab and observe received events. However, if filters for reports are observed, they do not show anything, even if the audit configuration included specific, custom details. As shown below, there are no SQL Servers in the Servers list, even if the refresh button is pressed:
The same goes with the Logins:
And the rest of the filters will show the same situation.
The behavior can be simply explained by the fact that the mechanism behind reporting filters is based on collected data and not the audit configuration. In other words, when an event based on auditing settings is captured, it will relay the information contained in it, like the name of the database affected, a login that created the event, objects affected, etc. and, as result, the filter list will start to populate.
It is possible to be certain that some events were captured but still don’t see anything in filters:
This is where the refresh button comes to place. Using this button will force the population of filters with new items:
The same experience can be expected when some new SQL Server or database is added for auditing on top of the current auditing configuration.
Sometimes it is possible to see an item in filters that is certainly not the subject of auditing. For example, in the screenshot below the “master” database can be seen in the Database filter, even though it is certain that this database is not being audited:
The screenshot also shows the event with the “master” database, again, even if that database is not audited.
The explanation comes from the fact that there was an audited event occurred that was referencing the object in question. In this case, the login attempt was audited, and since the login operation is executed against the “master” database, it will be referenced. To check why some objects appeared as audited, it is advised to examine the Operation column. That would give the clue how and why was this object referenced.
This behavior is caused due to the part of the event capture mechanism which relays all information about that event as previously explained. More complex events can create additional confusion, with logins or object names showing up in filters, but the easiest way to clarify the situation is to check the Operation column and the captured SQL text data.
Filter retention
The filter information is kept in the central repository database, and that includes archived repositories or simply archives. There is a possibility that we are searching for a specific item in the filters which cannot be found, and we are certain that at some point an event related to that item was captured. This behavior comes as a result of removing some archives from active reporting data sources:
If a wanted event is recorded in some past moment it might be contained in an archive and if that archive is removed the event will not be shown in reports until the archive is attached again as a data source. As consequence, if that event is the only one containing the details about some database activities, those details will not be present anymore in the reporting filters, and, in return, adding back the archive with that event will regenerate that detail in the filter.
