Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 12

How to get the Alert triggered duration with SQL 2005


We would like to have the duration of an alert.

We have follow the thread 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 ?


Tags (3)
0 Kudos
3 Replies
Level 12


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.


0 Kudos

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 .


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



0 Kudos