A colleague has created a Universal Device Poller to extract all serial numbers from a node (system, PSU, chassis, etc) and then display the output on to a specific tab in the node view. This is very crude as the output lists all hardware inventory (all ports, fans, etc) as well asthe required bits and makes the output mostly empty of any data.
What I'd now like to do is extract only the entries that have serial numbers into a report. But if all I can manage is to extarct it all, blanks included, then so be it. I won't be able to do this just by selcting aspects within the GUI - well not that I can see as I can't see anyway to say use the UDP or its output.
So, my best guess is that I will need to (within the report editor)
-- add content
-- add a 'custom table'
-- create new object selection
-- select 'adevanced database query'
and then craft a SQL / SWQL statement to interrogate and extract said info - but this is where I come unstuck as my SQL/SWQL fu is very weak.
Can anybody guide me?
Solved! Go to Solution.
Not sure if this will help as I figured it out with the sql..
I loaded a poller and changed then names on the fields pulled and then used this sql to get the unique elements I was interested in.
select * from (
select n.sysname, n.caption, n.ip_address, cp.uniquename xx, cps.rowid unit, cps.status
from nodes n
inner join custompollerassignment cpa on cpa.nodeid=n.nodeid
inner join custompollerstatus cps on cps.custompollerassignmentid = cpa.custompollerassignmentid
inner join custompollers cp on cp.custompollerid = cpa.custompollerid
where n.caption like 'gf%'
pivot( max(status) for xx in ( [serialnumber],[model],[iosversion]) ) as crap
where unit > 0
that actual value for each element is in the cps.status field and the cp.uniquename is the name I assigned in the poller.
I could have used the existing value but it was longer than I wanted.
This did the joins to the custom poller information and created one line (in my case) for each switch that matched the
criteria. Pivot command is way way cool!!!
Ugh - pivot just makes me think of Excel and I don't understand it there either
I really need to find time to start digging in to and learning SWQL / SQL
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. Learn more today by joining now.