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.

Question on widget set up for a modern dashboard

trilobite_rex over 3 years ago

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 mrxinu over 3 years ago

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