Folder and Disk Space utilization report

rated by 0 users
This post has 7 Replies | 3 Followers

Top 200 Contributor
Posts 14
squeekie Posted: 04-20-2011 4:28 PM

I have a portion of a script that creates an excel spreadsheet and populates the drives for remote servers, their size as well as used and percentage. (Found it here I believe) What I need to add to this spreadsheet are all of the directories that are listed under the drives by using the get-content pointing to my .txt file.

I.E. list only directories for each of the logical drives for the server

here is what I have found thus far, I am able to create the new column, I am not able to populate the information into it.

$strComputer = Get-Content <path to file>

$Excel = New-Object -Com Excel.Application
$Excel.visible = $True
$Excel = $Excel.Workbooks.Add()

$Sheet = $Excel.WorkSheets.Item(1)
$Sheet.Cells.Item(1,1) = “Computer”
$Sheet.Cells.Item(1,2) = “Drive Letter”
$Sheet.Cells.Item(1,3) = “Description”
$Sheet.Cells.Item(1,4) = “FileSystem”
$Sheet.Cells.Item(1,5) = “Size in GB”
$Sheet.Cells.Item(1,6) = “Free Space in GB”
$Sheet.Cells.Item(1,7) = "Free Space in %"
$Sheet.Cells.Item(1,8) = "Directories"

$WorkBook = $Sheet.UsedRange
$WorkBook.Interior.ColorIndex = 8
$WorkBook.Font.ColorIndex = 11
$WorkBook.Font.Bold = $True

$intRow = 2
$wmi1 = Get-wmiObject -class “Win32_LogicalDisk” -filter "DriveType = 3" -namespace “root\CIMV2" -computername $strComputer

foreach ($objItem in $wmi1) {
$Sheet.Cells.Item($intRow,1) = $objItem.SystemName
$Sheet.Cells.Item($intRow,2) = $objItem.DeviceID
$Sheet.Cells.Item($intRow,3) = $objItem.Description
$Sheet.Cells.Item($intRow,4) = $objItem.FileSystem
$Sheet.Cells.Item($intRow,5) = $objItem.Size / 1GB
$Sheet.Cells.Item($intRow,6) = $objItem.FreeSpace / 1GB
$Sheet.Cells.Item($IntRow,7) = ($objItem.FreeSpace / 1GB) / (($objItem.Size / 1GB))*100

<the next line of code would go here to populate the fields for each server>

$intRow = $intRow + 1
}

$WorkBook.EntireColumn.AutoFit()
Clear

------------what I am trying to do now----------------------------

What I have done to retrieve the folders, I run this after I created a psdrive for u:\

get-childitem u:\ -recurse | where-object {$_.psIscontainer} | Where-Object {$_.fullname.Count -ne 0 }

Only because I have not been able to query the servers in my .txt file.  My thought is to have a nested foreach and take the output of the variable and add it to the cell.  Once the query is complete for that server, it begins to query the next server on the list and populate the next row in excel.

 

Top 25 Contributor
Posts 287
Top Contributor

$strComputer = Get-Content <path to file>

$Excel = New-Object -Com Excel.Application
$Excel.visible = $True
$Excel = $Excel.Workbooks.Add()

$Sheet = $Excel.WorkSheets.Item(1)
$Sheet.Cells.Item(1,1) = “Computer”
$Sheet.Cells.Item(1,2) = “Drive Letter”
$Sheet.Cells.Item(1,3) = “Description”
$Sheet.Cells.Item(1,4) = “FileSystem”
$Sheet.Cells.Item(1,5) = “Size in GB”
$Sheet.Cells.Item(1,6) = “Free Space in GB”
$Sheet.Cells.Item(1,7) = "Free Space in %"
$Sheet.Cells.Item(1,8) = "Directories"

$WorkBook = $Sheet.UsedRange
$WorkBook.Interior.ColorIndex = 8
$WorkBook.Font.ColorIndex = 11
$WorkBook.Font.Bold = $True

$intRow = 2
$wmi1 = Get-wmiObject -class “Win32_LogicalDisk” -filter "DriveType = 3" -namespace “root\CIMV2" -computername $strComputer

