Solved

PowerShell Script to list drive free space in MS Excel

Posted on 2014-04-07
7
758 Views
Last Modified: 2014-04-08
I am using the following PowerShell script to list down the percentage free space of disks for C: and E: drives only. But the problem is that E: drives are coming just on the next row of C: drive.

OutputBut I want the output like following so that both C: and E: drives come under same row and the blank cells will be colored in Red.

Please help me to do that.

Desired Output
0
Comment
Question by:hchabria
  • 3
  • 2
  • 2
7 Comments
 
LVL 34

Expert Comment

by:Dan Craciun
ID: 39982836
the following PowerShell script
0
 

Author Comment

by:hchabria
ID: 39982868
$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) = “Machine 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,3) = “Free Space (%)” 
#$c.cells.item(1,6) = "Name "
$c.Cells.Item(1,4) = “Drive” 
$c.Cells.Item(1,5) = "Free Space (%)"

$d = $c.UsedRange 
$d.Interior.ColorIndex = 19 
$d.Font.ColorIndex = 11 
$d.Font.Bold = $True 
$d.EntireColumn.AutoFit()

$intRow = 2
$intRow1 = 1

$colComputers = get-content "C:\MorningCheck\Servers.txt"

foreach ($strComputer in $colComputers) 
{

$colDisks = get-wmiobject Win32_LogicalDisk -computername $strComputer -Filter “DriveType = 3"

foreach ($objdisk in $colDisks) 
{ 
	if($objdisk.DeviceID -eq "C:")
	{
		$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, 3) = “{0:P0}” -f ([double]$objDisk.FreeSpace/[double]$objDisk.Size) 
#		$c.cells.item($introw, 6) = $objdisk.volumename

		$intRow = $intRow + 1 
	} 
		
	if($objdisk.DeviceID -eq "E:")
	{
#		$c.Cells.Item($intRow, 1) = $strComputer.ToUpper() 
		$c.Cells.Item($intRow, 4) = $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) = $objdisk.volumenam

		$intRow = $intRow + 1 
	} 
}
$d.EntireColumn.AutoFit()
}
cls

Open in new window

0
 
LVL 34

Expert Comment

by:Dan Craciun
ID: 39982891
You're skipping to the next row after C and E.
Just increment the row after both tests: remove lines 44 and 56.
You might end up with blank rows, but you can easily filter them after.
$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) = “Machine 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,3) = “Free Space (%)” 
#$c.cells.item(1,6) = "Name "
$c.Cells.Item(1,4) = “Drive” 
$c.Cells.Item(1,5) = "Free Space (%)"

$d = $c.UsedRange 
$d.Interior.ColorIndex = 19 
$d.Font.ColorIndex = 11 
$d.Font.Bold = $True 
$d.EntireColumn.AutoFit()

$intRow = 2
$intRow1 = 1

$colComputers = get-content "C:\MorningCheck\Servers.txt"

foreach ($strComputer in $colComputers) 
{

$colDisks = get-wmiobject Win32_LogicalDisk -computername $strComputer -Filter “DriveType = 3"

foreach ($objdisk in $colDisks) 
{ 
	if($objdisk.DeviceID -eq "C:")
	{
		$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, 3) = “{0:P0}” -f ([double]$objDisk.FreeSpace/[double]$objDisk.Size) 
#		$c.cells.item($introw, 6) = $objdisk.volumename

	} 
		
	if($objdisk.DeviceID -eq "E:")
	{
#		$c.Cells.Item($intRow, 1) = $strComputer.ToUpper() 
		$c.Cells.Item($intRow, 4) = $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) = $objdisk.volumenam

	} 
	$intRow = $intRow + 1 
}
$d.EntireColumn.AutoFit()
}
cls

Open in new window

0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 18

Expert Comment

by:Raheman M. Abdul
ID: 39982905
Try this:   (updated)
$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) = “Machine 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,3) = “Free Space (%)”
#$c.cells.item(1,6) = "Name "
$c.Cells.Item(1,4) = “Drive”
$c.Cells.Item(1,5) = "Free Space (%)"

