Disk space report showing wrong results

Question

Disk space report showing wrong results

vsamantha35

SSChampion

Points: 11529
More actions
July 2, 2017 at 12:23 am

#331661

Hi Experts,
Need some powershell help.
Issue Descrition: I work as SQL Server DBA and we monitor servers using powershell scripts. So, we have one such script to monitor disk free space of multiple servers.
We are running powershell 4.0 and using below powershell script which takes Servers_test.txt file as input file. i.e. We put the list of servers and read the content inside the powershell script to loop thru each server , get all disk/drive space details and if the freespacepercent< 10% we show the drive entry in RED color indicating it is CRITICAL and we are running of space and if freespacepercent< 15% show it in orange color indicating a WARNING!. So, we display all suc drives which are below thresholds for each server.
Issue here is , If I try to run the script for powershellise outside sql server i.e from DOS / command line . The report is shown as expected.
But if we run the same script from SQL Agent Job, it is skipping details of one server. I dont know why ? I tried multiple things but no use. I even tried to generate the HTML which is getting formed and try to save it into a .html file and see the content of it. If so, it shows correct report. But when we invoke the powershell script via sql agent job then it is showing wrong results. Attaching screenshots and scripts.
I have spent a lot of time tweaking many things but was unable to figure out the issue. Can someone help me here !!
-- Variable to be changed before running the script
$smtpServer #-- smtp server
$msg.From   #-- From user
$users    #-- To user
PS E:\DiskSpace> $PSVersionTable
Name         Value
----         -----
PSVersion        5.1.14393.1358
PSEdition        Desktop
PSCompatibleVersions    {1.0, 2.0, 3.0, 4.0...}
BuildVersion       10.0.14393.1358
CLRVersion       4.0.30319.42000
WSManStackVersion     3.0
PSRemotingProtocolVersion  2.3
SerializationVersion    1.1.0.1
SQL Server Details :
===============
SQL server 2016 Enterprise Edition SPSql agent Job as only 1 step and choosen Powershell subsystem and then invoking powershell script as belowpowershell.exe E:\diskspace\DiskspaceV2.ps1
Script source code (E:\diskspace\DiskspaceV2.ps1)
======================
# Continue even if there are errors-
$ErrorActionPreference = "Continue";
# Set your warning and critical thresholds
$percentWarning = 15;
$percentCritcal = 10;
# EMAIL PROPERTIES i.e Tolist
# Set the recipients Emails
   $users = "testguy@cnz.com" #---- change this later
# REPORT PROPERTIES
# Path to the report
$reportPath = "E:\DiskSpace\";
# Report name
   #$reportName = "DiskSpaceRpt_$(get-date -format ddMMyyyy).html";
$reportName = "DiskSpaceRpt_$(get-date -format ddMMyyyyHHmmss).html";
# Path and Report name together
$diskReport = $reportPath + $reportName
#Set colors for table cell backgrounds
$redColor = "#FF0000"
$orangeColor = "#FBB917"
$whiteColor = "#FFFFFF"
# Count if any computers have low disk space. Do not send report if less than 1.
$i = 0;
$computers = Get-Content "E:\DiskSpace\servers_test.txt"; #--testing
$datetime = Get-Date -Format "MM-dd-yyyy_HHmmss";
Write-host $diskReport
# Create and write HTML Header of report
$titleDate = get-date -uformat "%m-%d-%Y - %A"
$header = "
   <html>
   <head>
   <meta http-equiv='Content-Type' content='text/html; charset=iso-8859-1'>
   <title>DiskSpace Report</title>
   <STYLE TYPE='text/css'>
   
   </style>
   </head>
   <body>
   <table width='70%'>
   <tr bgcolor='#CCCCCC'>
   <td colspan='7' height='25' align='center'>
   <font face='tahoma' color='#003399' size='4'><strong>Disk Usage Report for $titledate</strong></font>
   </td>
   </tr>
   </table>
