Hello Guys,
i created an alert to trigger when a node configuration changed in the last 24hours and then send a message to splunk. my query is return the values on the database but is not alerting on Orion. What could it be?
in reports:
in alerts:
The message "will immediately trigger for 0 object(s)" means the composed query the alert engine built came back empty, so the gap is between what you ran in the query builder and what the alert actually executes. A few things to check, roughly in the order I'd check them.
Isolate condition vs. action first. Temporarily add an email action alongside the Splunk one. If email doesn't fire either, it's the condition (which your 0-objects message already suggests). Saves you chasing the Splunk side for nothing.
Click Validate, then bisect the query. Strip it back to just the first join and confirm you get a non-zero count, then add one clause at a time. Whichever line drops it to 0 is your answer, and this takes about two minutes versus theorizing. Also retype that last line by hand instead of pasting. If your SQL came out of a doc or an email, smart quotes and non-breaking spaces paste in invisibly and the parser just returns nothing rather than complaining. Your screenshot has what looks like a stray character after DiffFlag = 1, though that may just be the cursor.
Rework it as a subquery. The custom SQL trigger wants a clean, distinct list of the object type you selected, and your preview is already showing NodeID 2655 four times, which means the joins are fanning out. The alert engine also appends its own predicates (limitations, scope) onto your text, and it handles that a lot more predictably when your text is a single WHERE. So, in the editable box put:
WHERE Nodes.NodeID IN ( SELECT NC.CoreNodeID FROM NCM_Nodes NC INNER JOIN NCM_ConfigArchive CA ON CA.NodeID = NC.NodeID INNER JOIN NCM_CacheDiffResults DIFF ON DIFF.NodeID = CA.NodeID WHERE CA.DownloadTime >= DATEADD(HOUR, -24, GETDATE()) AND DIFF.ComparisonType = 4 AND DIFF.DiffFlag = 1 )
Same logic, one row per node, and nothing for the engine to trip over. Strip any ORDER BY or GROUP BY out of the trigger while you're at it, since anything the engine can't append an AND to will break it.
A correctness issue separate from the firing problem. Your diff join is on NodeID only, so the condition currently reads "this node has a config downloaded in the last 24 hours, and this node has a matching diff row at some point in its history." Those two facts aren't tied to each other. If NCM_CacheDiffResults has a config-level key in your version, join on that instead so the diff belongs to the config that actually landed in your window. Otherwise, you'll eventually alert on nodes that just got polled, not nodes that changed.
Trigger Condition page, Advanced options, plus the scope on the next page. If there's a group or custom property limitation that doesn't include those RCC nodes, the query is fine and the engine is filtering them out afterward. Also confirm the alert is enabled and check the evaluation frequency and any time-of-day schedule.
Sanity-check the clock. Run SELECT MAX(DownloadTime) FROM NCM_ConfigArchive, then compare to GETDATE() and GETUTCDATE(). Plenty of Orion columns are UTC, and if that one is, your 24-hour window is offset by however far your server is from UTC. Won't explain a hard 0 by itself, but it'll bite you later.
Querying NCM_ tables directly work until an upgrade changes them, and the cache tables in particular aren't really meant as an alerting source. If NCM's built-in config change trigger covers your use case, or you can express this against the Orion.NCM entities in SWQL, that'll survive upgrades a lot better than this will. For the Splunk hop, an HTTP POST action to the HEC or a syslog action is generally cleaner than whatever custom path you're on now.