$d = $c.UsedRange
$d.Interior.ColorIndex = 19
$d.Font.ColorIndex = 11
$d.Font.Bold = $True
$d.EntireColumn.AutoFit()

$intRow = 2
$intRow1 = 1

$colComputers = get-content "C:\MorningCheck\Servers.txt"

foreach ($strComputer in $colComputers)
{

$colDisks = get-wmiobject Win32_LogicalDisk -computername $strComputer -Filter “DriveType = 3"

foreach ($objdisk in $colDisks)
{
      if($objdisk.DeviceID -eq "C:")
      {
            $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, 3) = “{0:P0}” -f ([double]$objDisk.FreeSpace/[double]$objDisk.Size)
#            $c.cells.item($introw, 6) = $objdisk.volumename

      }
            
      if($objdisk.DeviceID -eq "E:")
      {
#            $c.Cells.Item($intRow, 1) = $strComputer.ToUpper()
            $c.Cells.Item($intRow, 4) = $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) = $objdisk.volumenam
      }
    else
    {
      $c.Cells.Item($intRow, 4).Interior.ColorIndex = 3
      $c.Cells.Item($intRow, 5).Interior.ColorIndex = 3
    }
            


}
$intRow = $intRow + 1
$d.EntireColumn.AutoFit()
}
cls
0
 

Author Comment

by:hchabria
ID: 39983013
@Raheman-
The script is working fine except D and E columns in Excel are coming under red.
Output
0
 
LVL 18

Accepted Solution

by:
Raheman M. Abdul earned 500 total points
ID: 39983049
Updated and the new code is:
#---------------------------------------
$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) = “Machine 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,3) = “Free Space (%)”
#$c.cells.item(1,6) = "Name "
$c.Cells.Item(1,4) = “Drive”
$c.Cells.Item(1,5) = "Free Space (%)"

$d = $c.UsedRange
$d.Interior.ColorIndex = 19
$d.Font.ColorIndex = 11
$d.Font.Bold = $True
$d.EntireColumn.AutoFit()

$intRow = 2
$intRow1 = 1

$colComputers = get-content "C:\MorningCheck\Servers.txt"

foreach ($strComputer in $colComputers)
{

$colDisks = get-wmiobject Win32_LogicalDisk -computername $strComputer -Filter “DriveType = 3"
$found=0
foreach ($objdisk in $colDisks)
{
      if($objdisk.DeviceID -eq "C:")
      {
            $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, 3) = “{0:P0}” -f ([double]$objDisk.FreeSpace/[double]$objDisk.Size)
#            $c.cells.item($introw, 6) = $objdisk.volumename

      }
   else {        
      if($objdisk.DeviceID -eq "e:")
      {
#            $c.Cells.Item($intRow, 1) = $strComputer.ToUpper()
            $c.Cells.Item($intRow, 4) = $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) = $objdisk.volumenam
      # $c.Cells.Item($intRow, 4).Interior.ColorIndex = 1
      # $c.Cells.Item($intRow, 5).Interior.ColorIndex = 1
      $found=1
      }
   
  }
 
}
if(!$found)
    {
      $c.Cells.Item($intRow, 4).Interior.ColorIndex = 3
      $c.Cells.Item($intRow, 5).Interior.ColorIndex = 3
    }
$intRow = $intRow + 1
$d.EntireColumn.AutoFit()
}
cls
0
 

Author Closing Comment

by:hchabria
ID: 39985865
Awesome! Thank you so much. It's working fine now as expected.
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I thought I'd write this up for anyone who has a request to create an anonymous whistle-blower-type submission form created using SharePoint 2010 (this would probably work the same for 2013). It's not 100% fool-proof but it's as close as you can get…
This script can help you clean up your user profile database by comparing profiles to Active Directory users in a particular OU, and removing the profiles that don't match.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now