asked on

Disk Size Report and Email in GB and TB

Hi, i have code that read 12 SQL Servers, every server has a C drive and a D Drive. The C drive is under GB and the D drive is under TB. And I am focusing on the D Drive. The script creates a report with the following headers: "Servername","Drive","Total size (TB)","Free Space (TB)","Free Space (%)","Name ".
But the metrics for the C drive do not make sense, because i do the calculation for TB, the D drive.

Is there any to show GB and TB respectively for each Drive ?
Or, can I remove the C drive from the report ?
Lastly, can i add the contents of the report in the body of an email and send the report as an attachment ?

Code:

$computers = Import-CSV "C:\Scripts\HardDriveSpace\PROD-SQLBAK-Servers.csv"
foreach ($Computer in $Computers){  
	$Server =  $Computer.Name
	$ServerOS = Get-ADComputer $Server -Properties * | Select OperatingSystem
	Write-Host "Hostname: $Server OS:"($ServerOS).OperatingSystem""
	If ((Test-Connection -ComputerName $Server -Quiet) -and (($ServerOS).OperatingSystem -notin "unknown", "Ubuntu", "")) {
        try{
		    $Disks = Get-wmiobject -Class Win32_LogicalDisk -computername $Server -ErrorAction SilentlyContinue -filter "DriveType= 3"
		}
		Catch{
            $ErrorMessage = $_.Exception.Message
			$FailedItem = $_.Exception.ItemName
			Write-Warning "Failed to get Drives information from $Server"
			Write-Host "Sleeping"
			Start-Sleep 5
		}
	    Try {
		    $Servername = (Get-wmiobject CIM_ComputerSystem -ComputerName $Server -ErrorAction SilentlyContinue).Name 
	    }
	    Catch{
		    $ErrorMessage = $_.Exception.Message
		    Write-Host "ERROR: $ErrorMessage"
		    Write-host $Server -ForegroundColor Red
		    Write-host $Servername -ForegroundColor Red
		    $Server
		    $Servername
	    }       
	    foreach ($objdisk in $Disks)  {  
		    $out = New-Object PSObject 
		    $total = "{0:N0}" -f ($objDisk.Size/1TB)
		    $free = ($objDisk.FreeSpace/1TB)
		    $freePercent = "{0:P0}" -f ([double]$objDisk.FreeSpace/[double]$objDisk.Size)  
		    $out | Add-Member -MemberType NoteProperty -Name "Servername" -Value $Servername 
		    $out | Add-Member -MemberType NoteProperty -Name "Drive" -Value $objDisk.DeviceID  
		    $out | Add-Member -MemberType NoteProperty -Name "Total size (TB)" -Value $total 
		    $out | Add-Member -MemberType NoteProperty -Name "Free Space (TB)" -Value $free 
		    $out | Add-Member -MemberType NoteProperty -Name "Free Space (%)" -Value $freePercent
		    $out | Add-Member -MemberType NoteProperty -Name "Name " -Value $objdisk.volumename 
		    $out | export-csv "C:\Scripts\HardDriveSpace\SQLBAKServers-DiskSpace_Report.csv" -NoTypeInformation -Append
	    }
    }
}

Open in new window

See atachment for the report
SQLBAKServers-DiskSpace_Report.csv

Thanks for your help

David Johnson, CD

Change
$total = "{0:N0}" -f ($objDisk.Size/1TB)
to
$total = "{0:N2}" -f ($objDisk.Size/1TB)

ASKER CERTIFIED SOLUTION

oBdA

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

namerg

ASKER

Thank you all, will test.

namerg

ASKER

Thank You Sir...

namerg

ASKER

