/*============================================================================
  File:     4a_The_UI.sql

  SQL Server Versions: 2012 onwards
------------------------------------------------------------------------------
  Written by Erin Stellato, SQLskills.com
  
  (c) 2014, SQLskills.com. All rights reserved.

  For more scripts and sample code, check out 
    http://www.SQLskills.com

  You may alter this code for your own *non-commercial* purposes. You may
  republish altered code as long as you include this copyright and give due
  credit, but you must obtain prior permission before blogging this code.
  
  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED 
  TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  PARTICULAR PURPOSE.
============================================================================*/

/*
	Default templates exist, just as in Profiler
	The Activity Tracking Template recreates what the default trace captures
	Issue with said template in SQL 2012:
	https://www.sqlskills.com/blogs/jonathan/workaround-for-bug-in-activity-tracking-event-session-template-in-2012-rc0/
*/

/*
	Create XE session with:
		sp_statement_completed
		sql_batch_completed
	Filter on:
		database_id
		statement or batch_text
		logical_reads > 1000
*/

IF EXISTS (
		SELECT 1 FROM sys.server_event_sessions 
		WHERE name = 'Filter_Query_Text')
	DROP EVENT SESSION [Filter_Query_Text] ON SERVER;
GO

CREATE EVENT SESSION [Filter_Query_Text] 
	ON SERVER 
ADD EVENT sqlserver.sp_statement_completed(
	SET collect_object_name=(1),collect_statement=(1)
	ACTION(
		sqlserver.client_app_name,sqlserver.database_name
		)
	WHERE (
		[sqlserver].[database_id]=(7) AND (
			[sqlserver].[like_i_sql_unicode_string]([statement],N'%WorkOrder%') OR [logical_reads]>(5000)
			)
		)
	),
ADD EVENT sqlserver.sql_batch_completed(
	SET collect_batch_text=(1)
    ACTION(
		sqlserver.client_app_name,sqlserver.database_name
		)
    WHERE (
		[sqlserver].[database_id]=(7) AND (
			[sqlserver].[like_i_sql_unicode_string]([batch_text],N'%WorkOrder%') OR [logical_reads]>(5000)
			)
		)
	) 
ADD TARGET package0.event_file(SET filename=N'C:\temp\FilterQueryText.xel')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,
MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

ALTER EVENT SESSION [Filter_Query_Text] 
	ON SERVER 
	STATE = START;

ALTER EVENT SESSION [Filter_Query_Text] 
	ON SERVER 
	STATE = STOP;

DROP EVENT SESSION [Filter_Query_Text] 
	ON SERVER;
