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

How to get the Alert triggered duration

Jump to solution

Has anyone able to achive this

I am trying to get the duration in mins in advance alerts for which the alert has been generated

Something like

Alert RESET time - Alert Trigger time.

I can figure out something link ${DateTime - AlertTriggerTime}

now how to get this working.

0 Kudos
1 Solution
Level 14

Hi,

I opened a ticket for this not working with HTML (Ticket#265369) and I have been given a workaround.

 

So, in order to use this with plaintext emails in the alert reset action use this:

---

This alert was active for ${SQL:Select Floor(Convert(float,(Convert(datetime,'${Year}-${MM}-${DD} ${HH}:${Minute}:${Second} ${AM/PM}')-Convert(datetime,'${AlertTriggerTime}'))))} days and ${SQL:Select Convert(time,Convert(datetime,'${Year}-${MM}-${DD} ${HH}:${Minute}:${Second} ${AM/PM}') - Convert(datetime,'${AlertTriggerTime}'))}

---

For HTML emails use this:

---

This alert was active for ${SQL:Select Floor(Convert(float,(Convert(datetime,'$@Year@-$@MM@-$@DD@ $@HH@:$@Minute@:$@Second@ $@AM/PM@')-Convert(datetime,'$@AlertTriggerTime@'))))} days and ${SQL:Select Convert(time,Convert(datetime,'$@Year@-$@MM@-$@DD@ $@HH@:$@Minute@:$@Second@ $@AM/PM@') - Convert(datetime,'$@AlertTriggerTime@'))}

---

This works great for me, the result looks something like this: "This alert was active for 1 days and 03:46:10"

View solution in original post

0 Kudos
35 Replies
Level 7

We have made a new SQL script to find the alert duration to give us the time in {days hours minutes} form and all testing have worked. We are using NPM 11.5.3 and SAM 6.2.3

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 

I hope this helps others with the alert duration problem

0 Kudos
Level 7

I would like to accomplish this also. When an alert is reset, I would like to send how long the alert was active in the reset action. 

Example

Trigger when the sky is blue.

Reset when the sky is no longer buy.

Reset action sends an email saying how long the sky was blue.

0 Kudos
Level 19

That's pretty straightforward.  I've captured that feature request.

0 Kudos
Level 8

Hi,

Is there some advance with this kind of Alert Trigger variable that shows the reset time or something like that, that shows the total amount of time for an outage on a reset alert?

Thanks 

0 Kudos
Level 9

I would also like to know if this was implemented since 2009.  Currently I have Alert Trigger Time: ${AlertTriggerTime} & ${ShortTime} to show when the alert was triggered & reset.  However, If I have a delay on the Reset Timer I believe that the actual time the alarm was reset would be displayed incorrectly by ${ShortTime}. What would be displayed is the time the reset action is executed.

For example, if a node goes down @ 1:00pm a trigger action (e.g. an e-mail) would be sent with alert time 1:00pm.

Let's say it comes back up @ 1:10 pm but I have a 5 min delay on the Reset timer, I won't get an e-mail @ 1:10 i will get it @ 1:15 after the reset timer expires. Therefore the e-mail would be sent out with the time the e-mail was sent & not the time the reset condition was met.

It would be good if there was a ${AertReset} variable. 

0 Kudos
Level 15

If you are just looking for the math to be done, you could try ${SQL:Select ${AlertTriggerTime} - ${ShortTime}}

 

Edit: hmmm thats not working... I think the variable types are wrong... need to do a cast or something... let me look at this, unless anybody else can quickly fix my mistake here.

 

Ok, does this work: ${SQL:Select Convert(time,Convert(datetime,'${Time}') - Convert(datetime,'${AlertTriggerTime}'))}

0 Kudos
Level 14

Hi netlogix,

 

for some reason the query now produces sql errors:

MACRO SQL ERROR - The conversion of a varchar data type to a datetime data type resulted in an out-of-range value

 

any idea on how to fix this?

0 Kudos
Level 15

hmmm...  I don't know.  The only thing I can think of right now is that is might have to do with some sort of roll-over happening (was it more than 24 hours?).  I'll start giving this some thought, in the mean time, can you see if there was anything out of the norm with that specific occurrence?  Or, is it happening all the time now?

(The reason I am thinking roll over is because I didn't test that and I am not sure how I can... yet)

0 Kudos
Level 14

it does not seem to be a rollover problem...

we got this error for an alert that lasted 12minutes but not for an alert that rolled over...

however an alert that lasted for more than 6weeks shows only the hours (6h42mins)

 

whether it works or doesn't seems to be totally random for me

0 Kudos
Level 15

hmm... ok, thats what it will do for a day roll over, thats something else to figure out how to fix.

It is still working fine for me, it could be odd or unexpected format of the string in Time or Alert Trigger Time, or the result of the math
For now, can you add this to an alert that is having the issue?
Time: '${Time}'
Converted: '${SQL:Select Convert(datetime,'${Time}')}'

AlertTriggerTime: '${AlertTriggerTime}'
Converted: '${SQL:Select Convert(datetime,'${AlertTriggerTime}')}'

With Math: '${SQL:Select Convert(datetime,'${Time}') - Convert(datetime,'${AlertTriggerTime}')}'

The only other thing I could think of is to create a stored procedure to do this, but I don't like putting stored procedures in a database ... (I don't know how to say it, designed/owned/supported/updated/managed/serviced by someone else - solarwinds)

0 Kudos
Level 15

Ok, here is what I have now: ${SQL:Select datediff(day, Convert(datetime,'${AlertTriggerTime}'),Convert(datetime,'${Time}'))} days and ${SQL:Select Convert(time,Convert(datetime,'${Time}') - Convert(datetime,'${AlertTriggerTime}'))}

It's a bit ugly, but I can't think of a better way without some type of extensive "if then else" logic that I don't know how to get it working.  I was hoping I could just apply a better format to it by changing the "Convert(time" to something like "Convert(duration", but I can't find anything easy like that.

Questionario - Have you seen you more of the issue? any pattern?

0 Kudos
Level 14

Hi netlogix,

the string

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

 

seems to return the number of calendar days that have passed, therefore if an alarm goes off at 11pm and clears at 1am, it will return 1day and 2 hours while the alert was only active for 2 hours. you know how to get around this?

0 Kudos
Level 15

I got an alert like that to and have been thinking about it.  I came up with this:

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

I also tested it up to 23:59:59 and it returned 0.  I had a different one that would return 1 after 12:00:00 (half day), but thats not what I want and would assume the same for you.

Thanks for your help on this... we work together pretty well.

 

*** I edited the SQL - I was missing a ")" at the end, there should be 4 not 3

0 Kudos
Level 14

haha, my help? 😉

yes, we are a good team! you do the work and I steal the results! 😉

I will try this out and post again if this does not work, if it does work I will edit the answer of this thread accordingly 🙂

0 Kudos
Level 7

Good day!

And does it works? Try to use all the version from ths topic, but they don't work with cause 

 'MACRO SQL ERROR - The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.'

 

I think my reason is the same, cause:



For now, can you add this to an alert that is having the issue? 

Time: '${Time}'

Converted: '${SQL:Select Convert(datetime,'${Time}')}'

AlertTriggerTime: '${AlertTriggerTime}'

Converted: '${SQL:Select Convert(datetime,'${AlertTriggerTime}')}'

With Math: '${SQL:Select Convert(datetime,'${Time}') - Convert(datetime,'${AlertTriggerTime}')}'



 

Time: '13:08'

Converted: '01.01.1900 13:08:00'

AlertTriggerTime: '22.11.2011 13:08:04'

Converted: 'MACRO SQL ERROR - The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.'

With Math: 'MACRO SQL ERROR - The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.'

 

I have try to

 



"The conversion of a varchar data type to a datetime data type resulted in an out-of-range value."

 

I've been able to correct this error in the past by changing the default database language for the SQL user account. Typically I've seen it when the database user is expecting the dd.mm.yyyy date format, but the application is expecting mm.dd.yyyy

 

You can do this using SQL Server Management Studio

- Open this tool and connect to your SQL server as SA.

- Expand the Security folder

- Expand the Logins folder

- Locate the SolarwindsNPM user (or the user Orion is using to connect to the DB). Right click this user and go to Properties.

- On the General page, towards the end, you will see a drop down menu next to "Default language". 



 

But it's don't help me too.

0 Kudos
Level 14

did you guys try what I marked as solution?

We use this and it works like a treat!

This alert was active for ${SQL:Select Floor(Convert(float,(Convert(datetime,'${Year}-${MM}-${DD} ${HH}:${Minute}:${Second} ${AM/PM}')-Convert(datetime,'${AlertTriggerTime}'))))} days and ${SQL:Select Convert(time,Convert(datetime,'${Year}-${MM}-${DD} ${HH}:${Minute}:${Second} ${AM/PM}') - Convert(datetime,'${AlertTriggerTime}'))}

0 Kudos
Level 7

Yes, of course. But did not work, therefore, decided to ask.



 

did you guys try what I marked as solution?

We use this and it works like a treat!

This alert was active for MACRO SQL ERROR - Преобразование типа данных varchar в тип данных datetime привело к выходу значения за пределы диапазона.

days and MACRO SQL ERROR - Преобразование типа данных varchar в тип данных datetime привело к выходу значения за пределы диапазона.

 





Error began writing in Russian after the change the user of database language, but actually it's the only thing that has changed.
In translation it reads as



MACRO SQL ERROR - The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. 

days and MACRO SQL ERROR - The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.



This is sad.

 

Update/

I'm sorry, i'm a stupid. 

You year.month.day date, but I day.month.year. Naturally swapping everything worked, badly hung up on one's head will not work.Thank you very much!

My version is 



This alert was active for ${SQL:Select Floor(Convert(float,(Convert(datetime,'${DD}-${MM}-${Year} ${HH}:${Minute}:${Second} ${AM/PM}')-Convert(datetime,'${AlertTriggerTime}'))))} days and ${SQL:Select Convert(time,Convert(datetime,'${DD}-${MM}-${Year} ${HH}:${Minute}:${Second} ${AM/PM}') - Convert(datetime,'${AlertTriggerTime}'))}



0 Kudos
Level 14

do you use the 12h or 24h clock in russia?

Try the following for 12h format, if it doesn't work I'll look up the syntax for 24h format (I think its just ${Hour} instead of ${HH}:

This alert was active for ${SQL:Select Floor(Convert(float,(Convert(datetime,'${DD}.${MM}.${Year} ${HH}:${Minute}:${Second} ${AM/PM}')-Convert(datetime,'${AlertTriggerTime}'))))} days and ${SQL:Select Convert(time,Convert(datetime,'${DD}.${MM}.${Year} ${HH}:${Minute}:${Second} ${AM/PM}') - Convert(datetime,'${AlertTriggerTime}'))}

0 Kudos
Level 7

Good day Questionario! 

We use 24H clock. I have already done the script for my needs, after reading your comment yesterday(Thank you that was taken out of torpor). For date like day.month.year and 24h clock it will be



This alert was active for ${SQL:Select Floor(Convert(float,(Convert(datetime,'${DD}.${MM}.${Year} ${Hour}:${Minute}:${Second}')

-Convert(datetime,'${AlertTriggerTime}'))))} days and ${SQL:Select Convert(time,Convert(datetime,'${DD}.${MM}.${Year} ${Hour}:${Minute}:${Second}')

- Convert(datetime,'${AlertTriggerTime}'))}

 



I think its just ${Hour} instead of ${HH}:



You are right.

P.S. I test it only in "Alarm manager test", it's returns me 0 days and 00:00:00. Fortunately / unfortunately nothing is broke) Tomorrow i think i could give 100% answer. I will accomplish your goal here about the results.

0 Kudos
Level 14

sounds like its working fine for you! 🙂

Would you mind trying the original query and only change the time format but not the date format?

Would be nice if we could find a query that works for everyone 😃

0 Kudos