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.
$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
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}
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.
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 nameforeach ($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))*100foreach($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
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}
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 sharesfunction 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 sharesfunction 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) }}
$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 nameforeach ($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()
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
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
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