17 Replies Latest reply on Sep 2, 2011 5:50 AM by syn3rgy

    Rounding Off

    syn3rgy

      I currently have an alert for HDD space, i would like to round this off to 2 decimal places:

      So currently i have:

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

       

       

      Thank you. 

        • Re: Rounding Off
          smargh

          [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.

          • Re: Rounding Off
            stuartwhyte

            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

              • Re: Rounding Off
                stuartwhyte

                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:  HTTP://xxxxxxxx/Orion/Netperfmon/nodedetails.aspx?netobject=N:${NodeID}&view=NodeDetails

                  • Re: Rounding Off
                    syn3rgy

                    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

                     

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

                    Alert: LDNCTXTS03-C:\ Label: 34f0b008 is MACRO SQL ERROR - Unclosed quotation mark after the character string '41.20775'.
                    Incorrect syntax near '41.20775'.',0) from Volumes WHERE VolumeID='@{171'} Percent Utilized

                    Volume Size MACRO SQL ERROR - Unclosed quotation mark after the character string '171'.
                    Incorrect syntax near '171'.' GB
                    Currently Used MACRO SQL ERROR - Unclosed quotation mark after the character string '171'.
                    Incorrect syntax near '171'.' GB
                    Currently Available MACRO SQL ERROR - Unclosed quotation mark after the character string '171'.
                    Incorrect syntax near '171'.' GB


                    Additional Information:

                    Server Name - LDNCTXTS03
                    IP Address - ***********
                    Server Location -

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

                    LDNCTXTS03 is Up

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

                    Dell Open Manage Page: *********
                    Orion: ********

                      • Re: Rounding Off
                        stuartwhyte

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

                         

                        Alert: Server-E:\ 5429c65f is 81 Percent Utilized

                        Volume Size 930.5 GB
                        Currently Used 751.4 GB
                        Currently Available 179.1 GB


                        Additional Information:

                        Server Name - Server
                        IP Address - xxxx
                        Server Location - xxxx

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

                        Server is Up

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

                        Dell Open Manage Page:xxxxxhttps://192.168.80.201:1311/
                        Orion: xxxx

                          • Re: Rounding Off
                            syn3rgy

                            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

                              • Re: Rounding Off
                                syn3rgy

                                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

                                • Re: Rounding Off
                                  Questionario

                                  you can either change your email type from HTML to plain text to resolve this or use the following string instead:

                                   

                                  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

                                   

                                  This behaviour should be fixed in the upcoming 10.2 release (according to SW support)

                                    • Re: Rounding Off
                                      stuartwhyte


                                      This behaviour should be fixed in the upcoming 10.2 release (according to SW support)

                                       



                                       

                                      Ah, the old "Coming in the next release" line...

                                      • Re: Rounding Off
                                        syn3rgy

                                        Hi guys, 

                                         

                                        Sorry it took me a while to respond. 

                                         

                                        Questionario - The bottom bit works great, however, I am still getting errors with the top bit.

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

                                        ------------------------------------------------------------------------------------------------------------------
                                        Alert: LDNFS02-C:\ Label: 68363d3f is MACRO SQL ERROR - Unclosed quotation mark after the character string '99.60268'.
                                        Incorrect syntax near '99.60268'.',0) from Volumes WHERE VolumeID='@VolumeID@' Percent Utilized

                                        Volume Size - 48.8 GB
                                        Currently Used - 48.6 GB
                                        Currently Available - 0.2 GB
                                        ------------------------------------------------

                                         

                                        Tried changing between Text and HTML as well.

                                         

                                        Thank you