SQL Powershell help

• vsamantha35

  SSChampion

  Points: 11551

  More actions

  October 27, 2016 at 10:05 pm

  #318476

  Hi All,

  Need some help in SQL Powershell. I got the below script from the powershell community. 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)

• Gary Varga

  SSC Guru

  Points: 82166

  More actions

  October 28, 2016 at 2:55 am

  #1909313

  What type does it think $instance is?

  Gaz

  -- Stop your grinnin' and drop your linen...they're everywhere!!!

• Gary Varga

  SSC Guru

  Points: 82166

  More actions

  October 28, 2016 at 2:55 am

  #1909314

  Does $instance need to be in quotes? (Single or double - check to see which one allows variable expansion.)

  Gaz

  -- Stop your grinnin' and drop your linen...they're everywhere!!!

• Gary Varga

  SSC Guru

  Points: 82166

  More actions

  October 28, 2016 at 3:03 am

  #1909317

  As for formatting, I found using a search engine helped. My search came up with, amongst others, the following:

  http://stackoverflow.com/questions/4559233/technique-for-selectively-formatting-data-in-a-powershell-pipeline-and-output-as

  https://community.spiceworks.com/scripts/show/2450-change-cell-color-in-html-table-with-powershell-set-cellcolor

  Gaz

  -- Stop your grinnin' and drop your linen...they're everywhere!!!