foreach ($objItem in $wmi1) {
$Sheet.Cells.Item($intRow,1) = $objItem.SystemName
$Sheet.Cells.Item($intRow,2) = $objItem.DeviceID
$Sheet.Cells.Item($intRow,3) = $objItem.Description
$Sheet.Cells.Item($intRow,4) = $objItem.FileSystem
$Sheet.Cells.Item($intRow,5) = $objItem.Size / 1GB
$Sheet.Cells.Item($intRow,6) = $objItem.FreeSpace / 1GB
$Sheet.Cells.Item($IntRow,7) = ($objItem.FreeSpace / 1GB) / (($objItem.Size / 1GB))*100
$Sheet.Cells.Item($IntRow,8) = Get-ChildItem \\$strComputer\u -recurse | where-object {$_.psIscontainer} | select Name
$intRow = $intRow + 1
}

$WorkBook.EntireColumn.AutoFit()
Clear

Top 200 Contributor
Posts 14

Felipe,

Thx for the quick response.  The one issue I have with what I scripted in my get-childItem is I had to map a drive.  I did this outside of the main script to test various methods to pull directories.  What I'd like to do is identify via wmi the logical drives for each of the servers listed in the txt file.  That keeps the script looking at one file and re-using the content.

so if we did a foreach-object loop that gets the content list, we could then create a $wmi2 variable with the win32_logical disk -filter names and then perform the -recurse method.  The output would then populate each cell in column 8 until all directories are listed.  We can leave this at top level directory for now.

Top 25 Contributor
Posts 287
Top Contributor

So you want to iterate through all the available drives?

If so:

$strComputer = Get-Content <path to file>


$Excel = New-Object -Com Excel.Application
$Excel.visible = $True
$Excel = $Excel.Workbooks.Add()


$Sheet = $Excel.WorkSheets.Item(1)
$Sheet.Cells.Item(1,1) = “Computer”
$Sheet.Cells.Item(1,2) = “Drive Letter”
$Sheet.Cells.Item(1,3) = “Description”
$Sheet.Cells.Item(1,4) = “FileSystem”
$Sheet.Cells.Item(1,5) = “Size in GB”
$Sheet.Cells.Item(1,6) = “Free Space in GB”
$Sheet.Cells.Item(1,7) = "Free Space in %"
$Sheet.Cells.Item(1,8) = "Directories"


$WorkBook = $Sheet.UsedRange
$WorkBook.Interior.ColorIndex = 8
$WorkBook.Font.ColorIndex = 11
$WorkBook.Font.Bold = $True


$intRow = 2
$wmi1 = Get-wmiObject -class “Win32_LogicalDisk” -filter "DriveType = 3" -namespace “root\CIMV2" -computername $strComputer


$wmi2 = Get-wmiObject -class “Win32_LogicalDisk” -filter "DriveType = 3" -namespace “root\CIMV2" -computername $strComputer | select name

foreach ($objItem in $wmi1) {
$Sheet.Cells.Item($intRow,1) = $objItem.SystemName
$Sheet.Cells.Item($intRow,2) = $objItem.DeviceID
$Sheet.Cells.Item($intRow,3) = $objItem.Description
$Sheet.Cells.Item($intRow,4) = $objItem.FileSystem
$Sheet.Cells.Item($intRow,5) = $objItem.Size / 1GB
$Sheet.Cells.Item($intRow,6) = $objItem.FreeSpace / 1GB
$Sheet.Cells.Item($IntRow,7) = ($objItem.FreeSpace / 1GB) / (($objItem.Size / 1GB))*100

foreach($letter in $wmi2){
$drive = $letter.name.split(":","")
$Sheet.Cells.Item($IntRow,8) = Get-ChildItem \\$strComputer\$drive -recurse | where-object {$_.psIscontainer} | select Name

}
$intRow = $intRow + 1
}


$WorkBook.EntireColumn.AutoFit()
Clear

Top 200 Contributor
Posts 14

Felipe,

That is what I wanted to get accomplished.  I have to work around the authentication to these shares now.  I added a function to export credentials and have the script import them, but it doesn't support interactive.  I'm still looking for the authentication to work.

I'm looking for a method to authenticate using an admin account, but I don't want to hardcode the password.  If I call the function prior to the script running, the interactive and have that stored for the remainder of the iterations, would that be a possibility?  I added a function that changes the drive letters to c$, d$ etc.  It looks into a file for the drive letters.  This seems to work fine, but I get an access denied.  I will also have a test loop to ensure the share exists.  here is what I have as a function located at the top of the script block:

