DiskSpace

Question

DiskSpace

mtz676

SSCrazy Eights

Points: 9255
More actions
April 7, 2017 at 3:36 am

#336791

When I run this below piece of code using task scheduler.I get an output where the last server mentioned repeats random number of times.(sometimes it repeats twice or thrice or 5 times or appears normally,once as it is supposed to.
When I schedule it as a task it does not run , but when I manually run it gives me an output as described above as an html email table but with the last server(in the text file) repeating randomly.
In task scheduler: --> Actions
Program script:powershell.exe
-ExecutionPolicy Bypass -Command "X:\SpaceDetails.ps1"
And if I run the below powershell code as part of a SQL job it throws the following error.
A job step received an error at line 150 in a PowerShell script. The corresponding line is ' Write-Host $item.DeviceID $item.VolumeName $item.FreeSpace $item.Size'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Cannot invoke this function because the current host does not implement it. '. Process Exit Code -1. The step failed.
How can I fix the above 3 scenarios.
1.make the task scheduler run as scheduled.
2.Upon manual run give a proper output without the last server repeating randomly.
3.Fix the issue as part of the SQL job and get the code to run as part of the SQL job.
Thanks
#create a text file, where we will later save the freedisk space info
$freeSpaceFileName = "E:\ABCD\Details.htm"
$serverlist = "E:\ABCD\Computers.txt"
$warning = 25
$critical = 10
New-Item -ItemType file $freeSpaceFileName -Force
# Getting the freespace info using WMI
#Get-WmiObject win32_logicaldisk | Where-Object {$_.drivetype -eq 3} | format-table DeviceID, VolumeName,status,Size,FreeSpace | Out-File FreeSpace.txt
# Function to write the HTML Header to the file
Function writeHtmlHeader
{
param($fileName)
$date = ( get-date ).ToString('dd/mm/yyyy')
Add-Content $fileName "<html>"
Add-Content $fileName "<head>"
Add-Content $fileName "<meta http-equiv='Content-Type' content='text/html; charset=iso-8859-1'>"
Add-Content $fileName '<title>All Servers DiskSpace Report</title>'
add-content $fileName '<STYLE TYPE="text/css">'
add-content $fileName ""
add-content $fileName "</style>"
Add-Content $fileName "</head>"
Add-Content $fileName "<body>"
add-content $fileName "<table width='100%'>"
add-content $fileName "<tr bgcolor='#CCCCCC'>"
add-content $fileName "<td colspan='7' height='25' align='center'>"
add-content $fileName "<font face='tahoma' color='#003399' size='4'><strong>All Servers DiskSpace Report - $date</strong></font>"
add-content $fileName "</td>"
add-content $fileName "</tr>"
add-content $fileName "</table>"
}
# Function to write the HTML Header to the file
Function writeTableHeader
{
param($fileName)
Add-Content $fileName "<tr bgcolor=#CCCCCC>"
Add-Content $fileName "<td width='10%' align='center'>Drive</td>"
Add-Content $fileName "<td width='50%' align='center'>Drive Label</td>"
Add-Content $fileName "<td width='10%' align='center'>Total Capacity(GB)</td>"
Add-Content $fileName "<td width='10%' align='center'>Used Capacity(GB)</td>"
Add-Content $fileName "<td width='10%' align='center'>Free Space(GB)</td>"
Add-Content $fileName "<td width='10%' align='center'>Freespace %</td>"
Add-Content $fileName "</tr>"
}
Function writeHtmlFooter
{
param($fileName)
Add-Content $fileName "</body>"
Add-Content $fileName "</html>"
}
Function writeDiskInfo
{
param($fileName,$devId,$volName,$frSpace,$totSpace)
$totSpace=[math]::Round(($totSpace/1073741824),2)
$frSpace=[Math]::Round(($frSpace/1073741824),2)
$usedSpace = $totSpace - $frspace
$usedSpace=[Math]::Round($usedSpace,2)
$freePercent = ($frspace/$totSpace)*100
$freePercent = [Math]::Round($freePercent,0)
if ($freePercent -gt $warning)
{
Add-Content $fileName "<tr>"
Add-Content $fileName "<td>$devid</td>"
Add-Content $fileName "<td>$volName</td>"
Add-Content $fileName "<td>$totSpace</td>"
Add-Content $fileName "<td>$usedSpace</td>"
Add-Content $fileName "<td>$frSpace</td>"
Add-Content $fileName "<td>$freePercent</td>"
Add-Content $fileName "</tr>"
}
elseif ($freePercent -le $critical)
{
Add-Content $fileName "<tr>"
Add-Content $fileName "<td>$devid</td>"
Add-Content $fileName "<td>$volName</td>"
Add-Content $fileName "<td>$totSpace</td>"
Add-Content $fileName "<td>$usedSpace</td>"
Add-Content $fileName "<td>$frSpace</td>"
Add-Content $fileName "<td bgcolor='#FF0000' align=center>$freePercent</td>"
#<td bgcolor='#FF0000' align=center>
Add-Content $fileName "</tr>"
}
else
{
Add-Content $fileName "<tr>"
Add-Content $fileName "<td>$devid</td>"
Add-Content $fileName "<td>$volName</td>"
Add-Content $fileName "<td>$totSpace</td>"
Add-Content $fileName "<td>$usedSpace</td>"
Add-Content $fileName "<td>$frSpace</td>"
Add-Content $fileName "<td bgcolor='#FBB917' align=center>$freePercent</td>"
# #FBB917
Add-Content $fileName "</tr>"
}
}
Function sendEmail
{ param($from,$to,$subject,$smtphost,$htmlFileName)
$from=New-Object System.Net.Mail.MailAddress "abcd@xyz.org"
$to= New-Object System.Net.Mail.MailAddress "mnop@xyz.org"
$subject="Servers Disk space report - $Date"
$smtphost="smtp.qwerty.org"
$body = Get-Content $htmlFileName
$smtp= New-Object System.Net.Mail.SmtpClient $smtphost
$msg = New-Object System.Net.Mail.MailMessage $from, $to, $subject, $body
$msg.isBodyhtml = $true
$smtp.send($msg)
}
writeHtmlHeader $freeSpaceFileName
foreach ($server in Get-Content $serverlist)
{
Add-Content $freeSpaceFileName "<table width='100%'><tbody>"
Add-Content $freeSpaceFileName "<tr bgcolor='#CCCCCC'>"
Add-Content $freeSpaceFileName "<td width='100%' align='center' colSpan=6><font face='tahoma' color='#003399' size='2'><strong> $server
</strong></font></td>"
Add-Content $freeSpaceFileName "</tr>"
writeTableHeader $freeSpaceFileName
$dp = Get-WmiObject win32_logicaldisk -ComputerName $server | Where-Object {$_.drivetype -eq 3}
foreach ($item in $dp)
{
Write-Host $item.DeviceID $item.VolumeName $item.FreeSpace $item.Size
writeDiskInfo $freeSpaceFileName $item.DeviceID $item.VolumeName $item.FreeSpace $item.Size
}
}
writeHtmlFooter $freeSpaceFileName
$date = ( get-date ).ToString('yyyy/MM/dd')
sendEmail mnop@xyz.org mnop@xyz.org "Disk Space Report - $Date" hub1 $freeSpaceFileName

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

mtz676 SSCrazy Eights Points: 9255 More actions · Answer 1

The code works.The issues was with the task scheduler.

How do I cc in the above code.Right now I can only send the mail to 1 recipient.I would like to
cc 2 more people.
Thanks

Sue_H SSC Guru Points: 90860 More actions · Answer 2

sqlnewbie17 - Saturday, April 8, 2017 10:40 AM
The code works.The issues was with the task scheduler.
How do I cc in the above code.Right now I can only send the mail to 1 recipient.I would like to
cc 2 more people.
Thanks

$msg.CC.Add("def@jkl.com")
$msg.CC.Add("mno@pqr.com")

Sue

Gary Varga SSC Guru Points: 82166 More actions · Answer 3

sqlnewbie17 - Saturday, April 8, 2017 10:40 AM
The code works.The issues was with the task scheduler.
How do I cc in the above code.Right now I can only send the mail to 1 recipient.I would like to
cc 2 more people.
Thanks

Perhaps you could mark yourself as the answer to help future readers?

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!

DiskSpace

Cookies on SQLServerCentral