Solved

PowerShell Script to list drive free space in MS Excel

Posted on 2014-04-07
7
745 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
Comment Utility
the following PowerShell script
0
 

Author Comment

by:hchabria
Comment Utility
$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
Comment Utility
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 18

Expert Comment

by:Raheman M. Abdul
Comment Utility
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
Comment Utility
@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
Comment Utility
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
Comment Utility
Awesome! Thank you so much. It's working fine now as expected.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

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…
Set OWA language and time zone in Exchange for individuals, all users or per database.
This video discusses moving either the default database or any database to a new volume.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

762 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

10 Experts available now in Live!

Get 1:1 Help Now