"
Add-Content $diskReport $header
# Create and write Table header for report
$tableHeader = "
<table width='70%'><tbody>
<tr bgcolor=#CCCCCC>
<td width='10%' align='center'>Server</td>
<td width='5%' align='center'>Drive</td>
<td width='5%' align='center'>Drive Label</td>
<td width='10%' align='center'>Total Capacity(GB)</td>
<td width='10%' align='center'>Used Capacity(GB)</td>
<td width='10%' align='center'>Free Space(GB)</td>
<td width='15%' align='center'>Freespace %</td>
</tr>
"
Add-Content $diskReport $tableHeader
# Start processing disk space reports against a list of servers
foreach($computer in $computers)
{
$disks = Get-WmiObject -ComputerName $computer -Class Win32_LogicalDisk -Filter "DriveType = 3"
$computer = $computer.toupper()
# Write-host "Computer : "$computer
$dataRow="";
   foreach($disk in $disks)
{
   $deviceID = $disk.DeviceID;
   $volName = $disk.VolumeName;
   [float]$size = $disk.Size;
   [float]$freespace = $disk.FreeSpace;
   $percentFree = [Math]::Round(($freespace / $size) * 100, 2);
   $sizeGB = [Math]::Round($size / 1073741824, 2);
   $freeSpaceGB = [Math]::Round($freespace / 1073741824, 2);
   $usedSpaceGB = $sizeGB - $freeSpaceGB;
   $color = $whiteColor;

    Write-host "Computer : "$computer
    Write-host "Deviceid : "$deviceID
    Write-host "Volumne name : "$volName
    Write-host "Size   : "$size
    Write-host "FreeSpace : "$freespace
    Write-host "PercentFree : "$percentFree
    Write-host "SizeGB   : "$sizeGB
    Write-host "UsedGb   : "$usedSpaceGB
    Write-host "FreespaceGB : "$freeSpaceGB
    Write-host " "
# Set background color to Orange if just a warning
if($percentFree -lt $percentWarning)
   {
    $color = $orangeColor
# Set background color to Orange if space is Critical
  if($percentFree -lt $percentCritcal)
   {
   $color = $redColor
   }
# Create table data rows
$dataRow = "
   <tr>
   <td width='10%'>$computer</td>
   <td width='5%' align='center'>$deviceID</td>
    <td width='5%' >$deviceID</td>
   <td width='10%' align='center'>$sizeGB</td>
   <td width='10%' align='center'>$usedSpaceGB</td>
   <td width='10%' align='center'>$freeSpaceGB</td>
   <td width='15%' bgcolor=`'$color`' align='center'>$percentFree</td>
   </tr>
"
Add-Content $diskReport $dataRow;
#Write-Host -ForegroundColor DarkYellow "$computer $deviceID percentage free space = $percentFree";
$i++
   }
}
}
# Create table at end of report showing legend of colors for the critical and warning
$tableDescription = "
</table><br><table width='20%'>
<tr bgcolor='White'>
<td width='10%' align='center' bgcolor='#FBB917'>Warning less than 15% free space</td>
<td width='10%' align='center' bgcolor='#FF0000'>Critical less than 10% free space</td>
</tr>
"
Add-Content $diskReport $tableDescription
Add-Content $diskReport "</body></html>"
# Send Notification if alert $i is greater then 0
if ($i -gt 0)
{
#Write-host "*** 1 ***"
foreach ($user in $users)
{
#Write-host "*** 2 ***"
   Write-Host "Sending Email notification to $user"

   $smtpServer = "smtphost.xxxxx.xxx.xxxx.com" #---- change this later
   $smtp = New-Object Net.Mail.SmtpClient($smtpServer)
   $msg = New-Object Net.Mail.MailMessage
   $msg.To.Add($user)
   $msg.From = "abc@hall.com"   #---- change this later
   $msg.Subject = "Disk Usage Report for $titledate"
   $msg.IsBodyHTML = $true
   $msg.Body = get-content $diskReport
   Invoke-Expression $diskReport
   Write-Host $msg.Body
   $smtp.UseDefaultCredentials = $true
   $smtp.Send($msg)
   #$body = ""
}
}
Screenshots below
SQL Agent Job step:
Thanks,
Sam

Viewing 6 posts - 1 through 5 (of 5 total)

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

vsamantha35 SSChampion Points: 11529 More actions · Answer 1

Not able to figure out if it is a security issue or it is picking from some cache. I know its pretty hard to repro such issue but if any pointers on troubleshooting such issue?

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 2

Fire up an SQL Agent job that does a DIR through xp_CmdShell on the C: drive of a machine that is reporting correctly and a machine that is not. Be sure to capture the output that xp_CmdShell will provide. I'm betting that your SQL Agent doesn't have privs on the box with the problem but you do and that's why it works from the Command Line for you.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Jones - SSC Editor SSC Guru Points: 734449 More actions · Answer 3

If it's one machine/drive, it's likely security. Try as Jeff suggested.

vsamantha35 SSChampion Points: 11529 More actions · Answer 4

Thanks Jeff and Steve. Bang on! IT was security issue.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 5

vsamantha35 - Monday, July 3, 2017 12:12 PM
Thanks Jeff and Steve. Bang on! IT was security issue.

Thank you for the feedback. Glad to have helped.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)