[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)"

[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!