cancel
Showing results for 
Search instead for 
Did you mean: 
rgward
Level 14

missing UnDP report?

Jump to solution

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.

0 Kudos
10 Replies
rgward
Level 14

Re: missing UnDP report?

Jump to solution

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

0 Kudos
d09h
Level 16

Re: missing UnDP report?

Jump to solution

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

0 Kudos
dhanson
Level 11

Re: missing UnDP report?

Jump to solution

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!

rgward
Level 14

Re: missing UnDP report?

Jump to solution

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 !

0 Kudos
Highlighted
dhanson
Level 11

Re: missing UnDP report?

Jump to solution

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.

0 Kudos
rgward
Level 14

Re: missing UnDP report?

Jump to solution

You're quick. Thanks for the article.  I think the " add Details Page Link display setting to your caption column." is referring to the field WebURL link which links you to the Node Details page.

Before I saw your latest post, I tried adding n.NodeID to the Select (SELECT n.Caption, n.NodeID  FROM Nodes AS n) and it worked.  WebURL was left unchanged.  Now the node is hyperlinked.  I see you included the keyword DISTINCT.  Would it be best to add it too?

0 Kudos
dhanson
Level 11

Re: missing UnDP report?

Jump to solution

Don't think it makes a significant difference, unless you start observing repeats in your query. Otherwise, glad it's working for you. =)

0 Kudos
rgward
Level 14

Re: missing UnDP report?

Jump to solution

I'll go ahead and add it for insurance against possible repeats.  You have been a great help!  Thanks again dhanson

0 Kudos
ecklerwr1
Level 19

Re: missing UnDP report?

Jump to solution

Nice another AZ thwacker in the house!

0 Kudos