Simple Display of Multiple Counts

First of all, I'd like to thank the THWACK community for making me look like such a creative genius to this point.

My latest idea sounded oh so simple, but I've not been able to wrap my mind around it. I'm sure I must be missing something very simple.

I have a view that has been put together to display BGP neighbor issues in a variety of formats.

Now I want to add a very simple counter widget to the main NOC page that displays just 2 numbers:

The number of nodes having 1 or more BGP neighbor issues
The number of BGP neighbors that are not in the ESTABLISHED state

I have solved how to count the neighbors, but I cannot figure out how to count up the unique nodes.

SELECT
Count(ORN.NodeID) AS [Neighbors]
FROM Orion.Routing.Neighbors AS ORN
    WHERE ORN.ProtocolID = 14
    AND ORN.IsDeleted = 0
    AND ORN.ProtocolOrionStatus != 1

This will provide me with a table with 1 column "Neighbors" and a count beneath it, but what I want is the number of unique nodes first then the number of neighbors.

Nodes	Neighbors
25	99

There is a part of me that thinks this format would be better, though, in case I were to expand the things being counted later. Bonus Points for this option too:

Error	Count
BGP Nodes	25
BGP Neighbors	99

Thanks in advance!

Find more posts tagged with

Custom Query

bgp neighbors

npm 12.5

Accepted answers

dan_jagnow

What about something like this?

SELECT
    'BGP Nodes' AS [Error],
    COUNT(NodeID) AS [Count]
FROM (
    SELECT DISTINCT ORN.NodeID
    FROM Orion.Routing.Neighbors AS ORN
    WHERE ORN.ProtocolID = 14  
        AND ORN.IsDeleted = 0  
        AND ORN.ProtocolOrionStatus <> 1
)
UNION
(
    SELECT
        'BGP Neighbors' AS [Error],
        COUNT(NeighborID) AS [Count]
    FROM (
        SELECT DISTINCT ORN.NeighborID
        FROM Orion.Routing.Neighbors AS ORN
        WHERE ORN.ProtocolID = 14  
            AND ORN.IsDeleted = 0  
            AND ORN.ProtocolOrionStatus <> 1
    )
)

All comments

wluther

christophercamacho1 Just to clarify, you're NOT looking to list/group the number of Neighbors per node, correct?

Do you already have the SWQL query to show you the numbers you want to see for each column? (You obviously already have the query to show the neighbors, or course.)

dan_jagnow

What about something like this?

SELECT
    'BGP Nodes' AS [Error],
    COUNT(NodeID) AS [Count]
FROM (
    SELECT DISTINCT ORN.NodeID
    FROM Orion.Routing.Neighbors AS ORN
    WHERE ORN.ProtocolID = 14  
        AND ORN.IsDeleted = 0  
        AND ORN.ProtocolOrionStatus <> 1
)
UNION
(
    SELECT
        'BGP Neighbors' AS [Error],
        COUNT(NeighborID) AS [Count]
    FROM (
        SELECT DISTINCT ORN.NeighborID
        FROM Orion.Routing.Neighbors AS ORN
        WHERE ORN.ProtocolID = 14  
            AND ORN.IsDeleted = 0  
            AND ORN.ProtocolOrionStatus <> 1
    )
)

christophercamacho1

That works perfectly in SWQL Studio, yet when I plug it into a Custom SWQL Query to display it on the website I get the dreaded "There was an error processing the request" error message... Any ideas why?

christophercamacho1

Right - not what I need. I already have a nice custom query for this on the main routing view. I'm just looking for a small summary widget that I can place on the main NOC view to signify the need to investigate routing without cluttering the front page up.

dan_jagnow

It could be a problem with the SELECT DISTINCT. See Re: Using custom SWQL to search for subnets.

christophercamacho1

After reading about that issue and pondering on it a short while, I added "ORDER BY Error" to your query above and now it works perfectly!

Thank you!

SELECT
    'BGP Nodes' AS [Error],
    COUNT(NodeID) AS [Count]
FROM (
    SELECT DISTINCT ORN.NodeID
    FROM Orion.Routing.Neighbors AS ORN
    WHERE ORN.ProtocolID = 14 
        AND ORN.IsDeleted = 0 
        AND ORN.ProtocolOrionStatus <> 1
)
UNION
(
    SELECT
        'BGP Neighbors' AS [Error],
        COUNT(NeighborID) AS [Count]
    FROM (
        SELECT DISTINCT ORN.NeighborID
        FROM Orion.Routing.Neighbors AS ORN
        WHERE ORN.ProtocolID = 14 
            AND ORN.IsDeleted = 0 
            AND ORN.ProtocolOrionStatus <> 1
    )
)
ORDER BY Error