[T-SQL] Basic Pivot automation script

There's a guy out there who has a great dynamic pivot CLR.  This is a much simpler version.  I _think_ this is original to me.  I've been using it in various projects on this blog, but I don't see where I posted just this code.

Say you have records from your monitor that happens to Track EventLogs.
We'll call it EventLog_Tracking for argument's sake (hint hint http://thebakingdba.blogspot.com/2015/05/powershell-eventlogtracking-capturing.html) and want to look at trends over time.

First, you'd need a grouped set.

SELECT sourcename, CONVERT(DATE, TimeGenerated) AS the_date, COUNT(*) AS the_count
INTO #temp
FROM EventLog_201512
WHERE sourcename LIKE 'microsoft-windows%'
GROUP BY SourceName, CONVERT(DATE,TimeGenerated)
ORDER BY CONVERT(DATE,TimeGenerated), SourceName





And you want it to look like...
 


Here's the base pivot code.  For this example, replace down with "the_date" and across with "sourcename"

If you want a name or something instead of the number, then do something like "max(yourname)" instead of "sum(the_count)"

	DECLARE @SQL as VARCHAR (MAX)
	DECLARE @Columns AS VARCHAR (MAX)
	SELECT @Columns=
	COALESCE(@Columns + ',','') + QUOTENAME(across)
	FROM
	(
	SELECT DISTINCT across
	From #TEMP
	) AS B
	ORDER BY B.across
	SET @SQL='
	SELECT down, ' + @Columns + ' FROM
	(
	SELECT down, across, the_count FROM
	#TEMP ) AS source
	PIVOT
	(SUM(the_count) FOR source.across IN (' + @columns + ')
	)AS pvt'
	EXEC (@sql)

view raw dynamic_pivot.sql hosted with ❤ by GitHub

[Hey Kids] @@servername and BCP and ultra-long statements.

Hear me now and listen to me later.

When you take that ultra-complex 6000 character string for BCP and wonder why it's getting truncated to 4000 (which SSMS has problems showing anyhow), remember that @@servername is sysname, which is an alias for Nvarchar(256)... and adding it to a varchar converts the whole string to nvarchar. 

I'll never get those hours of my life back.  Be smarter than me.

[System_Health] pulling all the data from the saved-to-disk files

TL;DR - imports the 3-4 system_health files that are automatically saved to disk.

I had a problem this week with a deadlock.  I went looking in the ring buffer for the system_health session, but it had already aged out. 

So, remembering that the system_health session has a file target, went looking for it (Jason Brimhall has code for it - thanks!).  The file went back further but not all the way.  Since the event definition says to keep a couple of older files, I use the current file to find the path, list all the files in that path (xp_dirtree; doesn't require xp_cmdshell), then walk through and import all of them. 

You could simplify it a lot and use fn_xe_file_target_read_file and tell it to read all files like system_health*0.xel in the path, but with my luck those ending numbers will sometimes end in a non-0 value.  I use the 0 in the mask because I make local copies of the files occasionally, so that weeds out " - Copy".

When I get some free time, I'll get my current sp_server_diagnostics/system_health parsers to work with it (see http://thebakingdba.blogspot.com/2015/04/systemhealth-2-dynamic-and-full-parsers.html and http://thebakingdba.blogspot.com/2012/12/spserverdiageventparser-make.html).  I started on that, but it's cartesianing on me and I don't have time to fix it right now. 

Enjoy!

	--TL;DR thebakingdba.blogspot.com
	--Purpose: find and read your system_health files.
	--uses Jason Brimhall's code to find the event_file target via DMV, then uses that as a base folder for the rest.
	--http://jasonbrimhall.info/2015/06/21/reading-extended-event-file-session-data/
	declare @full_systemhealth_file varchar(1000), @systemhealth_path VARCHAR(512)
	DECLARE @full_directory_listing TABLE (subdirectory VARCHAR(1000),depth SMALLINT, [FILE] smallint)
	DECLARE @system_health_files TABLE (id INT IDENTITY, XE_filename VARCHAR(1000))
	if object_id('tempdb..#xe_file_target_results') is not null
	DROP TABLE #xe_file_target_results
	CREATE TABLE #xe_file_target_results
	(XE_object_name NVARCHAR(256), event_data XML, xe_filename VARCHAR(500), file_offset BIGINT)
	--get the name of the current system_health file
	SELECT @full_systemhealth_file = FileTarget.value('@name','varchar(1000)') from
	--do this so you get the XML to parse with Xquery
	(SELECT target_data = CONVERT(XML, target_data)
	FROM sys.dm_xe_session_targets t
	INNER JOIN sys.dm_xe_sessions s
	ON t.event_session_address = s.address
	WHERE t.target_name = 'event_file') cte1
	--then this gives you the file target name
	CROSS APPLY cte1.target_data.nodes('//EventFileTarget/File') FileEvent ( FileTarget )
	--Use xp_dirtree to get ALL the system_health files in that folder, since that's where the old ones should reside
	--I could have just called "sys.fn_xe_file_target_read_file('C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\system_health*0.xel', NULL, NULL, NULL)"
	-- but my concern was that the tag at the end might not always end in 0. On the plus side, xp_dirtree doesn't require xp_cmdshell!
	SELECT @systemhealth_path = LEFT(@full_systemhealth_file, len(@full_systemhealth_file) - charindex('\',reverse(@full_systemhealth_file))) + '\'
	--once we have the path, list all files in that path.
	INSERT INTO @full_directory_listing
	EXEC xp_dirtree @systemhealth_path, 1, 1
	--make a list of system_health files that aren't copies
	INSERT INTO @system_health_files (XE_filename)
	SELECT subdirectory FROM @full_directory_listing
	WHERE [FILE] = 1
	AND subdirectory LIKE 'system_health%.xel'
	AND subdirectory NOT LIKE '%copy%'
	AND subdirectory NOT LIKE '% %'
	--Now import each file
	declare @min INT, @max INT, @filename VARCHAR(500)
	SELECT @min = MIN(id), @max = MAX(id) FROM @system_health_files
	while @min <= @max
	BEGIN
	print @min
	SET @filename = NULL
	SELECT @filename = xe_filename FROM @system_health_files WHERE id = @min
	INSERT INTO #xe_file_target_results
	SELECT [object_name] AS XE_object_name, event_data, @filename AS xe_filename, file_offset
	FROM sys.fn_xe_file_target_read_file(@systemhealth_path + '\' + @filename, NULL, NULL, NULL)
	set @min = @min+1
	END

view raw system_health_import_all_event_file hosted with ❤ by GitHub