#List of servers to be used
$strComputer = Get-Content C:\copiedto\test1\devserverstestgroup.txt

#Function to supply credentials for admin shares
function export-credential($cred, $path) {   
    $cred = $cred | Select-Object *   
    $cred.password = $cred.Password | ConvertFrom-SecureString   
    $cred | Export-Clixml $path
}

function Import-credential($path) {   
    $cred = Import-Clixml $path   
    $cred.password = $cred.Password | ConvertTo-SecureString   
    New-Object system.Management.Automation.PSCredential(     
        $cred.username, $cred.password)
}

Export-Credential (Get-Credential) c:\copiedto\test1\cred.xml

#Function to change logical disk letters to admin shares
function ChangePath($path) {  
    $qualifier = Split-Path $path -qualifier  
    $drive = $qualifier.substring(0,1)  
    $noqualifier = Split-Path $path -noQualifier  
    "$drive`$$noqualifier"
}
get-content c:\copiedto\test1\devserverstestgroup.txt | Foreach-Object {  
    $server = $_  
    get-content "c:\copiedto\test1\drivetest.txt" | Foreach-Object {     
        get-content "
\\$server\$(ChangePath $_)"  -credential (Import-Credential c:\copiedto\test1\cred.xml)
    }
}

$Excel = New-Object -Com Excel.Application
$Excel.visible = $True
$Excel = $Excel.Workbooks.Add()

$Sheet = $Excel.WorkSheets.Item(1)
$Sheet.Cells.Item(1,1) = “Computer”
$Sheet.Cells.Item(1,2) = “Drive Letter”
$Sheet.Cells.Item(1,3) = “Description”
$Sheet.Cells.Item(1,4) = “FileSystem”
$Sheet.Cells.Item(1,5) = “Size in GB”
$Sheet.Cells.Item(1,6) = “Free Space in GB”
$Sheet.Cells.Item(1,7) = "Free Space in %"
$Sheet.Cells.Item(1,8) = "Directories"

$WorkBook = $Sheet.UsedRange
$WorkBook.Interior.ColorIndex = 8
$WorkBook.Font.ColorIndex = 11
$WorkBook.Font.Bold = $True

$intRow = 2
$wmi1 = Get-wmiObject -class “Win32_LogicalDisk” -filter "DriveType = 3" -namespace “root\CIMV2" -computername $strComputer
$wmi2 = Get-wmiObject -class "Win32_logicaldisk" -filter "DriveType = 3" -namespace "root\CIMv2" -computername $strComputer | select name
foreach ($objItem in $wmi1) {
$Sheet.Cells.Item($intRow,1) = $objItem.SystemName
$Sheet.Cells.Item($intRow,2) = $objItem.DeviceID
$Sheet.Cells.Item($intRow,3) = $objItem.Description
$Sheet.Cells.Item($intRow,4) = $objItem.FileSystem
$Sheet.Cells.Item($intRow,5) = $objItem.Size / 1GB
$Sheet.Cells.Item($intRow,6) = $objItem.FreeSpace / 1GB
$Sheet.Cells.Item($IntRow,7) = ($objItem.FreeSpace / 1GB) / (($objItem.Size / 1GB))*100

foreach ($letter in $wmi2){
$drive = $letter.name.split(":","")
$Sheet.Cells.Item($IntRow,8) = Get-childItem ChangePath -recurse | where-object {$_.psiscontainer} | Select Name
}
$intRow = $intRow + 1
}

$WorkBook.EntireColumn.AutoFit()

Top 25 Contributor
Posts 287
Top Contributor

You could just prompt for credentials before running the script..

    $cred = get-credential
    .
    .
    .
    $Sheet.Cells.Item($IntRow,8) = Get-childItem ChangePath -recurse -credential $cred | where-object {$_.psiscontainer} | Select Name


   

Top 150 Contributor
Posts 20

Felipe,

Thank you for this

Could you possible update this script so that the servers with the lowest % of space available list first?

Also could those with less than 10% show in red, 15% in orange etc

This would be really helpfull

Thank you

 

Top 25 Contributor
Posts 287
Top Contributor

Sorry don't know how to do it and would have to spend too much time getting it right.

I think it's easier to customize it using Excel itself. Just insert filters.

Cheers

Page 1 of 1 (8 items) | RSS
Copyright 2012 PowerShell.com. All rights reserved.