Barton team update: ApexSQL Audit – Update 2

Barton team is solely responsible for ApexSQL Audit and we are committed to ensuring that it is the best auditing tool for SQL Server available

Here is what we have been working on

  • We have been working on bug fixes, improving usability and general quality. New features introduced in ApexSQL Audit 2015 R1 will get even better in ApexSQL Audit 2015 R2
  • The advanced auditing filter will be more intuitive. Ability to select the auditing filter type (simple or advanced) per server has been introduced. The advanced auditing filter allows much more control over which SQL Server events are captured
  • Exporting to CSV (Comma separated values file) has been made more stable supporting larger data sets. CSV files are useful since they can be opened in Microsoft Excel, or other applications, so captured events can be analyzed further

Our ETA to production …

  • ApexSQL Audit 2015 R2 is planned for release in May. For a complete schedule of all upcoming releases, please see the Product roadmap.

Some design and technical challenges we have encountered and overcome include

  • In this release our main focus was on improving usability and general quality
  • We made crucial design improvements to the advanced auditing filter in order to make it more intuitive and easier to use. See the next section for some images
  • The CSV export feature was limited by the amount of RAM, which meant a lower amount of data could be exported. By using the file system to create the CSV file this limitation has been lifted, so now the only limit to the size of data exported in CSV is the empty disk space
  • If a different Windows account was used to run the ApexSQL Audit service, the reports couldn’t access the repository archives. This was a high severity bug which will be fixed in the upcoming release

Here are some cool features, improvements and bug fixes coming in ApexSQL Audit 2015 R2:

  • The advanced auditing filter layout is more intuitive so it’s easier to configure – a group of conditions is represented with a border and the operator in front of the group is displayed in front, so it’s easy to understand how the group is related to other conditions outside of the group

  • Reports can correctly access repository archives when a different Windows account is used for running the ApexSQL Audit central service – this critical issue has been fixed in the upcoming release
  • When an inaccessible repository archive is selected as the data source in reports, the UI would lock up and prevent selection of a different data source, or fixing the currently selected one. This has been fixed, the UI doesn’t get locked, so the data source can be corrected or a different data source can be selected
  • The common and custom reports were not using the same date-time input control. This has been fixed and makes the UI more consistent and easier to use
  • The exporting process had issues with RAM usage, and this prevented generating reports with larger amount of data. This process has been optimized, which increases the maximum size of the exported report
  • CSV exporting is a cool new feature introduced in ApexSQL Audit 2015 R1, but was constrained by RAM. In the upcoming version this feature relies only on the file system, allowing CSV files limited only by empty disk space
  • Loading report preview data would sometimes timeout (wouldn’t show any data) when using repositories with a large number of events. This won’t happen with the upcoming version
  • The Complete audit trail report now has the ability to show internal events (events generated by ApexSQL Audit service), making it consistent with reports generated in the custom reports
  • The “Loading report data…” notification message would remain shown after showing report preview data. This was very confusing and has been fixed in the upcoming release
  • The simple auditing filter UI layout has been improved to use screen estate more efficiently and minimize the need for scrolling
  • The date-time format setting in report options has gotten a list of predefined date-time formats making it easier to modify the date-time format used for displaying event date and time in reports
  • There was a bug in the ApexSQL Audit auditing service which caused the auditing to break when large SQL statements occur -longer than 10000. This issue has been fixed in the upcoming release

Big features planned in subsequent releases will be:

ApexSQL Audit R3 – ETA September 2015:

  • Multiple simultaneous data sources in reports – the application will automatically choose which repository archives to read from based on the selected time span
  • Advanced system status – showing detailed per instance information and information about the central instance which collects the events into the repository
  • Choose table columns (sensitive columns) – giving the ability to specify which columns are audited
  • CLI for exporting report – allowing scheduling the export process through external task scheduling applications

ApexSQL Audit R4 – ETA December 2015:

  • Include/Exclude query text – when SQL statement text is not necessary it can be excluded, which will help to make repository smaller and reports generate faster
  • Configuration templates – allowing the same auditing filter to be applied to multiple audited servers, making the configuration process faster
  • Aggregate and graphical views on collected data per instance
  • Improved archiving – choose to archive from specific time: last week, last month, last year or just by specific date. The current repository database will always contain the newest data and old data will be automatically moved to a repository archive
  • Purge data from specific time range – old events will automatically be purged, freeing up disk space
  • Improved alerts – alerts related to sensitive columns, custom alert threshold to minimize the number of alert emails, date filter, making alerts more powerful

For a complete schedule of all upcoming releases, please see the Product roadmap.

May 11, 2015