Shut, not sure what i did now i am getting just numbers in the report :(

Import-Module ActiveDirectory
$User = "Domain\Username"
$PWord = ConvertTo-SecureString -String "P@$$w0rd" -AsPlainText -Force
$Credential = New-Object -TypeName "System.Management.Automation.PSCredential" -ArgumentList $User, $PWord
New-PSDrive -Name PROD -PSProvider ActiveDirectory -Server "DC01.domain.lcl" -Credential (Get-Credential $Credential) -Root "//RootDSE/" -Scope Global
Set-Location -Path 'PROD:'

$Computers = Import-CSV "C:\Scripts\SQLBackupsMounts\PROD-SQLBAK-Servers.csv"
$outFile = 'C:\Scripts\SQLBackupsMounts\SQLBackupsMountsDiskSpace-Report.csv'
$sendMailArgs = @{
	From = 'SQLBackupsMounts@covius.lcl'
	To = 'me@domain.local'
	Subject = 'SQLBackups Mounts Disk Space Report'
	Body = 'See attachment'
	SmtpServer = 'smtpserver'
	Attachments = $outFile
}

Function Get-DiskSizeString([Uint64]$Bytes) {
	If ($Bytes -ge 1TB) {
		$size = $Bytes / 1TB
		$unit = 'TB'
	} Else {
		$size = $Bytes / 1GB
		$unit = 'GB'
	}
	Return "$($size.ToString('N2')) $($unit)"
}

$Computers | ForEach-Object {
	$server = $_.Name
	Try {
		Write-Host "Processing: $($server) ..." -NoNewline
		$serverOS = (Get-ADComputer -Identity $server -Properties OperatingSystem).OperatingSystem
		Write-Host " OS: $($serverOS)"
		If ($serverOS -notin 'unknown', 'Ubuntu', '') {
			If (-not (Test-Connection -ComputerName $server -Quiet)) {
				Throw 'Not responding to Ping'
			}
			$serverName = (Get-WmiObject -Class CIM_ComputerSystem -ComputerName $server -ErrorAction Stop).Name 
			Get-WmiObject -Class Win32_LogicalDisk -Computername $server -ErrorAction Stop -Filter "DriveType=3" | ForEach-Object {
				[PSCustomObject]([ordered]@{
					ServerName = $serverName
					Drive = $_.DeviceID
					'Total size' = Get-DiskSizeString -Bytes $_.Size
					'Free space' = Get-DiskSizeString -Bytes $_.FreeSpace
					'Free space (%)' = '{0:P0}' -f ([double]$_.FreeSpace / [double]$_.Size)
					Name = $_.VolumeName
					# Error = $null
				})
			}
		}
	} Catch {ping 
		[PSCustomObject]([ordered]@{
			ServerName = $server
			Drive = $null
			'Total size' = $null
			'Free space' = $null
			'Free space (%)' = $null
			Name = $null
			Error = $_.Exception.Message
		})
	}
} | Export-Csv -Path $outFile -NoTypeInformation

Send-MailMessage @sendMailArgs
Start-Sleep 5
Set-Location C:
#Remove-PSDrive -Name PROD
Get-PSDrive PROD | Remove-PSDrive
#Remove-Item "C:\Scripts\SQLBackupsMounts\SQLBackupsMountsDiskSpace-Report.csv" -Force

Open in new window

SQLBackupsMountsDiskSpace-Report.csv

oBdA

Yo have a "ping" in line 53 that shouldn't be there, and you should uncomment line 49 again; the properties returned must be the same both in OK and Error case. Otherwise, if the first computer doesn't fail, but one later in the list, you'll not see what went wrong.
Paste this into a PS console to see what happens:

[pscustomobject]@{a=1}, [pscustomobject]@{a=2;b=9}, [pscustomobject]@{a=3;b=9} | ConvertTo-Csv

Open in new window

namerg

ASKER

Got this:

PS C:\> [pscustomobject]@{a=1}, [pscustomobject]@{a=2;b=9}, [pscustomobject]@{a=3;b=9} | ConvertTo-Csv
#TYPE System.Management.Automation.PSCustomObject
"a"
"1"
"2"
"3"

Open in new window

oBdA

Exactly. Notice how the 9 from the second and third object is missing? And that's what will happen to the Error property in all following rows if the first object written doesn't have it.
Compare with that; only difference is that the first now has a "b" property like the rest:

[pscustomobject]@{a=1; b=$null}, [pscustomobject]@{a=2;b=9}, [pscustomobject]@{a=3;b=9} | ConvertTo-Csv

Open in new window

namerg

ASKER

Ohh i see. All is good now. Thank youuuu