Need help in modifying powershell script

• vsamantha35

  SSChampion

  Points: 11529

  More actions

  October 27, 2016 at 12:53 pm

  #314988

  Hi Powershell Experts,

  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. PFA sample output for single instance.

  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 = "<style>"

  $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 + "</style>"

  $dbfl | ConvertTo-HTML -head "<H2>Database File Space Report </H2>" -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)

• Gary Varga

  SSC Guru

  Points: 82166

  More actions

  October 27, 2016 at 1:27 pm

  #1909224

  Isn't the connection to a single instance?

  Gaz

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

• Jeff Moden

  SSC Guru

  Points: 1003853

  More actions

  October 27, 2016 at 4:13 pm

  #1909258

  vsamantha35 (10/27/2016)

  ---

  Hi Powershell Experts,

  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. PFA sample output for single instance.

  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 = "<style>"

  $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 + "</style>"

  $dbfl | ConvertTo-HTML -head "<H2>Database File Space Report </H2>" -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)

  Wouldn't it be easier to setup CMS in SQL Server, which would provide you with additional capabilities that you wouldn't need to write additional scripts for.

  And, to fix your PoS script, you'd need a nested loop where the outside loop is for servers.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)