mbourgon

CREATE PROCEDURE sp_server_diag_event_parser
as
--SP_SERVER_DIAGNOSTICS Dynamic Parser for "events", v1.23
--You may use this at will, you may share it provided this header remains.  
-- Copyright 2012 Michael Bourgon
-- Commercial use or sale prohibited without permission. Personal, Internal Company, or Private use is fine.
-- If you're just running this as your job as a DBA, enjoy.
-- Please feel free to share, and feel free to send corrections or enhancements - thebakingdba.blogspot.com
-- Thanks to Marc_S on Stackoverflow for the help on parsing XML.
-- Thanks to Stack Overflow for forcing me to come up with a good question - so I found the flawed derived table slowdown.

mbourgon

--For the repository server I only have one cert and one key.  
--For the new servers I have to create a new certificate and key for each one.  

-------------------------------------------------------------
--Part 1, Run on new server adding to Event Notifications--
-------------------------------------------------------------
USE master
GO
--Creating master key and an user which will be used by the certificate
CREATE MASTER KEY ENCRYPTION BY PASSWORD = N'';

mbourgon

<#
.SYNOPSIS
   Script downloads an rss feed of data about SQL Server service packs and cummulative updates.
.DESCRIPTION
   Script downloads data into a staging table and is called by a job.
.PARAMETER <paramName>
   There are no parameters.
.EXAMPLE
   Call from a job:  powershell "& 'C:\Powershell_Scripts\rss_feed_SqlServer_Releases.ps1'"
#>

mbourgon

--reminder, this runs on the central monitorING box, and uses xp_cmdshell and the like to connect to the "new" box.


/*
 Event Notification - add server to monitoring.  Probably simpler to do by hand, but I like this.

 See http://thebakingdba.blogspot.com/ for more information, look for Event Notifications

 Given a parameter of servername, WHEN RUN ON THE EN "repository", it will 
	a) try to determine the servername for the "sender"

mbourgon

if object_id('tempdb..#systemhealthsessiondata') is not null
    DROP TABLE #systemhealthsessiondata

SELECT CAST(xet.target_data AS XML) AS XMLDATA
INTO #SystemHealthSessionData
FROM sys.dm_xe_session_targets xet
JOIN sys.dm_xe_sessions xe
ON (xe.address = xet.event_session_address)
WHERE xe.name = 'system_health'

mbourgon

if object_id('tempdb..#systemhealthsessiondata') is not null
    DROP TABLE #systemhealthsessiondata

SELECT CAST(xet.target_data AS XML) AS XMLDATA
INTO #SystemHealthSessionData
FROM sys.dm_xe_session_targets xet
JOIN sys.dm_xe_sessions xe
ON (xe.address = xet.event_session_address)
WHERE xe.name = 'system_health'

mbourgon

--table must have at least 10000 rows, so that it doesn't get lost in NULLs.  I think.
DECLARE @table_name sysname, @date_column sysname, @sql NVARCHAR(4000), @lowest_date VARCHAR(20)
SET @table_name = 'mytablename'
SET @date_column = 'mydatefield'
SET @lowest_date = '20150415'

SELECT @sql = '
DECLARE @tempdate DATETIME, @target_id bigint, @max_id bigint
SELECT @target_id = MAX(id) FROM ' + @table_name + ' with (NOLOCK) 
set @max_id = @target_id

mbourgon

. C:\powershell_scripts\out-datatable.ps1
. C:\powershell_scripts\write-datatable.ps1

$server_repository = 'myrepo'  
$database_repository = 'repodb'

#here we create $starttime, then it will get the values when passed through the for-each. Could just do inline, but it's a clever concept so leaving it here.
$StartTime= @{n='StartTime';e={$_.ConvertToDateTime($_.CreationDate)}}

#get list of servers we want to look at

mbourgon

USE tempdb
/*
2015/04/22 - mdb - 2.00 - pull raw CSV, shred via function, pivot into a virtual table, then insert into
							actual table based on existing fields.  Practically: forwards/backwards compatability.
							Add new fields to your target table, and as they show up in the new file they'll be added.
							Practically, they'll have to rename the file when they do this, so that we know the 
							names of the new fields. But that's just a parameter change in the job. 
2015/05/05 - mdb - 2.1 - very basic implementation, complete code. Just fill out the parameters!
Downside: SLOW?!  A 5mb file, which has 106k rows, takes 7 seconds.

mbourgon

#Note this is JUST the capture portion; there's a database component as well
# the 4 scripts below are obviously prereqs, from Hey Scripting Guy and PoshCode
clear 
. C:\powershell_scripts\invoke-sqlcmd2.ps1;
. C:\powershell_scripts\split-job.ps1;
. C:\powershell_scripts\write-datatable.ps1;
. C:\powershell_scripts\out-datatable.ps1;

$server_repository = 'yourreposerverhere'  
$database_repository = 'yourrepodbhere'