4 Replies Latest reply on Oct 28, 2019 5:39 AM by stuartd

    Report on UDP Output

    stuartd

      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.

       

      example:

       

      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?

        • Re: Report on UDP Output
          stuartd

          Arghh - as ever, sods law dictates I find a solution after I post

           

          For benefit of all:  Success Center [How to create a Web-Based report for a Universal Device Poller (UnDP) on the Orion Platform]

          • Re: Report on UDP Output
            bmacmt

            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%'

            ) why

            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!!!