cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 12

Need to create a report that lists all IPs on every device but can't find resource?

Jump to solution

I want to create a report that'll list all IP addresses on a node but can't seem to find the resource I'm looking for in the web based report builder. Here's the resource I'd like to include in my report...

allip.png

I want to create a list like so...

Node name               All IP's

Node 1                    10.xxx.xxx.xxx

                              10.xxx.xxx.xxx

Node 2                    10.xxx.xxx.xxx

                              10.xxx.xxx.xxx

So I know Solarwind's has the data I need (for cisco nodes anyway) without me having to set up UnDP and report on that but I can't find it when using the report builder, web based or even on the server? Am I missing something? BTW I have a UnDP set up to find all IPs configured on a cisco device it's polling an OID called  ipAdEntAddr 1.3.6.1.2.1.4.20.1.1 but this has a deprecated status. Is this the OID that solarwinds is polling for the above screen shot? Anybody know if this OID has been replaced by something? I know deprecated doesn't mean obsolete but if Cisco were to deprecate it wouldn't they provide a replacement? I don't know how to search for an OID's replacement

1 Solution

noobes

and here is a simple SWQL query that just lists all nodes, with all of their known IPs:

SELECT n.caption, ni.NodeID, ni.IPAddress

FROM Orion.Nodes n

join Orion.NodeIPAddresses ni ON n.nodeid=ni.nodeid

order by ni.nodeid asc

Hopefully one of these will set you in the correct direction.

-Will

View solution in original post

10 Replies

noobes

and here is a simple SWQL query that just lists all nodes, with all of their known IPs:

SELECT n.caption, ni.NodeID, ni.IPAddress

FROM Orion.Nodes n

join Orion.NodeIPAddresses ni ON n.nodeid=ni.nodeid

order by ni.nodeid asc

Hopefully one of these will set you in the correct direction.

-Will

View solution in original post

Thanks again Will appreciate your efforts once more. Yes running that SWQL query got me what I was after, so is that the only way I'd be able to pull that info? It doesn't look like there's an 'all IP's on device' object I can poll for each node on the report? Is there anyway to find the OID Solarwinds polls to get that info?

0 Kudos

noobes

No problem, and I am glad you were able to get what you were looking for.

In regards to the OIDs SolarWinds polls, I would presume they are using the ${SysObjectID} as the OID to poll each device.

Maybe they are specifically looking at the "IP-MIB" MIB table (snmpwalk results from this seem to be matching the results they have stored in the .[NodeIPAddresses] table of the database), but I do not know for sure how they do all of that magic.

Hopefully someone else can chime in here and answer that for you.

Thank you,

-Will

0 Kudos

Brilliant Will, thanks for such a thorough and informative answer! I'd mark it as correct but I'm worried that'll somehow close out the discussion so I'll leave it open in case someone else chimes in and I'll come back to close it. Thanks again

0 Kudos

noobes

Marking an answer/discussion as correct will simply provide a way for others to find a working/correct solution a bit easier. Marking an answer as correct will NOT end/close the discussion.

In regards to having someone else chime in, you will have a better chance when keeping your thread/post active.

As far as I know, the more views and responses this post get, the more it will show up on the main screen. Being on the main screen will provide better visibility to your topic.

Also, if all else fails, and you have not received an acceptable answer, you can always bug rob.hock‌. He is probably just slacking off, paint clouds or something.

Another thing that could help you is a feature request.

If you find something that you need, but does not yet exist, you should create a feature request and then go sell it to Thwack Nation, and get people to vote it up.

-Will

noobes​, This is awesome!

I found this thread while looking for a way to add a 'search for IP address' widget.  And no, not just the polling IP, any interface IP address, and a bonus win would be to to return the interface if the IP searched for was in the IP/Mask of the interface.

0 Kudos

noobes

This is the SWQL query used by the resource itself:

SELECT

SUB.[IPAddressType],

SUB.[IP_Address2]

