ApexSQL Audit provides a range of built-in queries as part of the reporting module that cover the most common user reporting requirements. For more specific reporting requirements, we have provided a possibility to create custom SQL Server auditing reports by using a drag-and-drop technique. If this still doesn’t provide the specific information you need, you will still be able create queries and reports of your own using T-SQL , due to the fact that ApexSQL Audit stores all audited data in a SQL Server database.
The SQL tables where audited records from all audited instances – local and remote are stored, resides in the ApexSQLCrd database on the central instance.
There are 2 views available – ApexSql.AccessedObjectView and ApexSql.EventView which can be used to simplify SQL table querying, as the tables in them are already properly joined, so all you have to do is specify the columns you want to see.
For example, to see how many events were created by each login in a specific time period, run the following SQL:
SELECT LoginName, count (ID)
AS EventCount
FROM ApexSQLCrd.ApexSql.EventView
WHERE CreateTime
>
'2013-07-03' AND LoginName is not null
GROUP BY LoginName
If you don’t use an existing view, the SQL would be:
SELECT LN.LoginName, count (E.Id) AS EventCount FROM ApexSQL.Event E LEFT JOIN ApexSQL.ServerName SN ON E.ServerNameId = SN.Id LEFT JOIN ApexSQL.ApplicationName AN ON E.ApplicationNameId = AN.Id LEFT JOIN ApexSQL.ClientHostName CHN ON E.ClientHostNameId = CHN.Id LEFT JOIN ApexSQL.LoginName LN ON E.LoginNameId = LN.Id LEFT JOIN ApexSQL.LoginSid LS ON E.LoginSidId = LS.Id LEFT JOIN ApexSQL.DatabaseName DN ON E.DatabaseNameId = DN.Id LEFT JOIN ApexSQL.SchemaName SCN ON E.SchemaNameId = SCN.Id LEFT JOIN ApexSQL.ObjectName OBN ON E.ObjectNameId = OBN.Id LEFT JOIN ApexSQL.TextData TD ON E.TextDataId = TD.Id LEFT JOIN ApexSQL.LoginName SLN ON E.SessionLoginNameId = SLN.Id LEFT JOIN ApexSQL.ServerName LSN ON E.LinkedServerNameId = LSN.Id LEFT JOIN ApexSQL.LoginName TLN ON E.TargetLoginNameId = TLN.Id LEFT JOIN ApexSQL.LoginSid TLS ON E.TargetLoginSidId = TLS.Id LEFT JOIN ApexSQL.UserName TUN ON E.TargetUserNameId = SN.Id WHERE CreateTime > ‘2013-07-03’ AND LN.LoginName is not null GROUP BY LN.LoginName
Another example would be to find out who has been altering SQL Server login roles, when and which roles were altered:
SELECT LoginName, CreateTime, TextData
AS EventCount
FROM ApexSQLCrd.ApexSql.EventView
WHERE TextData
LIKE 'ALTER SERVER ROLE%'
AND ServerName = 'FUJITSUSQL2012'
Downloads
Please download the script(s) associated with this article on our GitHub repository.
Please contact us for any problems or questions with the scripts.
