SQL Powershell help

This topic contains 1 reply, has 2 voices, and was last updated by Profile photo of Matt Howard Matt Howard 1 month ago.

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

    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 ('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 = $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)

  • #57065
    Profile photo of Matt Howard
    Matt Howard
    Participant

    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"

You must be logged in to reply to this topic.