Rounding Off

[edit: I just realised that this only does a drive letter substring, not available disk space. I've always found this to be perfectly fine for my uses though - it always looks aesthetically okay.]

I use this as the subject of disk space alert emails:

*** WARNING: ${NodeName} ${SQL:Select Substring ('${Caption}',1,2)  AS Caption} Free Space ${VolumeSpaceAvailable} [min=${Minimum_Free_MB}MB]

I have Minimum_Free_MB as a custom property to specify a mimimum amount of free space for specific volumes, and have two alerts - one is a custom SQL alert to do the bytes-to-MB comparison when the Minimum_Free_MB field is not empty. The other alert just checks that the Minimum_Free_MB field is blank and triggers if there's less than 5GB free.

Thank you for your reply.

Im new to NPN and SQL so finding it rather tough, Is there no way to just add something like a =round somewhere to round it off?

also 

           '${Caption}',1,2)

What does the ,1,2 signify ?

Thank you

try searching google or something for
mssql round transact-sql

and select the one from msdn.microsoft.com

Try this:

***WARNING*** Volume ${SQL:Select Substring('${Caption}',1,3) AS Caption} on ${NodeName} is almost full and has ${SQL:Select round((VolumeSpaceAvailable / 1024 / 1024 / 1024),1) from Volumes WHERE VolumeID='${VolumeID}'} GB

HTH

Stuart

my bad, you actually want decimal.  check here:
http://msdn.microsoft.com/en-us/library/ms187746.aspx

decimal[p,s] - the s is what you need to = 2

As a bonus extra, heres what I use for the alert mail:

Alert: ${FullName} is ${SQL:Select Round ('${VolumePercentUsed}',0) from Volumes WHERE VolumeID='${VolumeID}'} Percent Utilized

Volume Size        ${SQL:Select round((VolumeSize / 1024 / 1024 / 1024),1) from Volumes WHERE VolumeID='${VolumeID}'} GB
Currently Used     ${SQL:Select round((VolumeSpaceUsed / 1024 / 1024 / 1024),1) from Volumes WHERE VolumeID='${VolumeID}'} GB
Currently Available     ${SQL:Select round((VolumeSpaceAvailable / 1024 / 1024 / 1024),1) from Volumes WHERE VolumeID='${VolumeID}'} GB


Additional Information:

Server Name - ${Node.SysName}
IP Address - ${Node.IP_Address}
Server Location - ${Node.Location}

~~~~~~~~~~~~~~~~~~~~~~~~
Current Status:

${Node.SysName} is ${Node.Status}

~~~~~~~~~~~~~~~~~~~~~~~~

Dell Open Manage Page:  HTTPS://${Node.IP_Address}:1311
Orion:  xxxxxxxx/.../nodedetails.aspx

Thank you all for your help.

Stuart, your script works perfectly, thank you.

Thank you as well for providing the additional infomation however, when i get the alert i get the following

-----------------------------------------------------------

Strange, that was copy/pasted straight from my alert.  You Should get this:

Weird,

This line works when in the subject but not in the body

Alert: ${FullName} is ${SQL:Select Round ('${VolumePercentUsed}',0) from Volumes WHERE VolumeID='${VolumeID}'} Percent Utilized

is that code just pasted in the body of your email alerts? do you have to add anything so that when it emails the alert it recognises the SQL queries?

Thank you