I recently had a customer ask if they can audit SELECT operations for 1 table and 1 view in a specific database where the SELECT only contains certain keywords.
This article covers advanced filtering in ApexSQL Audit. This post will provide a tutorial to accomplish the requirement above.
Start by adding your server and database as you normally would. Select Operations and then all of the Query operations.
Next, on the Objects filter, build an Include list and check the tables/views/objects to audit. Pick as many options as you can using the "GUI" filters. Once complete, Apply the changes, then switch to the Advanced filter (5).
The Advanced filter will show the options that were selected via the GUI filter. In order to add the keyword filters, click the green + icon.
This is where it can get tricky! There is an "indent/outdent" button that will nest the criteria and attach it to the levels above and below. Start by adding a filter with the AND condition and pick "Text Data" as the field. This will match anything in the SELECT statement (or other operation, if you so choose.) We'll use the "contains" operator and then enter our text to match.
If there are multiple keywords to match, add another row to the filter using the green + icon. Then, you will want to indent it, and use the OR operator, as the screenshot below shows. Apply the filter when finished. You can also export the filter to save the definition of it.
Now it's time to test. Run some SQL statements that both match and miss the filter.
I use the "Default complete auditing report" with the time range set to the "last 1 minute" when testing, so as not to retrieve a large data set.
Advanced filters are powerful and can accomplish most auditing use cases. For more information on ApexSQL Audit, including a comprehensive knowledge base, visit https://www.apexsql.com/sql-tools-audit.aspx .
