3 Replies Latest reply on Jul 23, 2016 6:49 PM by dragoon231

    How to get the Alert triggered duration with SQL 2005

    MathieuJM

      Hi,

       

      We would like to have the duration of an alert.

      We have follow the thread http://thwack.solarwinds.com/thread/25685 unfortunately we have an error message:

      MACRO SQL ERROR - Type TIME is not a defined system type.

      It seems that Time is not defined in SQL 2005.

      How can we workaround this in order to have something functionnal ?

      We have the number of day from

      ${SQL:Select Floor(Convert(float,(Convert(datetime,'${Year}-${MM}-${DD} ${HH}:${Minute}:${Second} ${AM/PM}')-Convert(datetime,'${AlertTriggerTime}'))))} days

       

      but the second part

      ${SQL:Select Convert(time,Convert(datetime,'${Year}-${MM}-${DD} ${HH}:${Minute}:${Second} ${AM/PM}') - Convert(datetime,'${AlertTriggerTime}'))}

      give us the error: MACRO SQL ERROR - Type TIME is not a defined system type.

       

      Can anyone help on this ?

       

      Cheers

        • Re: How to get the Alert triggered duration with SQL 2005
          MathieuJM

          Hi,

           

          A few year later after upgrading to a recent version of NPM i have the following message:

          MACRO SQL ERROR - Conversion failed when converting date and/or time from character string

          Any advise to have the alert duration embedded in an alert email ?

          I have seen that the cvalue is available in the web interface but i can't find the variable to use it in an email.

           

          Cheers

            • Re: How to get the Alert triggered duration with SQL 2005
              dragoon231

              I have been trying to solve the same issue myself and no luck.  I've started going through the tables to see how it is stored.  If I find how it is stored, I will let you know.  I'm running SAM 6.2.3 and NPM 11.5.3.  It is weird it shows up on the webconsole, I will try to look through the HUBBLE tool and figure it out if possible.    :

               

               

               

               

              Rick

              • Re: How to get the Alert triggered duration with SQL 2005
                dragoon231

                Hope this helps MathieuJM, co-worker and I have been knocking around with this:

                 

                 

                Object was down for ${SQL:Select '${N=Alerting;M=Downtime}'/1440} Days ${SQL: Select '${N=Alerting;M=Downtime}'%1440/60} Hours ${SQL: Select '${N=Alerting;M=Downtime}'%1440%60;} Minutes

                 

                The first part "Object was down for" you can put that as whatever you want, "Alert active for:", etc.

                 

                On my end in the email alert it spits out:

                 

                Object was down for 0 Days 0 Hours 0 Minutes

                 

                This is for NPM 12, SAM 6.2.4 .

                 

                Rick

                2 of 2 people found this helpful