SQL Powershell script need help in modification

PowerShell Help
1

Hi Powershell Experts,

Need help in modifying SQL Server based powershell script. I’am not a expert in Powershell.
I have below PS script which will display the database file info like dbname, physicalfilename, size, free,used etc. It is working fine for 1 instance. However, I want this to run against multiple sql instances and in the output I want to see 1st column as InstanceName which is not there in the below code.

Also, for each instance, the output should like as follows

Server 1:
Table grid data for that instance.

Server 2:
Table grid data for that instance.

Server 3:
Table grid data for that instance.

Code:
$svr = new-object (‘Microsoft.SqlServer.Management.Smo.Server’) localhost
$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

$dfl = $fl | select @{Name=“DBName”; Expression={$dbname}}, Name, @{Name=“Directory”; Expression={$dirnm}}, @{Name=“FileName”; Expression={$filnm}}, @{Name=“Size(MB)”;Expression={$fl.Size/1024}}, @{Name=“UsedSpace(MB)”;Expression={$fl.UsedSpace/1024}}, @{Name=“FreeSpace(MB)”;Expression={($fl.Size - $fl.UsedSpace)/1024}} ,@{Name=“Used%”;Expression={(($fl.UsedSpace/$fl.Size)*100)}},@{Name=“Free%”;Expression={((($fl.Size-$fl.UsedSpace)/$fl.Size)*100)}}

$dbfl += $dfl
}
}
foreach ($fl in $db.LogFiles) {
$dirnm = $fl.FileName | Split-Path -Parent
$filnm = $fl.FileName | Split-Path -Leaf
$dfl = $fl | select @{Name=“DBName”; Expression={$dbname}}, Name, @{Name=“Directory”; Expression={$dirnm}}, @{Name=“FileName”; Expression={$filnm}}, @{Name=“Size(MB)”;Expression={$fl.Size/1024}}, @{Name=“UsedSpace(MB)”;Expression={$fl.UsedSpace/1024}}, @{Name=“FreeSpace(MB)”;Expression={($fl.Size - $fl.UsedSpace)/1024}}, @{Name=“Used%”;Expression={(($fl.UsedSpace/$fl.Size)*100)}},@{Name=“Free%”;Expression={((($fl.Size-$fl.UsedSpace)/$fl.Size)*100)}}
$dbfl += $dfl
}
}

Format for HTML

$a = “”

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

$dbfl | ConvertTo-HTML -head "Database File Space Report " -body $a | Out-File D:\Scripts\Datafile.html
$EmailFrom = “xxxxx@gmail.com”
$EmailTo = “aaa@gmail.com,bbb@gmail.com”
$Subject = “Datafile size report”
$Body = Get-Content(“D:\Scripts\Datafile.html”)
$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

You need to modularize your code into a function so you can call it for each instance. I cleaned up some other items, but this is a rough idea of what your code would look like:

3

Hi Rob,

Thank you very much for help. Don’t know why I am getting below error when I try to execute the PS.

ConvertTo-Html : Parameter set cannot be resolved using the specified named parameters.
At C:\Scripts\p1.ps1:51 char:13

  • $dbfl | ConvertTo-HTML -Title ("Report for {0}" -f $instance) -Fragment

  •         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    • CategoryInfo : InvalidArgument: (:slight_smile: [ConvertTo-Html], ParameterBindingException
    • FullyQualifiedErrorId : AmbiguousParameterSet,Microsoft.PowerShell.Commands.ConvertToHtmlCommand
4

Hi Rob,

I was able to send email succussfully. But I need the instance name to be printed instead of “Computer Info” in the below line.
$dbfl | ConvertTo-HTML -Fragment -PreContent ‘Computer Info’

Could you please help me fixing it?

complete code :

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

)
begin {
    $svr = new-object ('Microsoft.SqlServer.Management.Smo.Server') $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="DBName"; Expression={$dbname}}, 
                             @{Name="Directory"; Expression={$dirnm}}, 
                             @{Name="FileName"; Expression={$filnm}}, 
                             @{Name="Size(MB)";Expression={$fl.Size/1024}}, 
                             @{Name="UsedSpace(MB)";Expression={$fl.UsedSpace/1024}}, 
                             @{Name="FreeSpace(MB)";Expression={($fl.Size – $fl.UsedSpace)/1024}} ,
                             @{Name="Used%";Expression={(($fl.UsedSpace/$fl.Size)*100)}},
                             @{Name="Free%";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="DBName"; Expression={$dbname}},  
                         @{Name="Directory"; Expression={$dirnm}}, 
                         @{Name="FileName"; Expression={$filnm}}, 
                         @{Name="Size(MB)";Expression={$fl.Size/1024}}, 
                         @{Name="UsedSpace(MB)";Expression={$fl.UsedSpace/1024}}, 
                         @{Name="FreeSpace(MB)";Expression={($fl.Size – $fl.UsedSpace)/1024}}, 
                         @{Name="Used%";Expression={(($fl.UsedSpace/$fl.Size)*100)}},
                         @{Name="Free%";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 "Report for 1" -Fragment 
$dbfl | ConvertTo-HTML -Fragment -PreContent '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 = $instanceReportHTML | 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)

5

First, there is no need to create new posts for the same questions. Second, use the proper code wrapper which is BOLDED above the new post block or since you are posting HTML, use the third bullet which is a GIST URL.

Your first question should be a simple fix, try this:

$dbfl | ConvertTo-HTML -Fragment -PreContent ("Report for {0}" -f $instance)

Your next question about changing the color or a column based on criteria is a bit more complicated. I would suggest starting with the Creating HTML Reports in PowerShell in the eBooks menu above. The simple answer is you’ll need to manually generate the table versus using ConvertTo-HTML. There are a lot of examples of Diskspace HTML reports that can be used as reference for what you are asking.

6

Many Thanks for the timely help Rob. Definitely I ll take a look at those ebooks.

7

Rob, any help on the conditional formatting like YELLOW color background for Free% values < 10 ?

8

Hi Rob,

How can we sort the data based on a column? I want to sort data based of “FreeSpace(MB)”. How can we achieve it?

Thanks,
Sam

9

To sort, you would use Sort-Object:

$dbfl | Sort-Object "FreeSpace(MB)" | ConvertTo-HTML -Fragment -PreContent ("Report for {0}" -f $instance)

Again, there are A LOT of examples of disk reports. They will show you the way to generate a table manually and change the background color:

https://gallery.technet.microsoft.com/scriptcenter/Disk-Space-Report-Reports-98e64d65

10

Still trying to figure out a way to fix the background color. Where can I fit td bgcolor=YELLOW if “Free%” -lt 10?
I am not so familiar/confident with the powershell constructs. I am just trying to learn from others. Its a little bit over my head!!! I am afraid to fix this script.

I know I am asking too much help but checking if you can help fixing the bgcolor.

Thank you.