cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 12

Question on widget set up for a modern dashboard

I have a query that I have created.  

SELECT ONodes.Caption as [Node Name] , ONCPA.CustomPollerStatistics.Status , ONCPA.CustomPollerStatistics.RawStatus , ONCPA.CustomPollerStatistics.DateTime FROM Orion.NPM.CustomPollerAssignment ONCPA inner join Orion.Nodes ONodes on ONodes.NodeID = ONCPA.NodeID inner join (select ONCPA.NodeID, max(ONCPA.CustomPollerStatistics.DateTime) as MaxDate from Orion.NPM.CustomPollerAssignment ONCPA group by ONCPA.NodeID) b on ONCPA.NodeID = b.NodeID and ONCPA.CustomPollerStatistics.DateTime = b.MaxDate where ONCPA.CustomPollerMIB LIKE '%DATA-DOMAIN%' and ONCPA.CustomPollerOid = '1.3.6.1.4.1.19746.1.3.5.1.1.2' OR ONCPA.CustomPollerOid = '1.3.6.1.4.1.19746.1.3.1.4'

I need to know how I can get both mibs to show on the same line vs showing on separate lines.  

 

 

0 Kudos
1 Reply
MVP
MVP

Hey there - I hope you've had a restful weekend and you're reading this on Monday!

First, there are some more convenient entities in SWQL to get this information so you don't have to crawl through the assignment table. The one that's probably the most interesting is Orion.NPM.CustomPollerStatusOnNode. It assumes that they're node pollers and does the work of combining the data from the UnDP with connecting back to the Orion.Nodes entity (providing a Node.ID).

Second, because there's no pivot function in SWQL you have to work around that by using an awkward maneuver with MAX and CASE.

Give this a go and let me know if it gets you any closer to what you're looking for. You would want to use your poller names instead of the two I used.

SELECT
    Nodes.Caption
    , MAX(CASE WHEN Pollers.UniqueName = 'vmGuestOS' THEN PollerStatus.Status END) AS [vmGuestOS Status]
    , MAX(CASE WHEN Pollers.uniqueName = 'VmMemSize' THEN PollerStatus.Status END) AS [VmMemSize Status]
    , MAX(CASE WHEN Pollers.UniqueName = 'vmGuestOS' THEN PollerStatus.RawStatus END) AS [vmGuestOS RawStatus]
    , MAX(CASE WHEN Pollers.uniqueName = 'VmMemSize' THEN PollerStatus.RawStatus END) AS [VmMemSize RawStatus]
    , MAX(CASE WHEN Pollers.UniqueName = 'vmGuestOS' THEN PollerStatus.DateTime END) AS [vmGuestOS DateTime]
    , MAX(CASE WHEN Pollers.uniqueName = 'VmMemSize' THEN PollerStatus.DateTime END) AS [VmMemSize DateTime]
FROM Orion.Nodes AS Nodes
INNER JOIN Orion.NPM.CustomPollerStatusOnNode AS PollerStatus ON Nodes.NodeID = PollerStatus.NodeID
INNER JOIN Orion.NPM.CustomPollers AS Pollers ON PollerStatus.CustomPollerID = Pollers.CustomPollerID
INNER JOIN
(
    SELECT
        PollerStatus.NodeID
        , MAX(PollerStatus.DateTime) AS [DateTime]
    FROM Orion.NPM.CustomPollerStatusOnNode AS PollerStatus
    GROUP BY PollerStatus.NodeID
) AS LatestPoll ON Nodes.NodeID = LatestPoll.NodeID AND PollerStatus.DateTime = LatestPoll.DateTime
WHERE Pollers.UniqueName IN ('vmGuestOS', 'VmMemSize')
GROUP BY Nodes.Caption

 

 

0 Kudos