ApexSQL Audit Central Repository Database design

All configuration and audited records from all audited SQL Server instances are stored in a centralized auditing repository database called ApexSQLCrd. The tables in the ApexSQLCrd database are

ApexSQL.AlertReports – stores the information about alerts that happened.
ApexSQL.ApplicationName – a look-up SQL table, stores names of the applications used to make audited events.
ApexSQL.CentralRepository#Const, ApexSQL.CentralRepository#var – stores information about the central repository database (a name, version…). Some of the tables are split between variables and constants (the suffixes #var and #const are added). We use this design, to logically update a table, while rows are physically inserted.
ApexSQL.ClientHostName – a look-up SQL table, stores names for the computers which generated audited events.
ApexSQL.ComplyInstance#Const, ApexSQL.ComplyInstance#Var– stores information about ApexSQL Audit instances (SQL Server location).

ApexSQL.DatabaseName – a look-up SQL table, stores audited SQL databases names.
ApexSQL.DataSource#Const, ApexSQL.DataSource#Var – stores information about data sources used in reports, set in the Options web report tab.
ApexSQL.EncryptionKey – for internal usage.
ApexSQL.Event – the “main” table, stores the events that have happened.

ApexSQL.LoginName – a look-up SQL table, stores names for the logins used to generate events.

ApexSQL.ObjectName – a look-up SQL table, stores audited object names.
ApexSQL.Package#Const, ApexSQL.Package#Var– stores information about packages that are deployed to ApexSQL Audit central instance (these packages contain a list of events bulk inserted per package).
ApexSQL.ReportDefinition#Const, ApexSQL.ReportDefinition#Var – stores custom report definitions
ApexSQL.SchemaName – a look-up SQL table, stores audited schema names.
ApexSQL.ServerName– a look-up SQL table, stores audited SQL Server instance names.
ApexSQL.SystemEvent – stores internal system events
ApexSQL.SystemSettings#Const, ApexSQL.SystemSettings#Var – for internal usage.
ApexSQL.UserName – a look-up SQL table, stores information about user names (e.g. when a user is created, or permission is changed for a user…).
ApexSQL.UserOptions#Const, ApexSQL.UserOptions#Var – stores web console options

For example, the table ApexSQL.DatabaseName looks like this:

Table ApexSQL.DatabaseName

There is one view in ApexSQLCrd:

ApexSQL.EventView– shows all captured records for audited events – the time of the event, who did it, what was done, using which application, computer, which object was affected… A total of 40 columns from the ApexSQL schema tables Event, ServerName, ApplicationName, ClientHostName, LoginName, LoginSid, DatabaseName, SchemaName, ObjectName, TextData, LoginName, ServerName, LoginName, LoginSid and UserName tables are shown. This view is useful for further querying of the audited events; use it to avoid multiple INNER JOINS among the queried tables.

For example to get a list of the events with their basic parameters – SQL Server instance name, database name, login name, etc. that happened on 2013-07-02 between 8:40 PM and 20:44 PM, use the following query:

SELECT ServerName, 
       StartTime, 
       ApplicationName, 
       ClientHostName, 
       LoginName, 
       DatabaseName, 
       SchemaName, 
       ObjectName, 
       TextData

FROM [ApexSQLCrd].[ApexSQL].[EventView]
WHERE StartTime
      > 
      '2013-07-02 20:40:40.6800000 +02:00'

And the result set is:

ApexSQL.EventView results