powershellcsvserverdiskspace

How to add empty rows in output csv?


I am using the following script to generate a report for disk space utilization, But the output csv file does not have any spaces/blank rows between different servers, So how do I add the spaces/blank rows to increase readability ??

$LogDate = get-date -f yyyyMMddhhmm
$File = Get-Content -Path C:\StorageReport\Servers.txt

$DiskReport = ForEach ($Servernames in ($File)) 

{Get-WmiObject win32_logicaldisk <#-Credential $RunAccount#> `
-ComputerName $Servernames -Filter "Drivetype=3" `
-ErrorAction SilentlyContinue 
} 

$DiskReport | 

Select-Object @{Label = "Server Name";Expression = {$_.SystemName}},
@{Label = "Drive Letter";Expression = {$_.DeviceID}},
@{Label = "Total Capacity (GB)";Expression = {"{0:N1}" -f( $_.Size / 1gb)}},
@{Label = "Free Space (GB)";Expression = {"{0:N1}" -f( $_.Freespace / 1gb ) }},
@{Label = 'Free Space (%)'; Expression = {"{0:P0}" -f ($_.freespace/$_.size)}} |

Export-Csv -path "C:\StorageReport\DiskReport_$logDate.csv" -NoTypeInformation

Add-PSSnapin Microsoft.Exchange.Management.PowerShell.SnapIn; 

$messageParameters = @{                        
                Subject = "Weekly Server Storage Report"                        
                Body = "Attached is Weekly Server Storage Report.All reports are located in C:\StorageReport\, but the                

          most recent  is sent weekly"                   
                From = "Email name1 <Email.name1@domainname.com>"                        
                To = "Email name1 <Email.name1@domainname.com>"
                CC = "Email name2 <Email.name2@domainname.com>"
                Attachments = (Get-ChildItem C:\StorageReport\*.* | sort LastWriteTime | select -last 1)                   
                SmtpServer = "SMTPServerName.com"                        
            }   
Send-MailMessage @messageParameters -BodyAsHtml

Solution

  • Although my version of Excel (2016) accepts (and shows) blank lines in the input csv, I can not guarantee this will also be the case in other versions, so I think it is best to include a line with just commas in the csv, effectively adding a row with all fields empty.

    To do that, you could output the Csv file inside the loop where you iterate over the different servers with -Append switch added.

    $LogDate = Get-Date -Format 'yyyyMMddHHmm'
    $File    = Get-Content -Path C:\StorageReport\Servers.txt
    $OutFile = Join-Path -Path 'C:\StorageReport' -ChildPath "DiskReport_$LogDate.csv"
    
    # because we now are Appending to the csv, we must make sure we start off with a new file
    if (Test-Path -Path $OutFile -PathType Leaf) { 
        Remove-Item -Path $OutFile -Force
    }
    
    foreach ($Server in $File) {
        Get-WmiObject Win32_LogicalDisk -ComputerName $Server -Credential $RunAccount -Filter "Drivetype=3" -ErrorAction SilentlyContinue |
        Select-Object @{Label = 'Server Name';Expression = {$_.SystemName}},
            @{Label = 'Drive Letter';Expression = {$_.DeviceID}},
            @{Label = 'Total Capacity (GB)';Expression = {'{0:N1}' -f ( $_.Size / 1gb)}},
            @{Label = 'Free Space (GB)';Expression = {'{0:N1}' -f ( $_.Freespace / 1gb ) }},
            @{Label = 'Free Space (%)'; Expression = {'{0:P0}' -f ($_.freespace/$_.size)}} |
        Export-Csv -Path $OutFile -NoTypeInformation -Append
    
        # add a line with just commas (empty fields) below this server info to the file
        Add-Content -Path $OutFile -Value (',' * 4)
    } 
    

    Next, go ahead and send the email. My remark on that would be to simply do

    Attachments = $OutFile
    

    EDIT

    Looking at the error you show in your comments, I suspect the input file where you read the server names either starts with a blank line, or has whitespace surrounding the server name, which causes the Get-WmiObject command to fail.

    When this returns $null, there wil be no properties to write to the CSV file and because of -ErrorAction SilentlyContinue there is nothing to stop the script writing zilch to the file anyway.

    Below code does extensive error-checking on this, both in reading the file and stripping empty lines and whitespaces, testing beforehand if the server is on-line or not and it now uses a try{..} catch{..} block.

    $LogDate = Get-Date -Format 'yyyyMMddHHmm'
    # make sure you skip empty or whitespaace only lines and trime the values
    $File    = (Get-Content -Path 'C:\StorageReport\Servers.txt' | Where-Object { $_ -match '\S' }).Trim()
    $OutFile = Join-Path -Path 'C:\StorageReport' -ChildPath "DiskReport_$LogDate.csv"
    
    # because we now are Appending to the csv, we must make sure we start off with a new file
    if (Test-Path -Path $OutFile -PathType Leaf) { 
        Remove-Item -Path $OutFile -Force
    }
    
    foreach ($Server in $File) {
        if (!(Test-Connection -ComputerName $Server -Count 1 -Quiet)) {
            Write-Warning "Could not connect to server '$Server'"
        }
        else {
            try {
                Get-WmiObject Win32_LogicalDisk -ComputerName $Server -Credential $RunAccount -Filter "Drivetype=3" -ErrorAction Stop |
                Select-Object @{Label = 'Server Name';Expression = {$_.SystemName}},
                    @{Label = 'Drive Letter';Expression = {$_.DeviceID}},
                    @{Label = 'Total Capacity (GB)';Expression = {'{0:N1}' -f ( $_.Size / 1gb)}},
                    @{Label = 'Free Space (GB)';Expression = {'{0:N1}' -f ( $_.Freespace / 1gb ) }},
                    @{Label = 'Free Space (%)'; Expression = {'{0:P0}' -f ($_.freespace/$_.size)}} |
                Export-Csv -Path $OutFile -NoTypeInformation -Append
    
                # add a line with just commas (empty fields) below this server info to the file
                Add-Content -Path $OutFile -Value (',' * 4)
            }
            catch {
                Write-Warning "Error getting drive information for server '$Server'`r`n$($_.Exception.Message)"
            }
        }
    }