[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 936
  • Last Modified:

PowerShell Script to list drive free space in MS Excel

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
hchabria
Asked:
hchabria
  • 3
  • 2
  • 2
1 Solution
 
Dan CraciunIT ConsultantCommented:
the following PowerShell script
0
 
hchabriaAuthor Commented:
$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
 
Dan CraciunIT ConsultantCommented:
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
Creating Active Directory Users from a Text File

If your organization has a need to mass-create AD user accounts, watch this video to see how its done without the need for scripting or other unnecessary complexities.

 
Raheman M. AbdulSenior Infrastructure Support Analyst & Systems DeveloperCommented:
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
 
hchabriaAuthor Commented:
@Raheman-
The script is working fine except D and E columns in Excel are coming under red.
Output
0
 
Raheman M. AbdulSenior Infrastructure Support Analyst & Systems DeveloperCommented:
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
 
hchabriaAuthor Commented:
Awesome! Thank you so much. It's working fine now as expected.
0

Featured Post

Simplify Active Directory Administration

Administration of Active Directory does not have to be hard.  Too often what should be a simple task is made more difficult than it needs to be.The solution?  Hyena from SystemTools Software.  With ease-of-use as well as powerful importing and bulk updating capabilities.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now