SQL Database Report Revised

Last year I wrote an article that explained how to use the SQLSERVER PSDrive to create an HTML report highlighting some server and database information. If you want a refresher you can find that article here. In short, you can install the SQL Server PowerShell module on your client desktop and use it to manage remote servers.
During a PowerShell class I was teaching this week, I included some content on the SQL cmdlets and had an opportunity to revisit the original script. Even though the original version was intended to work with remote servers, I had a few bugs that prevented that from happening. I also decided to include some additional information as well as incorporate more conditional formatting. For example, depending on how much free space is left, the report might format the value in yellow, to indicate a warning or in red to indicate something more critical.

In the SQLSERVER PSdrive, the Databases directory doesn't show any system databasese by default unless you use -Force.

So I updated the script to allow you to include those databases if you want.

Here is the revised script:

#requires -version 4.0
#requires -module SQLPS


<#
Create a SQL Server database report
This script includes a graphic file which by default should be in the
same directory as this script. Otherwise, edit the $ImageFile variable. 

It is recommended that you import the SQLPS module first and then run 
this script.

Usage:
c:\scripts\SQLServerReport -computername CHI-SQL01 -includesystem -path c:\work\CHI-SQL01-DB.htm


#>

[cmdletbinding()]

Param(
[Parameter(Position=0,HelpMessage="Enter the name of a SQL server")]
[ValidateNotNullorEmpty()]
[Alias("CN")]
[string]$computername=$env:computername,
[Parameter(Position=1,HelpMessage="Enter the named instance such as Default")]
[ValidateNotNullorEmpty()]
[string]$Instance = "Default",
[switch]$IncludeSystem,
[ValidateNotNullorEmpty()]
[string]$Path="$env:temp\sqlrpt.htm",
[switch]$ShowReport
)


$scriptversion = "2.0"

Write-Verbose "Starting $($MyInvocation.Mycommand)"

#define the path to the graphic
$graphic = "db.png"
#the default location is the same directory as this script
$imagefile = Join-path -path (split-path $MyInvocation.InvocationName) -ChildPath $graphic

#define an empty array to hold all of the HTML fragments
$fragments=@("<br><br><br>")

#get uptime
Write-Verbose "Getting SQL Server uptime"
Try {
    #try to connect to the SQL server
    $starttime = Invoke-Sqlcmd -Query 'SELECT sqlserver_start_time AS StartTime FROM sys.dm_os_sys_info' -ServerInstance $computername -database master -ErrorAction Stop
}
Catch {
    Write-warning "Can't connect to $computername. $($_.exception.message)"
    #bail out
    Return
}

Write-Verbose "Getting SQL Version" 
$version = Invoke-Sqlcmd "Select @@version AS Version,@@ServerName AS Name" -ServerInstance $computername

#create an object
$uptime = New-Object -TypeName PSObject -Property @{
 StartTime = $starttime.Item(0)
 Uptime = (Get-Date)-$starttime.Item(0)
 Version = $version.Item(0).replace("`n","|")
}

$tmp = $uptime | ConvertTo-HTML -fragment -AS List
#replace "|" place holder with <br>"
$fragments += $tmp.replace("|","<br>")

#get services
Write-Verbose "Querying SQL services"
$services = Get-Service -DisplayName *SQL* -ComputerName $computername | 
Select Name,Displayname,Status

#add conditional formatting to display stopped services in yellow
[xml]$html = $services | ConvertTo-Html -fragment

#check each row, skipping the TH header row
for ($i=1;$i -le $html.table.tr.count-1;$i++) {
  $class = $html.CreateAttribute("class")
  #check the value of the last column and assign a class to the row
  if ($html.table.tr[$i].td[-1] -ne 'Running') {                                          
    $html.table.tr[$i].lastChild.setAttribute("class","warn") | Out-Null
  }  
} #for


$fragments += "<h3>SQL Services</h3>"
$fragments += $html.InnerXML

#get database information
#path to databases
$dbpath = "SQLServer:\SQL\$computername\$instance\databases"
Write-Verbose "Querying database information from $dbpath"

if ($IncludeSystem) {
    Write-Verbose "Including system databases"
    $dbs = Get-ChildItem -path $dbpath -Force
} else {
    $dbs = Get-ChildItem -path $dbpath
}

[xml]$html = $dbs | Select Name,
@{Name="SizeMB";Expression={$_.size}},
@{Name="DataSpaceMB";Expression={$_.DataSpaceUsage}},
@{Name="AvailableMB";Expression={$_.SpaceAvailable}},
@{Name="PercentFree";Expression={ [math]::Round((($_.SpaceAvailable/1kb)/$_.size)*100,2) }} | 
Sort PercentFree | ConvertTo-HTML -fragment

