1

I am trying to set an alert if the REMAINING AVAILABLE space of my SQL mdf data FILE is less than 2000 MB. See below:

$SQLInstances = "A","B","C"

$SQLQuery = " USE DB
             SELECT CONVERT(DECIMAL(10, 2), CAST(CAST(10240 - size/128.0 AS NVARCHAR(50)) + size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS NVARCHAR(50))) AS ActualSpaceAvailableMB
             FROM   sys.database_files
             WHERE  name = 'DB'"

$login = "XYZ"
$password = "123" | Convertto-SecureString -AsPlainText -Force
$creds = New-Object System.Management.Automation.Pscredential -Argumentlist $login,$password
$SMTPServer = "10.0.0.0.0.0" # This is your SMTP Server 
$to = "ME@ME.COM"# This is the recipient smtp address 1
$from = "NOREPLY@YOU.COM" # This will be the sender´s address 

foreach ($SQLInstance in $SQLInstances) {
    $output = Invoke-Sqlcmd -ServerInstance $SQLInstance -Database DB -Query  $SQLQuery |
              Select-Object -ExpandProperty ActualSpaceAvailableMB #-QueryTimeout 1200 -Verbose -ErrorAction Stop 4>&1 

    if ($output -lt 2000 ) {
        Send-MailMessage -SmtpServer $SMTPServer -Credential $creds -To $to -From $from -Subject " Alert at  $SQLInstance" -Body "free space is $output MB. Please investigate!!"
    } else {
        Write-Output "Actual size is not less than 2 GB"
    }
}

Now the problem sometimes the alert gives incorrect results i.e. even if the value in the $output variable is greater than 2000 MB an email is still being send. I want it to send an email ONLY if the value in the $output is less than 2000. Any ideas?

Ansgar Wiechers
  • 193,178
  • 25
  • 254
  • 328
Immortal
  • 1,133
  • 1
  • 15
  • 35

2 Answers2

6

Comparisons in PowerShell have a tendency to yield unexpected results, because PowerShell tries to adjust the type of the second operand to match the type of the first operand. Thus if your first operand is a string the comparison will be a string comparison, not a numeric comparison.

Demonstration:

PS C:\> $s = '20'
PS C:\> $s -gt 10    # ⇔ "20" -gt "10"
True
PS C:\> $s -gt 100   # ⇔ "20" -gt "100"
True
PS C:\> $s -gt 3     # ⇔ "20" -gt "3"
False

To mitigate this cast the first operand to a numeric type:

PS C:\> [int]$s -gt 100
False
PS C:\> [int]$s -gt 3
True
Ansgar Wiechers
  • 193,178
  • 25
  • 254
  • 328
  • 1
    @Immortal "it's not working" is not a valid problem description. How *exactly* is it "not working"? Please [edit] your question and show sample input (i.e. sample values of the variable `$output`). – Ansgar Wiechers Sep 16 '19 at 13:10
  • so am running a windows scheduler task that call that powershelll script. This is the email am getting: free space is MB. Please investigate!! – Immortal Sep 16 '19 at 13:19
  • @Immortal - `$output` is most likely `$null`. You should write the sql results to a file and investigate. And please, re-read Ansgar's previous comment. "it's not working" statements are a good way to not get any help at all. – Lieven Keersmaekers Sep 16 '19 at 13:59
  • @AnsgarWiechers Thanks for the heads-up, so I have removed the comment. What I mean is that, am still getting an email message like "free space is MB. Please investigate!!, Based on my pasted code above, I shouldn't be receiving any email if output is less than 2000 mb. Isnt it? Also note, the email message doesn't have the value of the output which is not supposed to be. Any ideas? – Immortal Sep 17 '19 at 09:34
0

You can do it the other way so $output gets cast to integer, instead of 2000 getting cast to string.

if ($output -and 2000 -ge $output) {

EDIT: added a null check

For example, this turns out to be true when they're both strings, which is not what you want.

 '90' -gt 100
js2010
  • 23,033
  • 6
  • 64
  • 66