SQL Powershell help

PowerShell Help
1

Hi All,

Need some help in modification of powershell script. Got this one from Rob Simmers who is one of the Powershell Community member.
This report works perfectly fine. This is sql powershell report which gives info abt the database files info like name,size,freespace …

Requirement 1: In the below line I want the instance name printed instead of “Computer Info” which is hard coded. I tried putting $insance put it didnt work!
$dbfl | ConvertTo-HTML -Fragment -PreContent ‘Computer Info’

Requirement 2: In the report there is a column called “Free%”, my requirement is , if the “Free%”<10 then the cell background color should change to YELLOW indicating a WARNING sign whenever user sees a report.

Complete Code:

function Get-DatabaseFileSpace {
param (
[string]$Instance = "LocalHost"

)
begin {
    $svr = new-object (&#039;Microsoft.SqlServer.Management.Smo.Server&#039;) $Instance
} #begin
process {
    $dbfl = foreach ($db in $svr.Databases) {
        $dbname = $db.Name
        foreach ($fg in $db.FileGroups) {
            foreach ($fl in $fg.Files) {
                $dirnm = $fl.FileName | Split-Path -Parent
                $filnm = $fl.FileName | Split-Path -Leaf

                $fl | select Name, 
                             @{Name=&quot;DBName&quot;; Expression={$dbname}}, 
                             @{Name=&quot;Directory&quot;; Expression={$dirnm}}, 
                             @{Name=&quot;FileName&quot;; Expression={$filnm}}, 
                             @{Name=&quot;Size(MB)&quot;;Expression={$fl.Size/1024}}, 
                             @{Name=&quot;UsedSpace(MB)&quot;;Expression={$fl.UsedSpace/1024}}, 
                             @{Name=&quot;FreeSpace(MB)&quot;;Expression={($fl.Size – $fl.UsedSpace)/1024}} ,
                             @{Name=&quot;Used%&quot;;Expression={(($fl.UsedSpace/$fl.Size)*100)}},
                             @{Name=&quot;Free%&quot;;Expression={((($fl.Size-$fl.UsedSpace)/$fl.Size)*100)}}
            } #foreach ($fl in $fg.Files)
        } #foreach ($fg in $db.FileGroups)

        foreach ($fl in $db.LogFiles) {
            $dirnm = $fl.FileName | Split-Path -Parent
            $filnm = $fl.FileName | Split-Path -Leaf
            $fl | select Name, 
                         @{Name=&quot;DBName&quot;; Expression={$dbname}},  
                         @{Name=&quot;Directory&quot;; Expression={$dirnm}}, 
                         @{Name=&quot;FileName&quot;; Expression={$filnm}}, 
                         @{Name=&quot;Size(MB)&quot;;Expression={$fl.Size/1024}}, 
                         @{Name=&quot;UsedSpace(MB)&quot;;Expression={$fl.UsedSpace/1024}}, 
                         @{Name=&quot;FreeSpace(MB)&quot;;Expression={($fl.Size – $fl.UsedSpace)/1024}}, 
                         @{Name=&quot;Used%&quot;;Expression={(($fl.UsedSpace/$fl.Size)*100)}},
                         @{Name=&quot;Free%&quot;;Expression={((($fl.Size-$fl.UsedSpace)/$fl.Size)*100)}}
        } #foreach ($fl in $db.LogFiles)
    } #foreach ($db in $svr.Databases)
} #process
end{$dbfl} #end

} #Get-DatabaseFileSpace

$instances = "Srv1","Srv2","Srv3"
$instanceReportHTML = foreach ($instance in $instances) {

#Get the data from the instance
$dbfl = Get-DatabaseFileSpace -Instance $instance
#Convert the data to a HTML fragment and send it to $instanceReportHTML
#$dbfl | ConvertTo-HTML -Title &quot;Report for 1&quot; -Fragment 
$dbfl | ConvertTo-HTML -Fragment -PreContent &#039;Computer Info'

}

Template for HTML

$html = @"

        BODY{
            background-color:white;
        }
        TABLE{
            font-family: Arial;
            font-size: 12px;
            width:100%; 
            height:75%; 
            border-width: 1px;
            border-style: solid;
            border-color: black;
            border-collapse: collapse;
        }
        TH{
            border-width: 1px;
            padding: 0px;
            border-style: solid;
            border-color: black;
            color:white; 
            background-color: green;
        }
        TD{
            border-width: 1px;
            padding: 0px;
            border-style: solid;
            border-color: black;
            background-color:white
        }
    


    Database File Space Report
    $instanceReportHTML

"@

$body = $html | Out-String
$EmailFrom = “xxxxx@gmail.com”
$EmailTo = “aaa@gmail.com,bbb@gmail.com”
$Subject = “Datafile size report”
$SMTPServer = “smtp.gmail.com”
$SMTPClient = New-Object Net.Mail.SmtpClient($SmtpServer, 587)
$SMTPClient.EnableSsl = $true
$SMTPClient.Credentials = New-Object System.Net.NetworkCredential(“xxxxx@gmail.com”, “pwd123”);
$message = New-Object Net.Mail.MailMessage($EmailFrom, $EmailTo, $Subject, $body)
$message.IsBodyHtml = $true;
$SMTPClient.Send($message)

2

On your ConvertTo-HTML line, try changing the value for -PreContent to $Instance (no quotes). For changing the color of the low space, you’ll have to set up some logic like:

if ($value < 10) {
    $color = 'yellow'
} else {
    $color = 'white'
}

Then change the value of the color in your Here-String for TD from “background-color:white” to “background-color:$color”