17 May 2018

SELECT Statement executed against Classified table

by Eelco Drost

3,546 0 Compliance

Metrics install automatically if you have Redgate Monitor installed.

If you are using Redgate’s SQL Server monitoring tool, Redgate 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.

3,546 Rate

Click to rate this post!

[Total: 1 Average: 5]

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 Redgate Monitor?

Featured custom metrics

Total database file size

Number of active backups and restores

Full backup to odd path

Find metrics