10 Replies Latest reply on Jun 16, 2015 3:48 PM by dhanson

    missing UnDP report?

    rgward

      We have a several UnDPs assigned to all our Cisco nodes, one being MIB OID 1.3.6.1.4.1.9.2.1.2 "'whyReload".  However, at times the NOC team forgets to assign them to new Cisco nodes.  Can anyone assist with a SQL query to list all nodes that do not have a specific UnDP assigned?  We are running NPM 11.0.1.

        • Re: missing UnDP report?
          rgward

          bump...anyone able to help with a query?  I can't get one to work.

            • Re: missing UnDP report?
              d09h

              I recall making a report like this via Report Writer.  Have you tried that?

                • Re: missing UnDP report?
                  dhanson

                  You can try this. Just modify the OID and vendor as needed. This should return the hostnames of nodes that do not have a poller with that OID assigned to them.

                   

                  WITH a AS
                  (
                  SELECT n.NodeID, n.Caption, cp.UniqueName FROM Nodes AS n
                  JOIN CustomPollerAssignment AS cpa ON cpa.NodeID = n.NodeID
                  JOIN CustomPollers as cp ON cp.CustomPollerID = cpa.CustomPollerID
                  WHERE n.Vendor = 'Cisco'
                  AND cp.OID = '<YOUR OID GOES HERE>'
                  )
                  SELECT n.Caption FROM Nodes AS n
                  WHERE n.NodeID NOT IN (SELECT a.NodeID FROM a)
                  AND n.Vendor = 'Cisco'
                  
                  

                   

                  Let me know if it doesn't work, and I can tweak it as necessary.

                   

                  Also, if you want to add it as a custom resource on a summary page, then I'd recommend creating this in Report Writer, and making a custom resource out of the report. SWQL doesn't take kindly to CTE's, so you'd have to figure out how to rewrite this using just JOIN's if you wanted to make it a Custom SWQL resource.

                   

                  -HTH!

                  2 of 2 people found this helpful
                    • Re: missing UnDP report?
                      rgward

                      Sweet!  I created query in Report Writer.  Works great. 

                       

                      The only thing that does not work is the hyperlink for n.Caption (Node) that RW applies to field (/Orion/View.aspx?View=NodeDetails&NetObject=N:${NodeID}).  Any suggestions how to make this work?  It's not a big deal, just the added functionality would be nice to have.

                       

                      Thank you so much for your help dhanson !

                        • Re: missing UnDP report?
                          dhanson

                          If I figure out how to embed links in custom SQL queries, I'll let you know. If you'd like to give it a shot, I'm looking at this article (Node Details URL in custom SQL Report) to hopefully learn how to do it. Right now, I can't seem to figure out what they mean by

                          " add Details Page Link display setting to your caption column." I can't find any options like this in Report Writer, and I'm starting to assume the function is not available in NPM 11.0.1 (I haven't upgraded yet).


                          UPDATE: Figured it out. Go to Report Grouping and add the Nodes column as a report group. If you click "Edit Report Group", you should be able to see the link there. If this doesn't work out of the box, you can edit the query as follows:


                          WITH a AS

                          (

                          SELECT n.NodeID, n.Caption, cp.UniqueName FROM Nodes AS n

                          JOIN CustomPollerAssignment AS cpa ON cpa.NodeID = n.NodeID

                          JOIN CustomPollers as cp ON cp.CustomPollerID = cpa.CustomPollerID

                          WHERE n.Vendor = 'Cisco'

                          AND cp.OID = '<YOUR OID HERE>'

                          )

                          SELECT DISTINCT n.Caption AS 'Nodes', n.NodeID

                          FROM Nodes AS n

                          WHERE n.NodeID NOT IN (SELECT a.NodeID FROM a)

                          AND n.Vendor = 'Cisco'

                           

                          Then under "Field Formatting", set the NodeID field as a Hidden Field.

                           

                          This should populate the list as links to the nodes.

                           

                          This may appear not to work properly in report writer, but check it out on the custom resource you created. If that is blank as well, unhide the NodeID field and try again.