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.

Trying to alert on a total count of interfaces that have interfaceindex = '-1' (stale).

hebron1212 over 2 years ago

I've got a SQL query that works in Database Manager, but the alert requires me to join that with a setup statement. I've done this to get a count of events per node, which works, but just a total count has me puzzled. Both the setup statement and the data I need counted are in the same table, so I'm not sure what to join together in the body of the alert.

I think I need something like this, but what do I put in the question marks?

SELECT Interfaces.FullName, Interfaces.InterfaceID, FROM Interfaces

Left JOIN (
select count(*) as stalecount FROM [dbo].[Interfaces]
where interfaceindex = '-1'

group by Interfaces.ObjectSubType
HAVING COUNT(*) >= 2
)

AS a ON a.??????=???????
Where stalecount >= 2

Btw, what's the purpose of the setup statement? Why not be able to just put the sql query in without it?

Parents

0 sum_giais over 2 years ago

Hey there,

I see the frustration with the 'setup' SELECT statements... I have had sometimes found ways to better the query without that restriction. That said when you think about it and take the time to revise your query its not all that difficult to join in what you need.

If I had to hazard a guess... the preexisting and non-editable SELECT statements are there to keep the alertable Orion Entity consistent for the alert process flow. Still many ways to fudge the custom SWQL or SQL alerts however but I continually find them necessary for monitoring in SolarWinds Orion.

Anyways... you need to add interface ID to your sub query to join it in appropriately like so:

INNER JOIN (
SELECT COUNT(i.InterfaceID) AS [TotalInterfaces], i.InterfaceID
FROM [dbo].[Interfaces] AS i
WHERE i.InterfaceIndex = -1
GROUP BY i.InterfaceID ) AS sub1 ON sub1.InterfaceID = Interfaces.InterfaceID
WHERE sub1.TotalInterfaces >= 2

This may require a few more adjustments and testing but should give you the idea and a starting point... I purposefully removed single quotes around the -1 as I think the InterfaceIndex is an integer. I could be wrong as I'm mobile and can't verify in my lab right now.

Best of luck and hope this helps you -
Cancel
Vote Up 0 Vote Down

Cancel

Reply

0 sum_giais over 2 years ago

Hey there,

I see the frustration with the 'setup' SELECT statements... I have had sometimes found ways to better the query without that restriction. That said when you think about it and take the time to revise your query its not all that difficult to join in what you need.

If I had to hazard a guess... the preexisting and non-editable SELECT statements are there to keep the alertable Orion Entity consistent for the alert process flow. Still many ways to fudge the custom SWQL or SQL alerts however but I continually find them necessary for monitoring in SolarWinds Orion.

Anyways... you need to add interface ID to your sub query to join it in appropriately like so:

INNER JOIN (
SELECT COUNT(i.InterfaceID) AS [TotalInterfaces], i.InterfaceID
FROM [dbo].[Interfaces] AS i
WHERE i.InterfaceIndex = -1
GROUP BY i.InterfaceID ) AS sub1 ON sub1.InterfaceID = Interfaces.InterfaceID
WHERE sub1.TotalInterfaces >= 2

This may require a few more adjustments and testing but should give you the idea and a starting point... I purposefully removed single quotes around the -1 as I think the InterfaceIndex is an integer. I could be wrong as I'm mobile and can't verify in my lab right now.

Best of luck and hope this helps you -
Cancel
Vote Up 0 Vote Down

Cancel

Children

No Data