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

Custom SQL Alert

Hello,

I am wondering if anybody could help me, we have SNMP traps coming in from our BMC scheduler and we want to get advanced alerting working in Solarwinds so we can have escalation etc. There are a few problems I'm coming up against that I'm not sure I am going to be able to achieve what I want.

1. There is no SNMP reset trap only the job failure.

2. I need to alert on pretty much every job (with a few exceptions).

The trap looks like the following:

I've got a basic SQL custom alert working:

WHERE nodeid IN (SELECT nodeid FROM traps WHERE TrapType = 'BMC-CONTROLEM-MIB:alertTrap.10' AND DateTime > DateAdd(minute, -1, Sysdatetime())

However once that has been triggered it won't alert again on the next job failure. Without creating an alert definition for every 'alertTrapJobName' can anyone think of a better solution as there are thousands of jobs?

Also as I don't get a reset trap the reset condition would just need to be based on whether the advanced alert had been acknowledged. Can you query the AlertActive table in the reset condition?

Hopefully someone can help me!

Thanks

Find more posts tagged with

Accepted answers

All comments

philaries

Just as a bit of an update I've got a bit further in that in the SNMP trap I can use the 'alertTrapNodeId' which will obviously create more than one alert but then sometimes more than one job might fail on the same node. The 'alertTrapOrderId' will always be unique but without setting up a lot of alert definitions I can't see how I'm going to keep all of the alerts active on the same node until they are acknowledged.

philaries

Ok, so what I've done is create a trigger that gives me a unique NodeID for each trap that comes in so that each one will alert.

SELECT Nodes.Caption, Nodes.NodeID FROM Nodes

WHERE Nodes.Caption IN ('NotApplicable')

UNION ALL

SELECT trapvarbinds.OIDValue_Unicode + CAST(ROW_NUMBER() OVER(PARTITION BY OIDValue_Unicode ORDER BY OIDValue_Unicode DESC) as varchar(5)) Caption, ((select COUNT(*) FROM traps where traps.traptype = 'BMC-CONTROLEM-MIB:alertTrap.10' and [DateTime] BETWEEN DATEADD(DAY,(DATEPART(dw,getdate())-(DATEPART(dw,getdate())*2)),GETDATE()) and GETDATE()) + ROW_NUMBER() OVER(ORDER BY OIDValue_Unicode ASC)) NodeID

FROM traps with (nolock) INNER JOIN trapvarbinds with (nolock)

ON traps.trapid = trapvarbinds.trapid

AND traps.traptype = 'BMC-CONTROLEM-MIB:alertTrap.10'

AND trapvarbinds.OIDName = 'alertTrapOrderId'

AND traps.datetime > Dateadd(HOUR, -1, Sysdatetime())

AND acknowledged = '0';

When I run this in the Database Manager I get the following results:

0ckx6	166
0cn4c	167
0cn4c	168
0cn4c	169
0cn4e	170
0cn4e	171

That should in theory create 6 alerts against 6 different NodeID's? However when I put it into the custom SQL alert it doesn't fire off any alerts.

If I change the SQL to be:

SELECT Nodes.Caption, Nodes.NodeID FROM Nodes

WHERE Nodes.Caption IN ('NotApplicable')

UNION ALL

SELECT trapvarbinds.OIDValue_Unicode + CAST(ROW_NUMBER() OVER(PARTITION BY OIDValue_Unicode ORDER BY OIDValue_Unicode DESC) as varchar(5)) Caption, '99' NodeID

FROM traps with (nolock) INNER JOIN trapvarbinds with (nolock)

ON traps.trapid = trapvarbinds.trapid

AND traps.traptype = 'BMC-CONTROLEM-MIB:alertTrap.10'

AND trapvarbinds.OIDName = 'alertTrapOrderId'

AND traps.datetime > Dateadd(HOUR, -1, Sysdatetime())

AND acknowledged = '0';

Then the alert's work against the rows that have the NodeID of 99. I don't get what the difference, clearly Solarwinds doesn't like something with that query.

Does anyone have any ideas?

philaries

No matter it looked like the Union was causing the issue, I simplified the query.

SELECT Nodes.Caption, Nodes.NodeID FROM Nodes

WHERE Nodes.NodeID IN (

SELECT ((select COUNT(*) FROM traps where traps.traptype = 'BMC-CONTROLEM-MIB:alertTrap.10' and [DateTime] BETWEEN DATEADD(DAY,(DATEPART(dw,getdate())-(DATEPART(dw,getdate())*2)),GETDATE()) and GETDATE()) + ROW_NUMBER() OVER(ORDER BY OIDValue_Unicode ASC)) NodeID

FROM traps with (nolock) INNER JOIN trapvarbinds with (nolock)

ON traps.trapid = trapvarbinds.trapid

AND traps.traptype = 'BMC-CONTROLEM-MIB:alertTrap.10'

AND trapvarbinds.OIDName = 'alertTrapOrderId'

AND traps.datetime > Dateadd(HOUR, -1, Sysdatetime())

AND acknowledged = '0'

)

Now for every trap I get an alert that won't keep getting overwritten if it is re-triggered against the same node.