cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
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.

Labels (2)
0 Kudos
1 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.

View solution in original post

0 Kudos
10 Replies
Level 14

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

0 Kudos

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

0 Kudos

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!

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

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.

View solution in original post

0 Kudos

Nice another AZ thwacker in the house!

0 Kudos

Represent! Arizona_Represent.jpg

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

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

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

0 Kudos