FROM

  (

  SELECT

  IP_Address AS [IP_Address2],

  IPAddressType,

  1 AS IPOrder

  FROM ORION.Nodes

  WHERE

  NodeID = ${NodeID}

  UNION

  (

  SELECT

  ni.IPAddress AS [IP_Address2],

  ni.IPAddressType,

  2 AS IPOrder

  FROM ORION.NodeIPAddresses ni

  JOIN ORION.Nodes n ON ni.NodeID = n.NodeID AND n.IP_Address != ni.IPAddress

  WHERE

  ni.NodeID = ${NodeID}

  )

  )

SUB ORDER BY SUB.[IPOrder]

Thanks for the reply! How did you get your hands on the SWQL query used? My question however was how could I include the 'All IPs' in an exportable report? Any ideas. Again I appreciate your input, thanks@wluther

0 Kudos

noobes

You should be able to go to manage reports, create new report, and then choose the "Custom Table" report type.

SELECT n.caption, ni.NodeID, ni.IPAddress

FROM Orion.Nodes n

JOIN Orion.NodeIPAddresses ni ON n.nodeid=ni.nodeid

Paste the SWQL query above, into the code box.

npm_report_custom_table-003.PNG

Then you can sort them however you would like.

The settings below should make the report look like:

Node1 name

     IP1
     IP2

     IP3

Node2 name
     IP1

     IP2

Node3 name
     IP1
...and so on, and so on...

npm_report_custom_table-002.PNG

Let me know if this gives you what you are looking for, please.

Thank you,

-Will

noobes

Look for the resource's name in the Resources table of the db.

/****** Script for SelectTopNRows command from SSMS  ******/

SELECT TOP 1000 [ResourceID]

      ,[ViewID]

      ,[ViewColumn]

      ,[Position]

      ,[ResourceName]

      ,[ResourceFile]

      ,[ResourceTitle]

      ,[ResourceSubTitle]

  FROM [SWNPMDB].[dbo].[Resources]

  --where ViewID=5317

  where ResourceTitle like '%All IP Addresses on ${Caption}%'

The results will probably look something like this:

ResourceIDViewIDViewColumnPositionResourceNameResourceFileResourceTitleResourceSubTitle
85218Node IP Addresses/Orion/NetPerfMon/Resources/NodeDetails/NodeIPAddresses.ascxAll IP Addresses on ${Caption}
196323613Node IP Addresses/Orion/NetPerfMon/Resources/NodeDetails/NodeIPAddresses.ascxAll IP Addresses on ${Caption}
507362813Node IP Addresses/Orion/NetPerfMon/Resources/NodeDetails/NodeIPAddresses.ascxAll IP Addresses on ${Caption}

Then, you need to open the ResourceFile, and you should be able to piece together what you need from that.

The contents of the file are shown below: (this actually comes from the "NodeIPAddresses.template" file, which is 1 of 4 files that correspond to this resource)

<?xml version="1.0" encoding="utf-8" ?>

<resourceTemplate>

  <path>NodeIPAddresses.ascx</path>

  <title>@{R=Core.Strings;K=XMLDATA_TP0_4;E=xml}</title>

  <property name="SWQL">SELECT SUB.[IPAddressType], SUB.[IP_Address2] FROM (SELECT IP_Address AS [IP_Address2], IPAddressType, 1 AS IPOrder FROM ORION.Nodes WHERE NodeID = ${NodeID} UNION (SELECT ni.IPAddress AS [IP_Address2], ni.IPAddressType, 2 AS IPOrder FROM ORION.NodeIPAddresses ni JOIN ORION.Nodes n ON ni.NodeID = n.NodeID AND n.IP_Address != ni.IPAddress WHERE ni.NodeID = ${NodeID})) SUB ORDER BY SUB.[IPOrder]</property>

  <property name="MaxRecords">5</property>

  <property name="Edit">true</property>

</resourceTemplate>

They are not always this straightforward, however, sometimes you get lucky and they just give you the answer. These are my favorite.

-Will