Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials
Store

Multiple sets of Data from the same column?

Hi there everyone,

I'm looking for a solution to a bit of a strange predicament, and I'm hoping the SQL experts on here will be able to point me in the right direction!

I'm trying to put together a report which collects multiple different values from the Universal Device Pollers (NPM.CustomPollerAssignment NPM.CustomPollerStatus) but running into an issue because the different sets of data are stored in the same column.

Is there a way to get Status from rows with "xupsBatteryAbmStatus" in the AssignmentName column to show up in a different column as the Status values from rows with "BatteryLifeRemaining" in the AssignmentName column?

Find more posts tagged with

swql query

sql

SWQL

SWQL subquries

Accepted answers

sum_giais

Hey there @ConnorS68,

Give this query a go, have done this before and to be honest I don't have the exact query in front of me to reference but I think it's close.

There's also a couple portions in your query that weren't necessary so I didn't include them. Hope this helps but let me know if you run into issues with it.

SELECT
     n.Caption
    ,p1.Status AS [Battery Status]
    ,p2.Status AS [Battery Life]
FROM Orion.Nodes AS n
LEFT JOIN Orion.NPM.CustomPollerStatusOnNodeTabular AS p1 ON p1.AssignmentName LIKE '%BatteryAbmStatus%' AND n.NodeID = p1.NodeID
LEFT JOIN Orion.NPM.CustomPollerStatusOnNodeTabular AS p2 ON p2.AssignmentName LIKE '%BatteryLifeRemaining%' AND p2.RowID = p1.RowID AND n.NodeID = p2.NodeID
WHERE n.CustomProperties.Device = 'UPS'

All comments

ConnorS68

Request I tried:

SELECT  n.Caption,  cp.AssignmentName,  cs.Status 

FROM  Orion.Nodes   n 

INNER JOIN  Orion.NodesCustomProperties   c  ON  c.NodeID  =  n.NodeID

INNER JOIN  Orion.NPM.CustomPollerAssignment   ca  ON  ca.NodeID  =  n.NodeID

INNER JOIN  Orion.NPM.CustomPollerStatus   cs  ON  cs.CustomPollerAssignmentID  =  cp.CustomPollerAssignmentID

WHERE  c.Device  Like 'UPS' AND (ca.AssignmentName  Like '%BatteryAbmStatus%' OR  ca.AssignmentName  Like '%BatteryLifeRemaining%') AND  ca.CustomPollerAssignmentID  IN (

    SELECT  a.CustomPollerAssignmentID 

    FROM  Orion.NPM.CustomPollerAssignment   a 

JOIN  Orion.NPM.CustomPollerStatus   b  ON  b.CustomPollerAssignmentID  =  a.CustomPollerAssignmentID

    GROUP BY  a.CustomPollerAssignmentID 

)

sum_giais

Hey there @ConnorS68,

Give this query a go, have done this before and to be honest I don't have the exact query in front of me to reference but I think it's close.

There's also a couple portions in your query that weren't necessary so I didn't include them. Hope this helps but let me know if you run into issues with it.

SELECT
     n.Caption
    ,p1.Status AS [Battery Status]
    ,p2.Status AS [Battery Life]
FROM Orion.Nodes AS n
LEFT JOIN Orion.NPM.CustomPollerStatusOnNodeTabular AS p1 ON p1.AssignmentName LIKE '%BatteryAbmStatus%' AND n.NodeID = p1.NodeID
LEFT JOIN Orion.NPM.CustomPollerStatusOnNodeTabular AS p2 ON p2.AssignmentName LIKE '%BatteryLifeRemaining%' AND p2.RowID = p1.RowID AND n.NodeID = p2.NodeID
WHERE n.CustomProperties.Device = 'UPS'

ConnorS68

You're a godsend.

Although I had to edit your query a bit to get the right data, this absolutely gave me what I needed which is assigning different variable names (s1.Status and s2.Status)

CustomPollerStatusOnNodeTabular does not exist so I changed that to CustomPollerAssignment to grab the AssignmentName variable, then added two more LEFT JOINs for the CustomPollerStatus s1.Status variable.

Here's the working query for all who stumble upon this:

SELECT
     n.Caption
    ,n.IP_Address
    ,s1.Status AS [Battery Status]
    ,s2.Status AS [Battery Life]
FROM Orion.Nodes AS n
LEFT JOIN Orion.NPM.CustomPollerAssignment AS p1 ON p1.AssignmentName LIKE '%BatteryAbmStatus%' AND n.NodeID = p1.NodeID
LEFT JOIN Orion.NPM.CustomPollerAssignment AS p2 ON p2.AssignmentName LIKE '%BatteryLifeRemaining%' AND p2.NodeID = p1.NodeID AND n.NodeID = p2.NodeID
LEFT JOIN Orion.NPM.CustomPollerStatus AS s1 ON s1.CustomPollerAssignmentID = p1.CustomPollerAssignmentID AND p1.AssignmentName LIKE '%BatteryAbmStatus%'
LEFT JOIN Orion.NPM.CustomPollerStatus AS s2 ON s2.CustomPollerAssignmentID = p2.CustomPollerAssignmentID AND p2.AssignmentName LIKE '%BatteryLifeRemaining%'
WHERE n.CustomProperties.Device = 'UPS'