How To Add Multiple CustomPoller To Report As Columns

I'm having trouble find the right SWQL to add the current values of two custom pollers to a report. The query I've been working with:

SELECT ONPMC.Node.Caption, ONPMC.Node.IPAddress, ONPMC.Node.IOSImage, ONPMC.CustomPollerName, ONPMC.AssignmentName, ONPMC.CurrentValue AS FlashSize
FROM Orion.NPM.CustomPollerAssignmentOnNode ONPMC
WHERE ONPMC.CustomPollerName in ('customFlashSize', 'customFreeSpace')

Turns out like:

So I can add at least one custom poller as a column but not sure how I would go about adding the other poller as a column. I'm not very well versed in SQL but if someone could provide any info as to how I could construct such a query, I would appreciate the help. Is this even possible? Would I need some type of JOIN? Thanks.

Find more posts tagged with

report creation

sql

custom swql report

swql_query

report

Accepted answers

mesverrum

This should work, basically when you have a table with multiple rows that you want to turn into multiple columns the easiest way is to filter your column to one kind of row, then left join in the same data with a different filter for the next set of rows, and you can just keep doing that for as many columns as you want to add.

SELECT ONPMC.Node.Caption, ONPMC.Node.IPAddress, ONPMC.Node.IOSImage, ONPMC.CustomPollerName, ONPMC.AssignmentName, ONPMC.CurrentValue AS FlashSize, ONPMC2.AssignmentName, ONPMC2.CurrentValue AS FreeSpace
FROM Orion.NPM.CustomPollerAssignmentOnNode ONPMC
left join Orion.NPM.CustomPollerAssignmentOnNode ONPMC2 on ONPMC.nodeid =ONPMC2.nodeid and ONPMC2.CustomPollerName = 'customFreeSpace'
WHERE ONPMC.CustomPollerName = 'customFlashSize'

All comments

KMSigma.SWI

What do you want your output to look like? I think this can be done with a simple UNION, but I don't want to make assumptions.

gr576

I would like to have the two custom pollers have their own columns "Flash Size" and "Free Space". I will give UNION a look. Thanks for the help.

KMSigma.SWI

Sorry - I was unclear. Can you provide me a sample table of how you want the data shown? Depending on your needs, there may be easier or harder ways.

gr576

I would like it to be shown like so:

Caption

IP Address

IOS Image

Flash Size

Free Space

192.168.1.253

C1000-UNIVERSALK9-M

244711424

153792512

192.168.1.254

C3640-IK9S-M

244711424

153792512

The two custom pollers are for flashSize and freeSpace, but as of right now I can only add one of them to a column. I have tried using UNION but failed. Instead I get an extra row withe the freeSpace value under the Flash Size column, but no new Free Space column.