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

Help with node downtime on the node up alert

Jump to solution

Hi all,

I am having trouble reporting a Nodes downtime in the back up alert.

I am using this:

${SQL:select distinct datediff(minute,e.eventtime,getdate()) from events e where e.networknode = ${NodeID}}

But it always reports 1 minute.

I run this in Report Writer on a test node just after I bring the node back up:

select distinct datediff(minute,e.EventTime,getdate())  from events e where e.networknode=197

And it shows:

1

8

10

178

...

The number I am after is the 8 as it was down for 8 mins, but another event gets the top spot?

So, how do I select the correct event to report node downtime?

1 Solution

so, I think the way for you to work is:

${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}'))}

as a side note, would you normally write today's date as 10/3/2012 or 3/10/2012?  your SQL server thinks it should be written like 3/10/2012, but my first sql statement was setup like 10/3.

View solution in original post

0 Kudos
39 Replies
Level 9

Thank you that does the trick!

The first answer that comes up when you Google this gives an answer that no longer seems to work.

0 Kudos

Have you had to update the query once you upgraded to 11.5, looks like it broke post upgrade

This alert was active for MACRO SQL ERROR - Conversion failed when converting date and/or time from character string. days and MACRO SQL ERROR - Conversion failed when converting date and/or time from character string.

0 Kudos

Hi,

On 11.5 I just use the new built in method:

${N=Alerting;M=DownTime}

0 Kudos

What is the output format of ${N=Alerting;M=DownTime}?  I'm trying to correlate the values I'm getting with this to actual downtime durations for nodes using the simulate option in 11.5 and they are not adding up.

0 Kudos

Mine spits out the downtime in minute's. I don't beleive the simulate feature gives a proper figure for downtime.

0 Kudos

running NPM 11.5.2 now and did notice that we can include "Node Down time" variable which displays in minutes...   Though, have to agree that reading "Down time 16hrs 4min"  would be much easier than " Down time 964 mins"

0 Kudos

thanks dude

0 Kudos
Level 15

This is what I use in the Reset action:

Time from alert to now: ${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}'))}

---- or ----- (For you aussies)

Time from alert to now: ${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}'))}

Hey netlogix,

This was working for abit but now displays the error:

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.

any idea?

0 Kudos

Are you using HTML emails?  if so try you need to replace the inner "{" and "}" with "@" like this: ${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

Hey netlogix,

No i'm just on plain text..

0 Kudos

Something is coming out wrong, get the pre-convert values to see why it is complaining:

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

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


Then look at it, or pop it in SQL query analyzer to find out why it is complaining

0 Kudos

Hey netlogix,

Sorry to be a newb but how do I get the pre-convert values? I poped in the code in your post and even tried:

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

But it just displays that as text?

0 Kudos

hmmm we are trying to figure out what ${Year}-${MM}-${DD} ${HH}:${Minute}:${Second} ${AM/PM} and ${AlertTriggerTime} display as.  The issue may have to do with how your SQL interprets time.  (your location settings changes how SQL looks at dates and times)

0 Kudos

I can see through DB manager that the Trigger Time Stamp looks like this: 27/09/2012 5:17:11 PM

Does that help?

Not sure how to find out the ${Year}-${MM}-${DD} ${HH}:${Minute}:${Second} ${AM/PM} details?

0 Kudos

you put just that in your reset email.  oh, how are you testing this?  if you are using the "Test Alert" button.  It might not work right with that.

0 Kudos

Testing by changing the IP to something non existent on a node.

Results are:

2012-10-02 10:22:20 AM

and

2/10/2012 10:08:19 AM

0 Kudos

ah, good, that should work just fine, if you open sql tools , what do you get if you use:

select Convert(float,(Convert(datetime,'2012-10-02 10:22:20 AM')-Convert(datetime,'2/10/2012 10:08:19 AM'))), Convert(time,Convert(datetime,'2012-10-02 10:22:20 AM') - Convert(datetime,'2/10/2012 10:08:19 AM'))

I get:

235.009733796296

00:14:01.0000000

Ah, HA! are you British?  Or do you do your dates like them (military?)?  2/10/2012 or 10/2/2012?  I see feb 10th, not Oct 2nd

try ${DD}/${MM}/${Year} ${HH}:${Minute}:${Second} ${AM/PM}

0 Kudos

We get..

235.009733796296

00:14:01.0000000

as well.

Ahh. Nope. Aussie here

${DD}/${MM}/${Year} ${HH}:${Minute}:${Second} ${AM/PM} returns:

03/10/2012 01:12:09 PM

0 Kudos

so, I think the way for you to work is:

${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}'))}

as a side note, would you normally write today's date as 10/3/2012 or 3/10/2012?  your SQL server thinks it should be written like 3/10/2012, but my first sql statement was setup like 10/3.

View solution in original post

0 Kudos