Powershell Invoke Command Export to Excel

Question

Having a few issues with this PowerShell code:

$vmslist = Import-Csv -Path .\vmsList.csv -Delimiter ',' | ForEach-Object {
    $_.Servers
    $vms = [string]$env:COMPUTERNAME
    $currentVms = $_.App_Central
    function Check-DiskSpace {
        $results = Invoke-Command -ComputerName $currentVms -Credential $Cred -ScriptBlock {
            $driveDetails = Get-PSDrive | Where-Object { $_.Name -eq 'D' } | ForEach-Object {
                $totalSpace = $_.Free + $_.Used
                $name = $_.Root
                $vms = $env:COMPUTERNAME

                $dDriveFreeSpace = [int]('{0:N2}' -f ($_.Free / 1Gb))
                $dDriveUsedSpace = [int]('{0:N2}' -f ($_.Used / 1Gb))
                $dDriveTotalSpace = [int]('{0:N2}' -f ($dDriveFreeSpace + $dDriveUsedSpace))

                $freePercent = '{0:N1}' -f ( ($dDriveFreeSpace / $dDriveTotalSpace) * 100)
                $usedPercent = '{0:N1}' -f ( ($dDriveUsedSpace / $dDriveTotalSpace) * 100)
                Write-Host 'Used % of' $_.Root "is $usedPercent%"
                Write-Host 'Free % of' $_.Root "is $freePercent%"
            }


            if($freePercent -le '30') {
                $Global:issue = 'Nearly Full!'
            }
            if ($freePercent -eq '0.0') {
                $Global:issue = 'WARNING - FULL CLEAR SPACE NOW!'
            }
            if($freePercent -gt '30') {
                $Global:issue = 'No Issues'
            }

            Write-Output "$env:COMPUTERNAME, Drive $name, $usedPercent% Full, $Global:issue"
            $Global:test3 = @()
            $Global:test3 += [PSCustomObject]@{
                ComputerName = "$env:COMPUTERNAME"
                Drive        = "$name" 
                UsedPercent  = "$usedPercent%"
                Comments     = "$Global:issue"
            }

            $test3 | Format-Table

        }
        Export-Excel -Path 'C:\temp\ProcessData.xlsx' -InputObject $results -AutoSize -AutoFilter -Append
        return $results
    }
}

Hey Everyone,

I'm having issues with exporting to Excel. I want to export $env:COMPUTERNAME, Drive $name, $usedPercent% Full, $Global:issue as headers with the data that is pulled from each invoke command. Unfortunately, I just get this in Excel:

However, when I use OutFile to text Out-File -Append Disk_Check.txt" I Get this:

This is how I want the data to appear in my Excel File.

score 2 · Accepted Answer · answered May 08 '23 at 23:08

Your code is much more complicated than it should be, you can simplify it as follows. Just be aware that Invoke-Command can run the remote invocations in parallel, 32 threads by default, which can be increased using its -ThrottleLimit parameter.

$vms = Import-Csv -Path .\vmsList.csv -Delimiter ','

# run this code in all VMs in parallel (32 threads by default)
Invoke-Command -ComputerName $vms.App_Central -Credential $Cred -ScriptBlock {
    $driveDetails = Get-PSDrive -Name D -ErrorAction SilentlyContinue

    if(-not $driveDetails) {
        Write-Host "No D drive found on $env:ComputerName"
        return
    }

    $totalSpace = $driveDetails.Free + $driveDetails.Used
    $freeSpace = $driveDetails.Free / $totalSpace
    $usedSpace = $driveDetails.Used / $totalSpace

    Write-Host ('Used % of {0} in {1} is {2:P0}' -f $driveDetails.Root, $env:ComputerName, $usedSpace)
    Write-Host ('Free % of {0} in {1} is {2:P0}' -f $driveDetails.Root, $env:ComputerName, $freeSpace)

    # lets assume it is gt than 0.3 (30%)
    $issue = 'No Issues'

    if($freeSpace -le 0.3) {
        $issue = 'Nearly Full!'
    }
    elseif($freeSpace -eq 0.0) {
        $issue = 'WARNING - FULL CLEAR SPACE NOW!'
    }

    [PSCustomObject]@{
        ComputerName = $env:ComputerName
        Drive        = $driveDetails.Root
        UsedPercent  = '{0:P0}' -f $usedSpace
        Comments     = $issue
    }
} | Export-Excel -Path 'C:\temp\ProcessData.xlsx' -AutoSize -AutoFilter

@Mayukh Bhattacharya thank you! Worked perfectly! – dcwilli5 May 09 '23 at 00:03 — dcwilli5, May 09 '23 at 00:03

Powershell Invoke Command Export to Excel

1 Answers1