Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Man of Mystery

Wes Brown is a PASS chapter leader and SQL Server MVP. He writes for SQL Server Central and maintains his blog at http://www.sqlserverio.com. Wes is Currently serving as a Senior Lead Consultant at Catapult Systems. Previous experiences include Product Manager for SQL Litespeed by Quest software and consultant to fortune 500 companies. He specializes in high availability, disaster recovery and very large database performance tuning. He is a frequent speaker at local user groups and SQLSaturdays.

Parsing Glenn Berry’s Diagnostic Information Queries With PowerShell

Do It In Bulk

I usually don’t run queries on just one server. Most of the time I need to get some piece of information from dozens if not hundreds of servers at a time. I also really like Glenn’s diagnostic queries and use them regularly with some modifications and additions of my own. I’m also really digging PowerShell too.

You Got Your Queries In My PowerShell

PowerShell is a very powerful way to gather information across lots of servers. I’ve got a set of templating files that drive my PowerShell scripts and thought I’d apply some of that to Glenn’s queries. The result is a function that takes a the file name of Glenn’s queries and outputs just the server level or the database level queries as an array that you can then loop through to query any number of servers or databases! It currently works with all three sets of files.

<#
.SYNOPSIS
Parse Glenn  Berry's DMV Diagnostic Queries into arrays http://sqlserverperformance.wordpress.com/
.DESCRIPTION
Does what it says so you can then loop through the array and execute the queries against a server ro database!
.PARAMETER FileName
FileName the name of the file you wish to process
.PARAMETER QueryType
QueryType ether server level or database level queries

.EXAMPLE
parseGABDMV '<path to sql file>\SQL Server 2005 Diagnostic Information Queries(December 2012).sql' server
#>
function parseGBDMV{
	[cmdletbinding(
		DefaultParameterSetName = '',
		ConfirmImpact = "low"
	)]
	Param(
		[Parameter(
			Mandatory = $True,
			Position = 0,
			ParameterSetName = '',
			ValueFromPipeline = $True)]
		[string]$FileName,
		[Parameter(
			Mandatory = $True,
			Position = 1,
			ParameterSetName = '',
			ValueFromPipeline = $True)]
		[string]$QueryType
	)
	Begin {
		$ErrorActionPreference = "SilentlyContinue"
		$found = $false
		$endquery = $false
		$startDatabaseQueries = $false
		$ServerQueries = @()
		$DatabaseQueries = @()
		$Query = "";
	}
	Process {
		Get-Content $File | foreach-Object {
			$_ = $_ -replace '\r\n$', "" #strip out empty lines
			$_ = $_ -replace '\t+', ""
			if ($_.Length -ne 0){
				if ($_.trim() -ne ""){
					if ($_.trim() -ne "`t"){
						$String = $_.toString();
					}
					else{
						$String = ""
					}
				}
				else{
					$String = ""
				}
			}
			else{
				$String = ""
			}

			if($found) {
				if($String -notlike "--*"){
					if($String.Trim() -ne ""){
						if($String.Length -ne 0){
							$Query += $String + "`r`n"
						}
					}
				}
			}

			if($String -like "--*"){
				if($String -cmatch '\(Query'){
					$found = $true
					$endquery = $true
					$Query = $Query -replace '\r\n$', ""
					if ($String.trim() -ne ""){
						if ($startDatabaseQueries){
							if (!$Query.Contains("USE YourDatabaseName")){
								$DatabaseQueries += $Query
							}
						}
						else{
							$ServerQueries += $Query
						}
					}
					$Query = ""
				}
			}

			if ($endqery) {
				$found = $false
			}
			if ($String -ne $null){
				if ($String.Contains("-- Database specific queries")){
					$startDatabaseQueries = $true
				}
				if ($String.Contains("-- Switch to user database *******************")){
					$startDatabaseQueries = $true
				}
			}
		}
		#dunno why always have a blank line on the first server query list
		$ServerQueries = $ServerQueries[1 .. ($ServerQueries.count-1)]
		if ($QueryType.ToUpper() -eq "SERVER"){
			return $ServerQueries
		}
		if ($QueryType.ToUpper() -eq "DATABASE"){
			return $DatabaseQueries
		}
	}
}

It may not be the cleanest PowerShell file parser on the planet but it seems to work and speed really isn’t an issue since the files are so small.

Enjoy!

The post Parsing Glenn Berry’s Diagnostic Information Queries With PowerShell appeared first on .

Comments

Leave a comment on the original post [sqlserverio.com, opens in a new window]

Loading comments...