17 May 2018

SELECT Statement executed against Classified table

by Eelco Drost

Rating: 5.0/5 (1 vote cast)

2,847 0 Compliance

Metrics install automatically if you have SQL Monitor installed.

If you are using Redgate’s SQL Server monitoring tool, SQL Monitor, you can instantly install and run this metric on your servers.

Uses Server and database audit to track SELECT statements.

First Create a Server audit:

CREATE SERVER AUDIT [Audit_Select_Statement] TO FILE (
FILEPATH = N'c:\Log\'
,MAXSIZE = 0 MB
,MAX_ROLLOVER_FILES = 2147483647
,RESERVE_DISK_SPACE = OFF
)
WITH (
QUEUE_DELAY = 1000
,ON_FAILURE = CONTINUE
)

ALTER SERVER AUDIT [Audit_Select_Statement]
WITH (STATE = ON)
GO

CREATE SERVER AUDIT [Audit_Select_Statement] TO FILE (

FILEPATH = N'c:\Log\'

,MAXSIZE = 0 MB

,MAX_ROLLOVER_FILES = 2147483647

,RESERVE_DISK_SPACE = OFF

)

WITH (

QUEUE_DELAY = 1000

,ON_FAILURE = CONTINUE

)

ALTER SERVER AUDIT [Audit_Select_Statement]

WITH (STATE = ON)

Then a database specific Audit based on this server audit: (in this case on the Database: WideworldImporters, schema “Sales” and table: “Customers”

USE [WideWorldImporters]
GO

IF EXISTS (
SELECT *
FROM sys.database_audit_specifications
WHERE name = 'SELECT_Statement_Classified_Table'
)
BEGIN
ALTER DATABASE AUDIT SPECIFICATION [SELECT_Statement_Classified_Table]
WITH (STATE = OFF)

DROP DATABASE AUDIT SPECIFICATION [SELECT_Statement_Classified_Table]
END

CREATE DATABASE AUDIT SPECIFICATION [SELECT_Statement_Classified_Table]
FOR SERVER AUDIT [Audit_Select_Statement] ADD (
SELECT ON OBJECT::[Sales].[Customers] BY [public]
)
WITH (STATE = ON)
GO

USE [WideWorldImporters]

IF EXISTS (

SELECT *

FROM sys.database_audit_specifications

WHERE name = 'SELECT_Statement_Classified_Table'

)

BEGIN

ALTER DATABASE AUDIT SPECIFICATION [SELECT_Statement_Classified_Table]

WITH (STATE = OFF)

DROP DATABASE AUDIT SPECIFICATION [SELECT_Statement_Classified_Table]

END

CREATE DATABASE AUDIT SPECIFICATION [SELECT_Statement_Classified_Table]

FOR SERVER AUDIT [Audit_Select_Statement] ADD (

SELECT ON OBJECT::[Sales].[Customers] BY [public]

)

WITH (STATE = ON)

Then setup the custom metric:

Metric definition

Name

SELECT Statement executed against Classified table

Description

Uses Server and database audit to track SELECT statements

The T-SQL query that will collect data


		
			
			
Transact-SQL
			
			DECLARE @auditFilePath VARCHAR(250) = (
		SELECT audit_file_path
		FROM sys.dm_server_audit_status
		WHERE name = 'Audit_Select_Statement'
		);

SELECT count(*)
FROM sys.fn_get_audit_file(@auditFilePath, DEFAULT, DEFAULT)
WHERE action_id = 'SL'
	AND database_name = DB_NAME(DB_ID())
	AND event_time BETWEEN DATEADD(MINUTE, - 2, GETUTCDATE())
		AND GETUTCDATE()
			
				
					
				
					1
2
3
4
5
6
7
8
9
10
11
12
				
						DECLARE @auditFilePath VARCHAR(250) = (
		SELECT audit_file_path
		FROM sys.dm_server_audit_status
		WHERE name = 'Audit_Select_Statement'
		);
 
SELECT count(*)
FROM sys.fn_get_audit_file(@auditFilePath, DEFAULT, DEFAULT)
WHERE action_id = 'SL'
	AND database_name = DB_NAME(DB_ID())
	AND event_time BETWEEN DATEADD(MINUTE, - 2, GETUTCDATE())
		AND GETUTCDATE()

Instances to collect from

Select all

Databases to collect from

Select all

Collection frequency

Use collected or calculated values

Leave the Use a calculated rate of change between collections check box unchecked

Metric collection

Enabled

Alert definition

You could add an alert once the baseline for your environment has been established.

2,847 Rate

Rating: 5.0/5 (1 vote cast)

SELECT Statement executed against Classified table

Metric definition

Name

Description

The T-SQL query that will collect data

Instances to collect from

Databases to collect from

Collection frequency

Use collected or calculated values

Metric collection

Alert definition

What are custom metrics?

What is SQL Monitor?

Featured custom metrics

Total database file size

Number of active backups and restores

Full backup to odd path

Find metrics