Visualizing and Automating SQL Server health with PowerShell and Excel


I was surfing on twitter (almost do not like doing that) when I saw a message from one of the big names in the SQL Server community on the world , Sir Robert Davis  (@SQLSoldier) asking for a solution to export SQL Server queries to Excel, maybe using Powershell.

As most of the time I’m intruding on others’ conversations on twitter (I know I must stop this) I shoot a DM to Robert asking if I could help with anything. Actually I had nothing done, need to do.
After some emails and a lot of DM´s with my questions (sorry man, Sometimes I need  to draw for understand) I could get what Robert wanted (at least I think).

Robert has his clients and he need a simple solution to gather information quering  a repository database that he has in each client and the information should be in a excel report. BUT the report must be in worksheets at the same Excel file.

Of course I will not show what querys that Robert use and I beleive that the script that I sent to him is specific to his job. But I worked in a new one that can be used for anyone.

The idea is that you have .sql files in a folder with the querys that you want to gather. Remember that this querys can be any kind of the T-SQL Script, but always with one resultset.

Then we just will load this files and report them into a Excel file, split into worksheets.

The PowerShell Solution must be simple, objective and functional as the person who will run it on the client does not need to have ANY knowledge in PowerShell. Just Click, maybe input some informations as Server Name, Database Name, User Name , PassWord to connect. Only this. Other point to be observed is that the computar that will run the PowerShell script does not necessarily need to have the Sql Server (or only client)  or SMO installed . At least .Net  framework I believe it will. So we will use Net for to do the job.

First Lets Create a folder to our .sql files and the posh script called HealthCheck, c:\healtcheck. In my case this folder is in hardcode inside the script and of course I could do this as well ,  but for me the more conditions that I put in the script, more will be possible for errors. But it is up to you.

