When I started in my current position at the beginning of the year, one of the first things that I did was to schedule a sit down with the various teams of Analysts that exist in our organization to find out more about their systems. One thing I am always interested in is the manual processes that people do routinely that might be able to be automated. A couple of the analyst mentioned that they routinely run queries in their systems to identify issues so that they can proactively fix them before they become an actual problem. My first response was, this sounds like a good candidate for Reporting Services Subscriptions, so I asked for a sample of the queries that they were running. What I got was a couple of emails with dozens of TSQL Script files attached to them, as well as comments that as new issues arise, new scripts are created to identify those problems.
I fully intended on creating Reports in Reporting Services for each of the scripts, but the time has never presented itself. One of the requirements was for the Reports to only generate if data actually was returned by the script. I asked on Twitter if this was possible using data driven subscriptions and was told that it was, but not being a SSRS person, this ended up on the back burner waiting on time to learn how exactly to go about creating the appropriate data driven report to accomplish the goal. I had originally planned to create one or two of the reports and then go back and teach the analysts how to create the reports on a development Reports Server themselves. Once they had been tested, I would them migrate them to the production server and setup the appropriate subscriptions for them to automate the process.
Fast forward four months, and this project has never even gotten off the ground. While it was important to me, and important to the analysts, other more important tasks always relegated this to the back burner. Last week when one of the analysts asked me about this again, and sent along a new batch of scripts that needed to be included, I got the idea to attack the problem from a different angle. I’ve been using PowerShell a good bit lately to pull information from SQL Server, so why not leverage what I know and tackle this problem in a easily scalable manner?
I know that this is completely reinventing the wheel, but it actually took less time to create a solution to this problem using PowerShell than it took to write this blog post. I provided the analysts a sample TSQL file that had a commented header section in it that contained information that would be used by a PowerShell script to control execution of the scripts similar to the following:
/*******************************************************************************************\
* *
* Name: <Script Name> *
* EmailTo: <Email Address List> *
* Description: <Description for the Email> *
* HowToFix: <Information on how to fix the problem> *
* Server: <SQL Server Name> *
* Database: <Database Name> *
* *
\*******************************************************************************************/
-- SELECT Statement that should return no results
The header information is parsed using simple regular expressions that look for the header information followed by an asterisk, capturing the information contained for each of the required pieces of information. Then a connection is made to the SQL Server and Database using SMO and the contents of the TSQL query file are executed. If a result set is returned, the script formats that information into an HTML Table using the ConvertTo-Html commandlet. This was the first place I ran into a problem, because the HTML output included five additional columns (RowError, RowState, HasErrors, Name, Table, ItemArray) that are properties of the .NET DataRow object. Some help from Twitter by Doug Finke (Blog|Twitter) showed me that by using the –ExcludeProperty option I could drop these columns from the output HTML table. for pretty formatting, a -Style in CSS form is applied in the ConvertTo-Html commandlet, and the Description and HowToFix sections of the header are added as the –Body of the resulting HTML document. Then a email is sent through an internal SMTP relay to the address(es) specified in the header information.
The PowerShell Script only requires one input parameter $ScriptPath, which is a UNC share that has all the scripts in it, allowing the analysts to update and add scripts as needed to the mix of items that are being monitored. A parameter was used to allow for segregation between groups for the script files and to better organize the scripts into logical groups. The PowerShell script for this process is:
param( [Parameter(Mandatory=$TRUE)]
[ValidateNotNullOrEmpty()]
[string]$ScriptPath=$(Throw `
"Parameter missing: -ScriptPath Path"))
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
[string]$style = "<style type='text/css'>
BODY{background-color:#FFFFFF;font-family:Verdana;}
TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;font-size:12px;}
TH{border-width: 1px;padding: 0px;border-style: solid;border-color: black;background-color:#D3D3D3 }
TD{border-width: 1px;padding: 0px;border-style: solid;border-color: black;background-color:#FFFFFF }
</style>"
[string]$Body = "<p><b>Description:</b></p>
$Description
<p><b>How to Fix:</b></p>
<p>$HowToFix</p>
<p> </p>"
Get-ChildItem $ScriptPath -Filter "*.sql" | % {
[string]$sqlcmd = ([system.string]::join("`r`n", (Get-Content $_.FullName)))
[string]$ScriptName = [regex]::Match($sqlcmd, "ScriptName:\t(.*?)\*").Groups[1].Value.Trim()
[string]$Server = [regex]::Match($sqlcmd, "Server:\t(.*?)\*").Groups[1].Value.Trim()
[string]$Database = [regex]::Match($sqlcmd, "Database:\t(.*?)\*").Groups[1].Value.Trim()
[string]$Description = [regex]::Match($sqlcmd, "Description:\t(.*?)\*").Groups[1].Value.Trim()
[string]$HowToFix = [regex]::Match($sqlcmd, "HowToFix:\t(.*?)\*").Groups[1].Value.Trim()
[string]$EmailTo = [regex]::Match($sqlcmd, "EmailTo:\t(.*?)\*").Groups[1].Value.Trim()
$smosvr = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $Server
$smodb = $smosvr.Databases[$Database];
$result = $smodb.ExecuteWithResults($sqlcmd)
if ($result.Tables[0].Rows.Count -ne 0)
{
[string]$emailbody = $result.Tables[0].Rows | `
select * -ExcludeProperty RowError, RowState, HasErrors, Name, Table, ItemArray | `
ConvertTo-Html -Head $style -Body $Body
$emailFrom = "monitor@domain.com"
$subject = "$ScriptName returned results"
$message = New-Object Net.Mail.MailMessage($emailFrom, $emailTo, $subject, $emailbody)
$message.IsBodyHTML = $true
$smtpServer = "smtp.domain.com"
$smtp = New-Object Net.Mail.SmtpClient($smtpServer)
$smtp.Send($message)
}
}
I fully understand that this is reinventing the wheel, but in less than an hour, including the time it took to get assistance with removing the additional columns from the DataRow object from Twitter, the problem was solved, and a time consuming manual process was automated.