SQL Powershell script need help in modification

This topic contains 9 replies, has 2 voices, and was last updated by Profile photo of M1981 M1981 1 month, 1 week ago.

  • Author
    Posts
  • #56282
    Profile photo of M1981
    M1981
    Participant

    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)

  • #56302
    Profile photo of Rob Simmers
    Rob Simmers
    Participant

    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:

  • #56326
    Profile photo of M1981
    M1981
    Participant

    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: (:) [ConvertTo-Html], ParameterBindingException
    + FullyQualifiedErrorId : AmbiguousParameterSet,Microsoft.PowerShell.Commands.ConvertToHtmlCommand

  • #56329
    Profile photo of M1981
    M1981
    Participant

    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)

  • #56377
    Profile photo of Rob Simmers
    Rob Simmers
    Participant

    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.

  • #56408
    Profile photo of M1981
    M1981
    Participant

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

  • #56414
    Profile photo of M1981
    M1981
    Participant

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

  • #56480
    Profile photo of M1981
    M1981
    Participant

    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

  • #56527
    Profile photo of Rob Simmers
    Rob Simmers
    Participant

    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

  • #56531
    Profile photo of M1981
    M1981
    Participant

    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.

You must be logged in to reply to this topic.