Retrieving Partition Size, Free Space and Fragmentation Percentage from multiple servers

At my current client we receive an email every morning with partition information from a number of servers. This is run using VBScript and was set up a while ago. So, for one, the list of servers is outdated. The biggest bit of information missing from this report for me is the fragmentation percent of the partitions. I say this because one of the primary reasons for keeping enough free space available in a partition is to ensure effective defrags can run.

So I gathered my Server list. This, after going through numerous spreadsheets, amounted to 483 servers! Powershell eats this for a little morsel.

Getting the Partition size and Free space is a straighforward GWMI query in Powershell. Getting the fragmentation percent in the same result set requires some fancy footwork. My mate, Jean Louw, added this information using Add-Member. When that cmdlet is mentioned to him his eyes lose focus and his nether regions tighten. Check out his blog at: http://powershellneedfulthings.blogspot.com/.

So getting down to some code. Here I've written a function that retrieves The ServerName, DriveLetter, Label, Capacity, FreeSpace, PercentFree and Fragmentation Percent. I've left this data raw without fancy formatting since I'm going to export this to a csv and then bulk insert the csv into a SQL table and then present the information in Reporting Services. In T-SQL and Reporting Services I'll embelish the data as required.

Here is the function:

Function Get-FreeSpaceFrag ($s)
{ 
trap {write-host "Can't connect to WMI on server $s"  -ForeGroundColor "Red" 
 continue 
 } 
$dt = get-date 

$Scope = new-object System.Management.ManagementScope "\\$s\root\cimv2" 
$query = new-object System.Management.ObjectQuery "SELECT * FROM Win32_Volume" 
$searcher = new-object System.Management.ManagementObjectSearcher $scope,$query 
$SearchOption = $searcher.get_options() 
$timeout = new-timespan -seconds 10 
$SearchOption.set_timeout($timeout) 
$SearchOption 
$searcher.set_options($SearchOption) 
$volumes = $searcher.get() 

            $fr = {foreach ($v in $volumes | where {$_.capacity -gt 0}){ 
            $frag=($v.defraganalysis().defraganalysis).totalPercentFragmentation 
            $v | Add-Member -Name Frag -MemberType NoteProperty -Value $frag -Force -PassThru 
            } } 
$fr.invoke() | select @{N="Server";E={$_.Systemname}}, DriveLetter, Label, Capacity, FreeSpace, @{N="PercentFree";E={"{0,9:N0}" -f (($_.FreeSpace/1gb)/($_.Capacity/1gb)*100)}}, Frag, @{N="InfoDate";E={$dt}}

}

The magic here is the Add-Member cmdlet getting my fragmentation percent. The other piece of magic is making sure the WMI query times out after 10 seconds!! Man did I battle with this. It took me a whole morning to get this right, and I start working at 6AM! The trick here is to instantiate the WMI object before invoking it. Then you can set the timeout using the new-timespan cmdlet and some properties of the ManagementObjectSearcher Object. I had a server, smack in the middle of my list of 483 servers, that broke my script before I added this error handling. Thats ugly, in a script that takes almost 4 hours. And when I say broke, Ctrl+C doesn't even work. Click on the X baby.

Great now I have my unit of work defined: Gather required information from one server. Its go time! Now I use this to gather the information from a list of servers, export the results to csv, time the entire operation of gathering the data and finally bulk insert the data into a SQL table. Code:

sl E:\Powershell\ServerDriveSpaceFragInfo
. ./Function_Get-FreeSpaceFrag.ps1
sl ..
. ./function_Get-TimeDelta.ps1
$svl = gc 'serversall.txt'
$x = {foreach ($s in $svl) {write-host "Getting Disk Info for Server $s" -foregroundcolor "Green"; Get-FreeSpaceFrag $s; start-sleep -s 60; break}}
$t1 = get-date
$x.invoke() | export-csv "D:\Powershell\DiskInfo.csv" -NoTypeInformation
$t2 = get-date
Get-TimeDelta $t1 $t2
cpi "C:\data\DiskInfo.csv" "\\SRV1\d$"
sl D:\Powershell\ServerDriveSpaceFragInfo
./BulkInsertDiskInfo.ps1

I save the above as a .ps1 file. I then call the ps1 file from SQL server using a SQL Agent job. The job step will be operating system (CmdExec) Type and the text would be:
"C:\WINDOWS\system32\windowspowershell\v1.0\powershell.exe" "D:\Powershell\DiskInfo\Eg_Get-FreeSpaceFrag.ps1"

For 483 servers this job runs for about 3 hours 43 minutes. Not bad considering they are all over the country, across some slow WAN links. The bulk insert code is as follows :

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=SRV1;Database=master;Integrated Security=True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "use DBA; TRUNCATE TABLE DriveSpaceFragInfo;"
$SqlCmd.Connection = $SqlConnection
$SqlConnection.Open()
$sqlCmd.ExecuteNonQuery()
$SqlConnection.Close()
$SqlCmd.CommandText = "BULK INSERT DBA..DriveSpaceFragInfo FROM 'D:\DiskInfo.csv' WITH (FIELDTERMINATOR = ',', FIRSTROW = 2, ROWTERMINATOR = '\n')"
$SqlCmd.Connection = $SqlConnection
$SqlConnection.Open()
$sqlCmd.ExecuteNonQuery()
$SqlConnection.Close()
$SqlCmd.CommandText = "Exec DBA..usp_UpdateDriveSpaceFragInfo"
$SqlCmd.Connection = $SqlConnection
$SqlConnection.Open()
$sqlCmd.ExecuteNonQuery()
$SqlConnection.Close()

The 3rd command runs a stored procedure to clean up the data. Basically just removing double quotes and one or two other things. Now I have nice clean partition information that provides a wealth of information. Such as a Server that was hosting 2 Virtual Servers, had 35% free space on the data partition and was 94% fragmented. 94%!!!! I have never seen such a high figure before. So the fact that the partition has 35% free space doesn't mean the partition is in a healthy state.

I then created some reporting services reports to show the top 20 partitions with the least amount of free space and another report with the top 20 worst fragmented partitions. Nice. So now from a possible 1500 partitions on 483 servers I can target the least healthy partitions first. Also, I keep the partition information in the database. So over time I can report on how long a partition has been in a certain state, when it was cleaned or defraged, and how quickly it got filled up and fragmented again. Makes management happy. ;-).