This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.

You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

How to get the Alert triggered duration with SQL 2005

MathieuJM over 11 years ago

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

Top Replies

dragoon231 over 7 years ago in reply to MathieuJM +3

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…

0 MathieuJM over 8 years ago

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
Cancel
Vote Up 0 Vote Down

Cancel
0 dragoon231 over 7 years ago in reply to MathieuJM

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
Cancel
Vote Up 0 Vote Down

Cancel
0 dragoon231 over 7 years ago in reply to MathieuJM

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
Cancel
Vote Up +3 Vote Down

Cancel