Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials
Store

SQL query challange (for me)

Does anyone know of a good way to define a variable that will display a nodes total downtime? So, on the Alert Reset action, the alert message can use this variable and quickly display the toal downtime of a system. The {LastBoot} variable is neat but doesn't really tell me anything useful.

Why this proposed variable of, say, "{TotalCurrentDowntime}" is useful to me is because we have hundreds of system nodes- some of which get rebooted and back online before others. So, for example, my inbox looks like this (simplified):

10:18am   "System A is back up"
9:38am   "System B is back up"
9:14am   "System B is down"
9:00am   "System A is down"

...and it would be useful to match up down systems by their total downtime. I'm guessing this would be a custom sql query but I'm a sql novice and I'm shamefully asking for assistance.

Or, perhaps some of you other admins know of a completely better way to acheive what I'm after :-)

Thanks in advance!

Find more posts tagged with

Accepted answers

vhcato

Here's an example that measures the difference (in minutes) between the trigger and reset times.

${NodeName} was down for ${SQL:Select TriggerTimeOffset / 60 From AlertStatus Where AlertDefID = (Select AlertDefID From AlertDefinitions Where AlertName = '${AlertName}') AND ActiveObject = '${NodeID}'} minutes.

We use this same methodology to include the number of hours that a router has been down in an escalated alert when one of our remote routers has been down for more than 4 hours, and we have it repeat every 2 hours after that. The thing to keep in mind is that to get a true representation of downtime, the alert has to trigger and reset immediately (not have a delay configured) so that it's timestamped accurately. Also, remember that, by default, Orion doesn't mark a device as "down" until a hold-down period of 2 minutes (configurable). If your alert is reset as a result of restarting processes or changes made to the alert itself, this may also cause the timestamps to change, which skews this number.

Will that work? If not, I'll go back to the drawing board and come up with something else.

All comments

afsprau

There is one in theContent sharing zone that I have used. It reports on each event over past month, may work for you.

SELECT
    StartTime.EventTime,
    Nodes.Caption,
    Nodes.Location,
    StartTime.Message,
    DATEDIFF(Mi, StartTime.EventTime,
    (SELECT TOP 1
        EventTime
        FROM Events AS Endtime
        WHERE EndTime.EventTime > StartTime.EventTime AND EndTime.EventType = 5
            AND EndTime.NetObjectType = 'N'
            AND EndTime.NetworkNode = StartTime.NetworkNode
        ORDER BY EndTime.EventTime)) AS OutageDurationInMinutes

FROM Events StartTime INNER JOIN Nodes ON StartTime.NetworkNode = Nodes.NodeID
WHERE (StartTime.EventType = 1) AND (StartTime.NetObjectType = 'N') AND
eventtime between dateadd(month, -1, getdate()) and getdate()
ORDER BY Nodes.Caption

mandg

Thanks! I ran that query against the Nodes table and it worked pretty neatly. Is there a way to add this custom query into the Alerts message body? And just trim it down for a particualr node (not the all systems in the last month)?

vhcato

Are you really only looking for the duration in minutes for the outage that just occurred to be included in the reset email? Using a derivative of the example above would tell you the duration of every outage associated with the device in question for the last 30 days instead of simply the one that just occurred.

At any rate, it's relatively easy to do, but I wanted to make sure I understood exactly what you want.

mandg

Thanks, vhcato-

You've described exactly what I'm looking to do (I suspected it was relatively simple but me and simple never seem to get along). The time delta showing the sum of the nodes "Down" timestamp and the "Up" timestamp. And, ideally, a way to somehow insert this in the Alert reset action (email message body in this case).

I may just be making this effort much harder than it actually is.So "afsprau's" sql query was neat, but not necessarily what I was looking for in this case.

vhcato

Here's an example that measures the difference (in minutes) between the trigger and reset times.

Will that work? If not, I'll go back to the drawing board and come up with something else.

vhcato

Cool... I'm glad it worked for you.

The ability to add custom SQL queries is extremely powerful, as it allows one to represent any bit of info in the database related to the element in question. If it's in the database, you can pull it out of there. We actually have some custom tables that we use to document all of the pertinent info for all of our locations, and we use SQL queries to look up and display info about the location in the alert messages, as well as to display the status info or "comments" about the node or interface that have been entered via the website by various support folks. Using this, for example, when we send an escalation alert stating that a site has been down for more than 4 hours, we include the values of some of the fields in our custom tables such as the site priority and access hours, as well as the comments about the troubleshooting that has been done by our front-line operations folks. As these alerts repeat every 2 hours following the initial escalation at the 4 hour mark, our management and tier 2/3/4 support personnel can keep updated on what's happening just by reading the updated text that's entered by the tier 1 WAN Ops people on the Orion website. Works pretty well.

The ability to do this in the email alerts is great, but what we really need is the ability to use custom SQL queries as conditions for the triggers and resets themselves. This would be HUGELY powerful. There's another post on here where this was brought up, and I plan to start a new thread just to see how much support we can foster for getting this feature added as quickly as possible.

Oh, what did you mean by the following?

it would be useful to match up down systems by their total downtime.

mandg

Yes, I agree that enabling customizable queries would further empower those like yourself.

As far as my quote above, I was just asking for the ability to look at my email alerts and determine the node down/up delta - which your query did exactly. Thanks again.

vhcato

Yes, I agree that enabling customizable queries would further empower those like yourself.

I envision having people create alerts and share them with everyone in the Content Sharing Zone. This way everyone can have access to them and be able to take advantage of the collective efforts and abilities of the community. Maybe that's a little too Utopian, but maybe someday...