Powershell Disk Usage Report

Question

I have the following script that will collect disk usage stats from remote servers. I'm trying to get the output to display the host name rather than the IP from the .\ServerIPs.txt file the script is reading. I have tried a retrieve the "SystemName" from get-wmiobject Win32_LogicalDisk but could not get the name to display. What should I add to allow the host name to be displayed in the report rather than the host IP address?

$erroractionpreference = "SilentlyContinue" 
$a = New-Object -comobject Excel.Application 
$a.visible = $True  

$b = $a.Workbooks.Add() 
$c = $b.Worksheets.Item(1) 

$c.Cells.Item(1,1) = "Server Name" 
$c.Cells.Item(1,2) = "Drive" 
$c.Cells.Item(1,3) = "Total Size (GB)" 
$c.Cells.Item(1,4) = "Free Space (GB)" 
$c.Cells.Item(1,5) = "Free Space (%)" 

$d = $c.UsedRange 
$d.Interior.ColorIndex = 19 
$d.Font.ColorIndex = 11 
$d.Font.Bold = $True 

$intRow = 2 

$colComputers = get-content ".\ServerIPs.txt" 
foreach ($strComputer in $colComputers) 
{ 
  $colDisks = get-wmiobject Win32_LogicalDisk -computername $strComputer -Filter "DriveType = 3"  
  foreach ($objdisk in $colDisks) 
  { 
    $c.Cells.Item($intRow, 1) = $strComputer.ToUpper() 
    $c.Cells.Item($intRow, 2) = $objDisk.DeviceID 
    $c.Cells.Item($intRow, 3) = "{0:N0}" -f ($objDisk.Size/1GB) 
    $c.Cells.Item($intRow, 4) = "{0:N0}" -f ($objDisk.FreeSpace/1GB) 
    $c.Cells.Item($intRow, 5) = "{0:P0}" -f ([double]$objDisk.FreeSpace/[double]$objDisk.Size) 
    $intRow = $intRow + 1 
  } 
}

What have you tried to get the name retrieved to display? I don't see `$objDisk.SystemName` anywhere. You might try adding `-Property DeviceID, SystemName, Size, FreeSpace` to your `Get-WmiObject` call. — Bacon Bits, Dec 27 '14 at 00:21
you could do `[System.Net.Dns]::GetHostByAddress("x.x.x.x")` to get the hostname from your dns if for some reason wmi is not working for you — Paul, Dec 27 '14 at 00:38
Thanks for your help. @BaconBits I replaced the $strComputer.ToUpper() with $objDisk.SystemName and that gave me what I was looking for in the output. Thanks again for your help! — KSpinnato, Dec 27 '14 at 02:10

score 1 · Answer 1 · answered Dec 27 '14 at 03:17

Thanks for the help @BaconBits. I also added a date column to the output.

$erroractionpreference = "SilentlyContinue" 
$a = New-Object -comobject Excel.Application 
$a.visible = $True  

$b = $a.Workbooks.Add() 
$c = $b.Worksheets.Item(1) 

$c.Cells.Item(1,1) = "Server Name" 
$c.Cells.Item(1,2) = "Drive" 
$c.Cells.Item(1,3) = "Total Size (GB)" 
$c.Cells.Item(1,4) = "Free Space (GB)" 
$c.Cells.Item(1,5) = "Free Space (%)"
$c.Cells.Item(1,6) = "Date" 

$d = $c.UsedRange 
$d.Interior.ColorIndex = 19 
$d.Font.ColorIndex = 11 
$d.Font.Bold = $True 

$intRow = 2 

$colComputers = get-content ".\ServerIPs.txt" 
foreach ($strComputer in $colComputers) 
{ 
  $colDisks = get-wmiobject Win32_LogicalDisk -computername $strComputer -Filter "DriveType = 3"  
  foreach ($objdisk in $colDisks) 
  { 
    $c.Cells.Item($intRow, 1) = $objDisk.SystemName 
    $c.Cells.Item($intRow, 2) = $objDisk.DeviceID 
    $c.Cells.Item($intRow, 3) = "{0:N0}" -f ($objDisk.Size/1GB) 
    $c.Cells.Item($intRow, 4) = "{0:N0}" -f ($objDisk.FreeSpace/1GB) 
    $c.Cells.Item($intRow, 5) = "{0:P0}" -f ([double]$objDisk.FreeSpace/[double]$objDisk.Size) 
    $c.Cells.Item($intRow, 6) = Get-Date
    $intRow = $intRow + 1 
  } 
}

Kory Gill · Answer 2 · 2016-03-12T08:28:45.790

Because many people might use the code shown in other answers, it might also be worth calling out that to properly and completely close Excel, you also need to release COM references. In my own testing have found removing the variable for Excel also ensures no remaining references exist which will keep Excel.exe open (like if you are debugging in the ISE).

Without performing the above, if you look in Task Manager, you may see Excel still running...in some cases, many copies.

This has to do with how the COM object is wrapped in a “runtime callable wrapper".

Here is the skeleton code that should be used:

$excel = New-Object -ComObject Excel.Application
$excel.Visible = $true
$workbook = $excel.Workbooks.Add()
# or $workbook = $excel.Workbooks.Open($xlsxPath)

# do work with Excel...

$workbook.SaveAs($xlsxPath)
$excel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)
# no $ needed on variable name in Remove-Variable call
Remove-Variable excel

Powershell Disk Usage Report

2 Answers2

Linked