Now lets take a look in the scipt :

   1: #region Functions 

   2:  

   3:  

   4: function Write-ScriptLog

   5: {

   6:     param($msg,$date,$ReportOn)

   7:     if ($ReportOn -ne "")

   8:         {    Add-Content -Path (Join-Path $ReportOn "SQLDmRepository_$($ServerName)_$($date).log") -Value  $msg}

   9:     Write-Output $msg    

  10: }

  11:  

  12: function Show-MessageBox ($title, $msg) {      

  13:     [System.Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms") | Out-Null  

  14:     [Windows.Forms.MessageBox]::Show($msg, $title, [Windows.Forms.MessageBoxButtons]::OK, [System.Windows.Forms.MessageBoxIcon]::Warning, [System.Windows.Forms.MessageBoxDefaultButton]::Button1, [System.Windows.Forms.MessageBoxOptions]::DefaultDesktopOnly) | Out-Null      

  15: }  

  16:   

  17: function Show-InformationBox ([string]$Title,[string]$Message) {  

  18:     [System.Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms") | Out-Null  

  19:     [Windows.Forms.MessageBox]::Show($Message, $Title, [Windows.Forms.MessageBoxButtons]::OK, [System.Windows.Forms.MessageBoxIcon]::Information, [System.Windows.Forms.MessageBoxDefaultButton]::Button1, [System.Windows.Forms.MessageBoxOptions]::DefaultDesktopOnly) | Out-Null      

  20: }

  21:  

  22:  

  23:  

  24: #endregion functions

  25:  

  26: #region Script

  27: Function Gather-Result {

  28:     param(

  29:         [Parameter(Position=0, Mandatory=$true)] [String]$Server,

  30:         [Parameter(Position=1, Mandatory=$true)] [String]$UserName,

  31:         [Parameter(Position=2, Mandatory=$true)] [String]$Password,

  32:         [Parameter(Position=2, Mandatory=$true)] [String]$Database

  33:      )

  34:  

  35:     Get-Process "Excel*" | Stop-Process

  36:     #$Server = '.'

  37:     #$UserName = 'xx'

  38:     #$Password = 'xxxxxxxxx'

  39:  

  40:     #Variables 

  41:     #$Database = "SQLDMRepository"

  42:     

  43:     #Variable to Change The Folder ##################################################################

  44:     $ReportOn = "c:\HealthCheck"

  45:     #Variable to Change The Folder ##################################################################

  46:     

  47:     

  48:     $ConnectionFailed = $false

  49:     $Date = get-date -format 'yyyy_MM_dd'

  50:     

  51:     #test path . If does not exist break the code

  52:     if (-not (Test-Path -Path $reporton -PathType Container)) {

  53:         Show-MessageBox -msg "Path $($ReportOn) does not exist" -title 'Error'

  54:         break

  55:     }

  56:  

  57:     try {

  58:     #Connect to SQL Server using .net

  59:         $SqlConnection = New-Object System.Data.SqlClient.SqlConnection

  60:         $SqlConnection.ConnectionString = "Server = $($Server); Database = $($Database); User = $($Username); PWD = $($Password)"

  61:         $SqlConnection.Open()

  62:     } catch {

  63:         Show-MessageBox -msg "Error to connect to Server $($Server) Additional information at log file" -title 'Error Connection'

  64:         Write-ScriptLog "$($date) : Operation FAILED for server $($Server) Database $($Database). Error details $($error[0])" -date $($date) -reporton $($ReportOn)                                                

  65:         break

  66:     }

  67:         

  68:     Try { 

  69:             $Excel = New-Object -Com Excel.Application 

  70:             #$Excel.visible = $True

  71:             $Excel = $Excel.Workbooks.Add() 

  72:             $NameFile = " HealthCheck_$(get-date -format 'yyyy_MM_dd')"

  73:             $NameFileTest = "$($NameFile).*"

  74:             $path = Join-Path -Path $($ReportOn) -ChildPath $NameFile

  75:             $pathTest = Join-Path -Path $($ReportOn) -ChildPath $NameFileTest

  76:             

  77:             if (Test-Path $pathTest -PathType Any) {

  78:                 try {

  79:                     Remove-Item -Path $pathTest -Force

  80:                 }    catch {

  81:                         Show-MessageBox -msg "The file $($NameFile) is using by another process" -title 'Error '

  82:                         Write-ScriptLog "$($date) : Operation FAILED for server $($Server) Database $($Database). Error details $($error[0])" -date $($date) -reporton $($ReportOn)                                                

  83:                         break

  84:                 }

  85:             }    

  86:  

  87:             $Excel.saveas($path) 

  88:  

  89:             $HeaderHash = @{}

  90:             $Itens = 1

  91:             $Files = Get-childitem $ReportOn -Filter "*.sql"

  92:             

  93:             

  94:             $Files  | % {

  95:                 $NumberOfWorkSheets = $Files.count

  96:                 

  97:  

  98:                 $SqlQuery = Get-Content $_.fullname 

  99:                 $SqlCmd = New-Object System.Data.SqlClient.SqlCommand

 100:                 $SqlCmd.CommandText = $SqlQuery

 101:                 $SqlCmd.Connection = $SqlConnection

 102:                 $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter

 103:                 $SqlAdapter.SelectCommand = $SqlCmd

 104:                 $DataSet = New-Object System.Data.DataSet

 105:  

 106:                 $SqlAdapter.Fill($DataSet) | Out-Null

 107:                 

 108:                 $DataExcel = $DataSet.Tables[0] | select * -ExcludeProperty 'RowError','RowState','Table','ItemArray','HasErrors' 

 109:  

 110:                 for ( $i = $Excel.Worksheets.count ;$i -lt $NumberOfWorkSheets; $i++) {

 111:                      $Excel.Worksheets.add() 

 112:                      }    

 113:                 Write-Host "Processing File $($_.Name)"

 114:                 $Name = "$($_.name)                                                                                             "

 115:                 $Excel.Worksheets.Item($Itens).name = $Name.substring(0,30)

 116:  

 117:                 $Sheet = $Excel.Worksheets.Item($Itens) 

 118:                 $property=@()

 119:                 

 120:                 For ( $i = 0 ;$i -lt  $DataSet.Tables[0].columns.count ; $i++) {

 121:                          $property += @($DataSet.Tables[0].columns[$i].columnname)

 122:                      }

 123:                 

 124:                 $Column = 1

 125:                 $Row = 1

 126:                 foreach ($header in $property) {

 127:                     $HeaderHash[$header] = $Column

 128:                     $Sheet.Cells.Item($Row,$Column) = $header.toupper()

 129:                     $Column ++

 130:                 }

 131:  

 132:                 $WorkBook = $Sheet.UsedRange

 133:                 $WorkBook.Interior.ColorIndex = 19

 134:                 $WorkBook.Font.ColorIndex = 11

 135:                 $WorkBook.Font.Bold = $True

 136:                 $WorkBook.HorizontalAlignment = -4108

 137:  

 138:                 $Row ++

 139:                 $DataExcel | % {

 140:                     foreach ($header in $property) {

 141:                         if ($thisColumn = $HeaderHash[$header]) {

 142:                             $Sheet.Cells.Item($Row,$thisColumn) = [string]$_.$header

 143:                         }

 144:                     }

 145:                     $Row ++

 146:                 }    

 147:                 $Excel.save()

 148:                 $Itens ++

 149:             }

 150:         }  catch {

 151:                     Write-ScriptLog $("$(get-date -format yyyy/MM/dd) : Operation FAILED for server $($Server) Database $($Database). Error details $($error[0])") -date $($date) -reporton $($ReportOn)                                                

 152:         } Finally {

 153:                 $SqlConnection.Close()

 154:                 $Excel.Close()

 155:                 Write-Host "Finished"

 156:                 Show-MessageBox -msg "Finished !!! "

 157:         }    

 158:     

 159:  

 160: }    

 161:  

 162:  

 163: #endregion Script

 164:  

 165: Gather-Result

The next Step is to create a .bat file calling the script.

The Region Functions has the that I need to my script. There is 3 functions :

Write-ScriptLog – I create this function to log any kind of error in a .log file located at the same folder that the others files (.sql and .ps1)

Show-MessageBox
Show-InformationBox
These  functions are in the PowerShell.com Script Library and I am using to show a message when the Script finishes or a connection failed. Also it is logged in the file .

In the Script Region :

I am using the parameters as Mandatory to input the information about the connection (SQL Server,Database, User Password).

   1: [Parameter(Position=0, Mandatory=$true)] [String]$Server,

   2: [Parameter(Position=1, Mandatory=$true)] [String]$UserName,

   3: [Parameter(Position=2, Mandatory=$true)] [String]$Password,

   4: [Parameter(Position=2, Mandatory=$true)] [String]$Database

Then I stop all processes that are using Excel. Why I am doing this ? when you work with Excel (COM Obect)  and for some reason the script stop (or crasch – whatever) unless you close it, the process still be active and locking the excel file. The script has a code to delete the file if it exists and if you o run the script again a message will be displayed :

Remove-Item : Cannot remove item C:\healthcheck\ HealthCheck_2012_05_09.xlsx: The process cannot access the file ‘C:\sqldm
repository\ HealthCheck_2012_05_09.xlsx’ because it is being used by another process.
At C:\healthcheck\GatherResultSets.ps1:78 char:17
+                     Remove-Item <<<<  -Path $pathTest -Force
    + CategoryInfo          : WriteError: (C:\sqldmreposit…2012_05_09.xlsx:FileInfo) [Remove-Item], IOException
    + FullyQualifiedErrorId : RemoveFileSystemItemIOError,Microsoft.PowerShell.Commands.RemoveItemCommand

   1: Get-Process "Excel*" | Stop-Process

Be in mind that it is better when run it, does not have any excel opened. It will be closed.

 

Then it is just the code to load the files and report to Excel.

   1: #region Functions 

   2:  

   3:  

   4: function Write-ScriptLog

   5: {

   6:     param($msg,$date,$ReportOn)

   7:     if ($ReportOn -ne "")

   8:         {    Add-Content -Path (Join-Path $ReportOn "SQLDmRepository_$($ServerName)_$($date).log") -Value  $msg}

   9:     Write-Output $msg    

  10: }

  11:  

  12: function Show-MessageBox ($title, $msg) {      

  13:     [System.Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms") | Out-Null  

  14:     [Windows.Forms.MessageBox]::Show($msg, $title, [Windows.Forms.MessageBoxButtons]::OK, [System.Windows.Forms.MessageBoxIcon]::Warning, [System.Windows.Forms.MessageBoxDefaultButton]::Button1, [System.Windows.Forms.MessageBoxOptions]::DefaultDesktopOnly) | Out-Null      

  15: }  

  16:   

  17: function Show-InformationBox ([string]$Title,[string]$Message) {  

  18:     [System.Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms") | Out-Null  

  19:     [Windows.Forms.MessageBox]::Show($Message, $Title, [Windows.Forms.MessageBoxButtons]::OK, [System.Windows.Forms.MessageBoxIcon]::Information, [System.Windows.Forms.MessageBoxDefaultButton]::Button1, [System.Windows.Forms.MessageBoxOptions]::DefaultDesktopOnly) | Out-Null      

  20: }

  21:  

  22:  

  23:  

  24: #endregion functions

  25:  

  26: #region Script

  27: Function Gather-Result {

  28:     param(

  29:         [Parameter(Position=0, Mandatory=$true)] [String]$Server,

  30:         [Parameter(Position=1, Mandatory=$true)] [String]$UserName,

  31:         [Parameter(Position=2, Mandatory=$true)] [String]$Password,

  32:         [Parameter(Position=3, Mandatory=$true)] [String]$Database

  33:      )

  34:  

  35:     

  36:     Get-Process "Excel*" | Stop-Process

  37: #    $Server = '.'

  38: #    $UserName = 'xx'

  39: #    $Password = 'xxxxx'

  40: #

  41: #    #Variables 

  42: #    $Database = "Database"

  43:     

  44:     #Variable to Change The Folder ##################################################################

  45:     $ReportOn = "c:\HealthCheck"

  46:     #Variable to Change The Folder ##################################################################

  47:     

  48:     

  49:     $ConnectionFailed = $false

  50:     $Date = get-date -format 'yyyy_MM_dd'

  51:     

  52:     #test path . If does not exist break the code

  53:     if (-not (Test-Path -Path $reporton -PathType Container)) {

  54:         Show-MessageBox -msg "Path $($ReportOn) does not exist" -title 'Error'

  55:         break

  56:     }

  57:  

  58:     try {

  59:     #Connect to SQL Server using .net

  60:         $SqlConnection = New-Object System.Data.SqlClient.SqlConnection

  61:         $SqlConnection.ConnectionString = "Server = $($Server); Database = $($Database); User = $($Username); PWD = $($Password)"

  62:         $SqlConnection.Open()

  63:     } catch {

  64:         Show-MessageBox -msg "Error to connect to Server $($Server) Additional information at log file" -title 'Error Connection'

  65:         Write-ScriptLog "$($date) : Operation FAILED for server $($Server) Database $($Database). Error details $($error[0])" -date $($date) -reporton $($ReportOn)                                                

  66:         break

  67:     }

  68:         

  69:     Try { 

  70:             $Excel = New-Object -Com Excel.Application 

  71:             #$Excel.visible = $True

  72:             $Excel = $Excel.Workbooks.Add() 

  73:             $NameFile = " HealthCheck_$(get-date -format 'yyyy_MM_dd')"

  74:             $NameFileTest = "$($NameFile).*"

  75:             $path = Join-Path -Path $($ReportOn) -ChildPath $NameFile

  76:             $pathTest = Join-Path -Path $($ReportOn) -ChildPath $NameFileTest

  77:             

  78:             if (Test-Path $pathTest -PathType Any) {

  79:                 try {

  80:                     Remove-Item -Path $pathTest -Force

  81:                 }    catch {

  82:                         Show-MessageBox -msg "The file $($NameFile) is using by another process" -title 'Error '

  83:                         Write-ScriptLog "$($date) : Operation FAILED for server $($Server) Database $($Database). Error details $($error[0])" -date $($date) -reporton $($ReportOn)                                                

  84:                         break

  85:                 }

  86:             }    

  87:  

  88:             $Excel.saveas($path) 

  89:  

  90:             $HeaderHash = @{}

  91:             $Itens = 1

  92:             $Files = Get-childitem $ReportOn -Filter "*.sql"

  93:             

  94:             

  95:             $Files  | % {

  96:                 $NumberOfWorkSheets = $Files.count

  97:                 

  98:  

  99:                 $SqlQuery = Get-Content $_.fullname 

 100:                 $SqlCmd = New-Object System.Data.SqlClient.SqlCommand

 101:                 $SqlCmd.CommandText = $SqlQuery

 102:                 $SqlCmd.Connection = $SqlConnection

 103:                 $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter

 104:                 $SqlAdapter.SelectCommand = $SqlCmd

 105:                 $DataSet = New-Object System.Data.DataSet

 106:  

 107:                 $SqlAdapter.Fill($DataSet) | Out-Null

 108:                 

 109:                 $DataExcel = $DataSet.Tables[0] | select * -ExcludeProperty 'RowError','RowState','Table','ItemArray','HasErrors' 

 110:  

 111:                 for ( $i = $Excel.Worksheets.count ;$i -lt $NumberOfWorkSheets; $i++) {

 112:                      $Excel.Worksheets.add() 

 113:                      }    

 114:                 Write-Host "Processing File $($_.Name)"

 115:                 $Name = "$($_.name)                                                                                             "

 116:                 $Excel.Worksheets.Item($Itens).name = $Name.substring(0,30)

 117:  

 118:                 $Sheet = $Excel.Worksheets.Item($Itens) 

 119:                 $property=@()

 120:                 

 121:                 For ( $i = 0 ;$i -lt  $DataSet.Tables[0].columns.count ; $i++) {

 122:                          $property += @($DataSet.Tables[0].columns[$i].columnname)

 123:                      }

 124:                 

 125:                 $Column = 1

 126:                 $Row = 1

 127:                 foreach ($header in $property) {

 128:                     $HeaderHash[$header] = $Column

 129:                     $Sheet.Cells.Item($Row,$Column) = $header.toupper()

 130:                     $Column ++

 131:                 }

 132:  

 133:                 $WorkBook = $Sheet.UsedRange

 134:                 $WorkBook.Interior.ColorIndex = 19

 135:                 $WorkBook.Font.ColorIndex = 11

 136:                 $WorkBook.Font.Bold = $True

 137:                 $WorkBook.HorizontalAlignment = -4108

 138:  

 139:                 $Row ++

 140:                 $DataExcel | % {

 141:                     foreach ($header in $property) {

 142:                         if ($thisColumn = $HeaderHash[$header]) {

 143:                             $Sheet.Cells.Item($Row,$thisColumn) = [string]$_.$header

 144:                         }

 145:                     }

 146:                     $Row ++

 147:                 }    

 148:                 $Excel.save()

 149:                 $Itens ++

 150:             }

 151:         }  catch {

 152:                     Write-ScriptLog $("$(get-date -format yyyy/MM/dd) : Operation FAILED for server $($Server) Database $($Database). Error details $($error[0])") -date $($date) -reporton $($ReportOn)                                                

 153:         } Finally {

 154:                 $SqlConnection.Close()

 155:                 $Excel.Close()

 156:                 Write-Host "Finished"

 157:                 Show-MessageBox -msg "Finished !!! " -title "Information"

 158:         }    

 159:     

 160:  

 161: }    

 162:  

 163:  

 164: #endregion Script

 165:  

 166: Gather-Result

The Excel report will be created at the same folder and with the name HEALTHCHECK_yyyy_MM_dd and  the worksheets will have the same name as the .sql files.

image

In order to not send to Robert a BIG Script and to be simple for him to use and configure in his clients (to not create module..etc) I create an function and put the excel code  inside it. But if You have your own environment I suggest you download this amazing function wrote by Luc Dekens(@lucd22) Beyond Export-Csv: Export-Xls and add at your module function.

The excel code is a cutomized version of the OUT-EXCEL from Pathological Scripter

To automate you can use a SQL Server job step type cmd and run this bat. Just pass the parameters sqlserver, database, user and password. You can change the code to accept windows authentication only in order to not show the credentials in the .bat.

This is an idea. You can customize to fit what you need !!! Alegre

I did a video showing th use of the Posh Script, and for the .sql scripts I used some dvm´s that I found in this Amzing (as always) resource from RedGate :

Watch the video !!!

RedGate – SQL Server DMV starter pack

Download The Code and/or  Video

 

WP_SM2_250x156

#PowerShellLIfeStyle

About Laerte Junior

Laerte Junior Laerte Junior is a SQL Server specialist and an active member of WW SQL Server and the Windows PowerShell community. He also is a huge Star Wars fan (yes, he has the Darth Vader´s Helmet with the voice changer). He has a passion for DC comics and living the simple life. "May The Force be with all of us"
This entry was posted in Powershell, Virtual Pass BR and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s