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
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)"
}
}
}