bringing it together! hash variable?

by staggerlee011 at 2013-03-28 06:55:09

Hi all,

i have list of sql servers, and want to find out the backup location and size of the folder. Ive managed to get all the info i want. but i cant see how to put it into a table?!

The code i have is:



$serverlist = get-content -path c:/serverlist.txt


$serverlist | ForEach-Object {


[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMO’) | out-null
$s = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’) $
$BackupDirectory = $s.Settings.BackupDirectory
$serverName = $s.name
$servername
$root = $BackupDirectory.Substring(0,1)
$folder = $BackupDirectory.Substring(3)
$pathname = “\” + $
+ "" + $root + "$" + $folder
$pathname
$BackupSize = ( Get-ChildItem $pathname -Recurse -Force | Measure-Object -Property Length -Sum ).Sum / 1GB
$BackupSize


}


Id like to have csv at the end with the following tables

ServerName, BackupDirectory, Size

Which i have in there as the variables ($serverName, $pathname, $BackupSize), but im not sure how to put them together!?

Any ideas?
by coderaven at 2013-03-28 07:15:58
I would recommend using a PSObject. Something like this would work inside your loop

$PSO = New-Object -Type PSObject
$PSO | Add-Member -MemberType NoteProperty -Name Server -Value $ServerName -PassThru |
Add-Member -MemberType NoteProperty -Name Path -Value $pathname -PassThru |
Add-Member -MemberType NoteProperty -Name Size -Value $BackupSize
$PSO


This works in PSv2 an v3 there are some new ways to do it and a lot of information out there if you just look for PSObject. Once you have it in this format, you can use Export-Csv to get it to file.
by staggerlee011 at 2013-03-28 07:45:00
hmm i had alreayd posted a ty but it doesnt seem to have posted… so ill try again!

thanks for the help!

Fully working script :



$serverlist = get-content -path c:/serverlist.txt

$results = @()
$serverlist | ForEach-Object {
[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMO’) | out-null
$s = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’) $
$BackupDirectory = $s.Settings.BackupDirectory
$serverName = $s.name
##$servername
$root = $BackupDirectory.Substring(0,1)
$folder = $BackupDirectory.Substring(3)
$pathname = “\” + $
+ "" + $root + "$" + $folder
##$pathname
$BackupSize = ( Get-ChildItem $pathname -Recurse -Force | Measure-Object -Property Length -Sum ).Sum / 1GB
##$BackupSize
$PSO = New-Object -Type PSObject
$PSO | Add-Member -MemberType NoteProperty -Name Server -Value $ServerName -PassThru |
Add-Member -MemberType NoteProperty -Name Path -Value $pathname -PassThru |
Add-Member -MemberType NoteProperty -Name Size -Value $BackupSize
$results += $PSO

}


$results | export-csv -path c:\summary23.csv