To configure alerts on traps by comparing the any value in the trap details??? If so, then how????
Use a custom SQL alert on the node and some SQL like this:
WHERE NODEID IN ( select NODEID from traps
INNER JOIN trapvarbinds tv
ON traps.trapid = tv.trapid
WHERE traps.traptype = 'BGP4-MIB:bgpTraps.0.2'
and traps.DateTime < dateadd(hour,-1,getdate())
AND acknowledged = 0
AND tv.oid LIKE '1.3.6.1.2.1.15.3.1.2.%'
AND tv.oidvalue = 'idle(1)'
)
this looks for a specific trap type on the node, and specific values in the trapvarbinds.
Richard
i dont know how i do this can you plz tell me the way how sql alert for trap will be configured??
start here: Video Tutorial: Orion Advanced Alerts - Videos | SolarWinds
This tutorial is about Advance alerts not covering the portion of trap alert configuration. Kindly tell some other solution. I'll be very thankful to you.
Wow. Never thought to use Custom SQL in Advanced Alerts. Thanks for the Trap code. Do you have a similar Where clause for Syslog?
-=Dan=-
here you go -- if a node reports that it cannot reach its NTP server more than a ten times in the past hour...
WHERE nodeid IN (SELECT nodeid
FROM (SELECT Count(*) errcount,
nodeid
FROM syslog
WHERE syslogseverity = 3
AND datetime < Dateadd(hour, -1, Getdate())
AND message LIKE '%NTP Server Unreachable'
GROUP BY nodeid
HAVING Count(*) > 10) T)
or, more simply if any node reports not reaching its NTP server in the past hour
AND message LIKE '%NTP Server Unreachable')
/RjL
p.s. http://www.dpriver.com/pp/sqlformat.htm is a nice SQL reformatter
Awesome. Thanks. A lot simpler without the joins. Getting Universal pollers is just plain UGLY.
Sure pays to get SQL proficient if you want to fully utilize NPM.
Is there a good source for learning subqueries, complex aggregation , use of temp tables, etc?
Thanks for sharing.
The SQL for Dummies book is really quite a good introduction; I find it steers one away from vendor-specific SQL. I've personally avoided temp tables, mostly because in many environments the DBA do not like them, and the databases I was used to work with usually had a couple of TB of data.
The other think I would recommend people do is try SQL queries in a real SQL tool (TOAD, or the MS workbench). the reason is that these will give you the output of explain plan -- an estimate of the 'cost' of running your query. Good code review practices often require programmers attach the output of explain plan to the check-in (forces the programmer to at least run the command, even if they ignore it)
consider this:
SELECT nodeid
compared to
AND Datediff(hour, Getdate(), datetime) <= 1
one of these queries runs a lot faster than the other -- it does an index search on the syslog table; the other does a full tablescan and subtraction on every date in the table.
explain plan will help you optimise your SQL.
Hi Richard
Could you help me with a similar case: http://thwack.solarwinds.com/thread/59316
Thanks,
Simon
