This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Alerting with a custom SQL condition

Hi Guys,

I am relatively new to Solarwinds and I'm trying to set up an alert using an SQL condition.

The end goal is to send out alerts for when we receive this trap 'BGP4-MIB:bgpTraps.0.2'.

This is what im using as the SQL condition to trigger the alert: "select * from [dbo].[Traps] WHERE Hostname IN ('Router1','Router2') AND TrapType = 'BGP4-MIB:bgpTraps.0.2' AND DateTime > DATEADD (minute, -2, Sysdatetime())"

This Query works correctly when used in the database manager and only returns traps sent from those devices within the last 2 minutes, the problem is that when I go to create the alert in solarwinds, it gives the warning that the alert will trigger on 1152 objects! this is odd because those objects include devices with different hostnames and devices that aren't even using BGP.

I'm aware that I can just send out alerts using the trap viewer application but I wanted to manage this with the other alerts.

Would appreciate any guidance or input as to what im missing here.

Thanks.

  • not clear where you are setting this up...are you trying to do this through NPM, I assume?

    If that is the case, you have to tie the trap to a node or some base object.  search for something like trap alert npm.

    ultimately, you'll probably select custom sql alert, use Node as base, then tie to trap IP, but there are a few posts out there showing how to do it.

  • Yep, I'm trying to do this through NPM, using a Custom SQL query as the trigger condition for the alert.

    We've found the problem, under "Set up your SQL condition" there is a drop-down menu, we have "node" selected which is putting a default line "SELECT Nodes.NodeID, Nodes.Caption FROM Node" in front of my custom SQL query.

    This default query is what is causing the alert to trigger on so many objects. None of the options will work with my SQL query, is there any way I can remove this or create my own?

    pastedImage_1.png

  • Yes, you can use Node as your base object and simply JOIN traps in SQL query (see below):

    pastedImage_3.png

    copy-paste below for convenience:

    JOIN Traps t ON t.Hostname = Nodes.Caption

    WHERE

      t.Hostname IN ('Router1','Router2')

      AND t.TrapType = 'BGP4-MIB:bgpTraps.0.2'

      AND t.DateTime > DATEADD (minute, -2, Sysdatetime())

    Note: This will work as long as your traps arrive from the nodes which do exist in SolarWinds. If node does not exist, then JOIN will not produce any rows, even though you may still be receiving traps from those nodes that are not in SolarWinds

  • Hi Alex,

    Thanks for your input,

    With that query I'm no longer seeing 1152 different rows, but now I don't see any. I tested the query on a device that definitely exists and with a trap that triggered within the last 2 minutes.

    this is what I see when I run the query:

    pastedImage_0.png

    and I know that this trap exists:

    pastedImage_1.png

    Is there something I'm missing?

  • Hey Josh,

    I can see the problem is with your 1st SQL query, you cannot use * (special character) in your where clause when you use IN condition, t.Hostname IN ('devicename*').

    Instead, you can try t.Hostname like 'devicename%' or t.Hostname in ('exactdevicename_that_exists_fromthetable').

    Hope this resolves your issue. Thanks.

  • Hi Karthik,

    I used the exact hostname as you suggested, but I'm still not producing any rows from the query.

    pastedImage_0.png

  • Hi Josh,

    Are you sure, Does the hostname exist with short name or FQDN in the table (Traps)?

    If you are not sure, please use like condition instead of IN condition.

    Eg: t.Hostname like 'r32paris02%'

    If still issue persist, please make sure all conditions in where clause satisfies in order to fetch you the data as you are using AND condition. Thanks.

  • I'd also change the time from -2 to -300 or -30 or something.  you are only checking for the last 2 minutes currently and to start, I'd echo KR... use like to  broaden a bit...

    t.hostname like '%paris0%'

    and t.datetime > dateadd (minute, -30, sysdatetime())

    and ..

  • Hi njoylif,

    as you guys suggested I've broadened my criteria to match '%PARIS0%'

    Select Nodes.NodeID, Nodes.Caption FROM Nodes
    JOIN Traps t ON t.Hostname = Nodes.caption

    WHERE

      t.Hostname LIKE '%PARIS0%'

    ^ This doesn't produce any rows for me, but the name does exist in both tables

    pastedImage_0.png

    pastedImage_1.png

    If both tables include that device name, why isn't it producing any rows?

  • Hey Josh,

    The problem here is,

    From your original (base) query, JOIN clause (ON) condition does not satisfy.

    (

    Select Nodes.NodeID, Nodes.Caption FROM Nodes

    JOIN Traps t ON t.Hostname = Nodes.caption)

    Look at the data from both nodes and traps table, where you can see nodes.caption has FQDN and traps.hostname has just shortname. So we cannot use this as condition when you JOIN these 2 tables. Instead try to use nodeid which is available from both the tables.

    (

    Select Nodes.NodeID, Nodes.Caption FROM Nodes

    JOIN Traps t ON t.nodeid=Nodes.nodeid

    )

    Hope this resolves the issue. Thanks.