for ($i=1;$i -le $html.table.tr.count-1;$i++) {
  $class = $html.CreateAttribute("class")
  #check the value of the last column and assign a class to the row
  if (($html.table.tr[$i].td[-1] -as [double]) -le 15) {                                          
    $html.table.tr[$i].lastChild.SetAttribute("class","danger") | Out-Null
  }
  elseif (($html.table.tr[$i].td[-1] -as [double]) -le 25) {                                               
    $class.value = "warn"    
    $html.table.tr[$i].lastChild.SetAttribute("class","warn") | Out-Null
  }
}
$fragments += "<h3>Database Utilization</h3>"
$fragments += $html.InnerXml

$fragments += "<h3>Database Backup</h3>"
$fragments += $dbs | Select Name,Owner,CreateDate,Last*,RecoveryModel | ConvertTo-Html -Fragment

#volume usage
Write-Verbose "Querying system volumes"
$data = Get-CimInstance win32_volume -filter "drivetype=3" -ComputerName $computername

$drives = foreach ($item in $data) {
    $prophash = [ordered]@{
    Drive = $item.DriveLetter
    Volume = $item.DeviceID
    Compressed = $item.Compressed
    SizeGB = $item.capacity/1GB -as [int]
    FreeGB = "{0:N4}" -f ($item.Freespace/1GB )
    PercentFree = [math]::Round((($item.Freespace/$item.capacity) * 100),2)
    }

    #create a new object from the property hash
    New-Object PSObject -Property $prophash
}

[xml]$html = $drives | ConvertTo-Html -fragment

#check each row, skipping the TH header row
for ($i=1;$i -le $html.table.tr.count-1;$i++) {
  $class = $html.CreateAttribute("class")
  #check the value of the last column and assign a class to the row
  if (($html.table.tr[$i].td[-1] -as [int]) -le 25) {                                          
    $html.table.tr[$i].lastChild.SetAttribute("class","danger") | Out-Null
  }
  elseif (($html.table.tr[$i].td[-1] -as [int]) -le 35) {                                               
    $class.value = "warn"    
    $html.table.tr[$i].lastChild.SetAttribute("class","warn") | Out-Null
  }
}

$fragments += "<h3>Volume Utilization</h3>"
$fragments += $html.innerxml

#define the HTML style
Write-Verbose "preparing report"

#encode the graphic file to embed into the HTML
$ImageBits = [Convert]::ToBase64String((Get-Content $imagefile -Encoding Byte))
$ImageHTML = "<img src=data:image/png;base64,$($ImageBits) alt='db utilization'/>"

#define a here string for the html header
$head = @"
<style>
body { background-color:#FAFAFA;
       font-family:Arial;
       font-size:12pt; }
td, th { border:1px solid black; 
         border-collapse:collapse; }
th { color:white;
     background-color:black; }
table, tr, td, th { padding: 2px; margin: 0px }
tr:nth-child(odd) {background-color: lightgray}
table { margin-left:50px; }
img
{
float:left;
margin: 0px 25px;
}
.danger {background-color: red}
.warn {background-color: yellow}
</style>
$imagehtml
<br><br><br>
<H2>SQL Server Report: $($version.name)</H2>
<br>
"@

#HTML to display at the end of the report
$footer = @"
<br>
<i>
Date&nbsp;&nbsp;&nbsp;&nbsp;: $(Get-Date)<br>
Author&nbsp;: $env:USERDOMAIN\$env:username<br>
Version: $scriptVersion<br>
</i>
"@

#create the HTML document
ConvertTo-HTML -Head $head -Body $fragments -PostContent $footer |
Out-File -FilePath $path -Encoding ascii

if ($ShowReport) {
    #open the finished report
    Write-Verbose "Opening report $path"
    Invoke-Item $path
}

Write-Verbose "Ending $($MyInvocation.Mycommand)"

My script includes a graphic file to make it pretty. You can download my graphic file db.png. Put the graphic in the same directory as the script. Or modify the script to use your own graphics. You can then run a command like this to generate the HTML report.

c:\scripts\sqlserverreport.ps1 -computername chi-sql01 -IncludeSystem -path s:\chi-sql01.htm -verbose

This script lacks a provision for alternate credentials and assumes the account you are running under has the necessary SQL permissions. When finished you can get a report like this.

I hope you'll let me know what you think and if you find this useful.

---

---