Audit Made Easy Without Audit Log - Part One

Introduction

 
In Microsoft SQL Server, the activity of each of the database tables is tracked in the other table. The phenomenon is called the Audit trail or Audit log of the database table.
 
In most of the cases, we don’t explicitly create the “Server Audit”. The reasons for not having a server audit for the database are listed below,
  • More memory space consumption
  • Each of the CRUD operations will trigger the Insert operation into Audit record which slows the process
  • Query execution time is increased [Not directly impacted]
  • Expensive
In this article, I will explain how to make use of the existing resources in the SQL Server which cannot directly replace the Audit trail whereas it can help to solve a few of the needs which an Audit trail can satisfy.
The Microsoft SQL Server itself has some of the features which were not used by the developers frequently and I am going to explore these options.
 

SQL Server Audit (Database Engine)

 
Auditing an instance of the SQL Server Database Engine or an individual database involves tracking and logging events that occur on the Database Engine. SQL Server Audit lets you create server audits, which can contain server audit specifications for server level events and database audit specifications for database level events. Audited events can be written to the event logs or to audit files.
 
There are several levels of auditing for SQL Server, depending on government or standard requirements for your installation. SQL Server Audit provides the tools and processes you need to enable, store, and view audits on various servers and database objects.
 
Reference 
 
https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-database-engine
 
Scenario 1
 
I have to identify the last table updated with DateTime, however, in this scenario, the Audit trail is not available for the specific table. Also, there is no column in the database table which shows the Last updated date time.
 
We are going to use the system table of SQL Server where some database and data table information is stored.
 
sys.dm_db_index_usage_stats is the table which is used to find the value of the table updated last time.
 
I will explain about the table “sys.dm_db_index_usage_stats” in later parts of this article but now, let’s know the usage of the table in different scenarios.
 
Query
  1. SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName,  
  2.     last_user_update  
  3. FROM sys.dm_db_index_usage_stats 
Output/Result
 
Output 
 
Scenario 2
 
In this, we have to get the Date time when the table was last executed with SELECT query. This will be helpful to check if some are used in an application or Code or by its stored procedures for a long time, if not, we can drop the table which is not necessary.
 
To confirm the usage of the table, we can use the following query. In this sample, I have added the condition to check for a specific data table value.
 
Query
  1. SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, last_user_seekFROM sys.dm_db_index_usage_statsWHERE OBJECT_ID=OBJECT_ID('ApplicationConfiguration')  
Output/Result
 
Output
 
Here, the “ApplicationConfiguration” table name is specified in the query. So, the information is shown only of that table.
 
Scenario 3
 
In this scenario, we have to find the number of times a table is used for Read or Update operations.
 
This is very helpful for the analytics scenarios where a certain table may be used multiple times, whereas some other tables may not be used at all by the application. So, to identify the frequency of usage, we can use this query.
 
Query - Number_of_Select_Queries
  1. SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName,user_seeks AS Number_of_Select_QueriesFROM sys.dm_db_index_usage_stats  
Output/Result
 
Output
 
Query - Number_of_Update_Queries
  1. SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, user_updates AS Number_of_Update_QueriesFROM sys.dm_db_index_usage_statsWHERE OBJECT_ID=OBJECT_ID('Logs')  
Output/Result
 
Output
 
The usage of this system table is not just limited to the above scenarios. It has many columns and each column is helpful for the user to get the statistics and index based reporting the data base is not configured with action audit.
 
More information on “sys.dm_db_index_usage_stats”
 
sys.dm_db_index_usage_stats has a limitation that it does not return the details about memory-optimized indexes, however, we have other set of alternatives to achieve those tasks.
 
I have provided the information of each of the Columns.
 
Query
  1. SELECT *FROM sys.dm_db_index_usage_stats  
The below result value shows all the information for each table in the particular database.
 
Result
 
Output
 
Output
 
The identifier for the node that this distribution is ON.
 
Table Column name Data type Information
database_id smallint Database ID on the table is defined.
object_id int ID of the table the index is defined
index_id int ID of the index.
user_seeks bigint Number of seeks by user queries.
user_scans bigint Number of scans by user queries. This represents scans that did not use 'seek' predicate.
user_lookups bigint Number of bookmark lookups by user queries.
user_updates bigint Number of updates by user queries. This includes Insert, Delete and Updates representing number of operations done not the actual rows affected. For example, if you delete 1000 rows in one statement, this count will increment by 1
last_user_seek datetime Time of last user seek
last_user_scan datetime Time of last user scan.
last_user_lookup datetime Time of last user lookup.
last_user_update datetime Time of last user update.
system_seeks bigint Number of seeks by system queries.
system_scans bigint Number of scans by system queries.
system_lookups bigint Number of lookups by system queries.
system_updates bigint Number of updates by system queries.
last_system_seek datetime Time of last system seek.
last_system_scan datetime Time of last system scan.
last_system_lookup datetime Time of last system lookup.
last_system_update datetime Time of last system update.
pdw_node_id int Applies to: Azure SQL Data Warehouse, Parallel Data Warehouse
 

Conclusion

 
This set of tables is very useful. Post your queries in the comment box and I will be happy to help you with more details.


Similar Articles