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 = '188.8.131.52.4.1.197184.108.40.206.1.1.2' OR ONCPA.CustomPollerOid = '220.127.116.11.4.1.19718.104.22.168.4'
I need to know how I can get both mibs to show on the same line vs showing on separate lines.
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
SolarWinds solutions are rooted in our deep connection to our user base in the THWACK® online community. More than 150,000 members are here to solve problems, share technology and best practices, and directly contribute to our product development process.