I like having the listing of all IPs on my devices, but would like to have a bit more information on them like interface name, up/down status, interface description...
the resource I am referring to is "All IP Addresses on ${Caption}":
Custom query use SWQL and that is similar to SQL. Probably use search in twhack, probably we don't have any guide how to create SWQL queries. If you need all ipadresses you can use same query without Where condition:
SELECT ip.IPAddressType, ip.IPAddress, ip.InterfaceIndex, i.caption as Caption, i.DetailsURL as [_LinkFor_Caption], '/Orion/images/StatusIcons/Small-' + i.StatusIcon AS [_IconFor_Caption]
FROM Orion.NodeIPAddresses as IP
left join Orion.NPM.Interfaces as i on ip.nodeid=i.nodeid and ip.interfaceindex=i.index
This might work:
Build a report with this sql:
select
distinct IPAddress as 'IP ADDRESS',
IPAddressType as 'IP VERSION',
Caption,StatusName,
PhysicalAddress
from
NodeIPAddresses join Interfaces on (NodeIPAddresses.NodeId=Interfaces.NodeId) join StatusInfo on (Interfaces.Status=StatusInfo.StatusId)
where
NodeIPAddresses.nodeid=${NodeId};
and include it as a resource on the page.
Thanks cgregors! I like rig24 have been looking for a way to do this. I tried your sql but I don't think the distinct is working in this case to filter the duplicates because of the Caption column. Here is the result I get.
Sorry, I am completely new to SQL, maybe I am doing it wrong. I pasted your code into the SQL window and got "Query is not valid". Am I in the wrong area?
You can't use a distinct clause there and still get all of the interfaces per device. Try grouping your report by the IPAddress field and see if that looks better for you.
Grouping by IPAddress just shows ip with all (11) configured interface captions under it. The sql query, at present, is associating one ip address with each of the 11 interfaces (captions) on the device. This repeats for each interface ip address (you can see this beginning at address 10.95.32.17 11 captions start to repeat). The objective is to have a report that shows each of the 11 (in my example) captions (interfaces) with the associated interface ip address.
rig24, can you save the web-based report as is? The Query is not valid because of the variable ${NodeId} used in the WHERE clause. If you create the report in Report Writer you will also get an error as well but save the report as is. Then, in the web console Node Details page, add the Report from Report Writer resource, then Edit it and select the report as named. It will then give results.
Yah, I noticed that in the query. I hoped the distinct qualifier would fix it but it didn't.
Hopefully someone with more sql skill can fix the query to return a more unique list. Also it would be good to flag which IP address is being used as the polling address. This would require another join to the nodes table.
Cavaet: All the SQL I learned was written on bathroom walls.
My experience is about the same but through googling.
I was able to make this work and eliminate the dups by changing the JOIN to also match on the interface in the Interfaces table.
JOIN Interfaces ON (NodeIPAddresses.NodeId=Interfaces.NodeId AND NodeIPAddresses.InterfaceIndex=Interfaces.InterfaceIndex)
BUT, this comes with a caveat. The revised JOIN will only select interfaces that are being monitored in NPM. For example, if you use the Loopback address as the node polling ip but do not monitor the loopback interface, this interface will not show even though it is in the NodeIPAddresses table. I haven't been able to figure out a way to write the query to show all addresses in the NodesIPAddresses table when JOINed with the Interfaces table (without the InterfaceIndex condition) without creating duplicate records for those IPAddress(es) that do not exist in the Interfaces table. Still trying to reason this out. Any ideas on how get around this would surely be helpful.
Hello
You can use Custom query resource and add it to Node detail page.
**
join Orion.NPM.Interfaces as i on ip.nodeid=i.nodeid and ip.interfaceindex=i.index
where nodeid=${nodeid}
This whole discussion makes me very happy that there is a community that interacts.
Without the community this wouldn't have happened.
Thanks to all who contributed.!
Copied and pasted the query. Didn't get any results, doesn't show an error either.
Result:
Agreed. This community is one of the best parts of SolarWinds.
Hello,
Are you insert to node detail page?
Do you have interfaces managed by NPM or NOT?
If not you can slight modify it:
Which version of NPM do you have? It is working on my enviroment.
Are you insert to node detail page? Yes
Do you have interfaces managed by NPM or NOT? I did have two interfaces being monitored by NPM, but realized when you asked that, that those serial interfaces don't have IPs (they are part of a multilink interface). Added the multilink to being monitored, and it popped up on custom query.
I guess my next question would be, can I list any other interfaces without them being monitored? I don't necessarily need the current status on it, just the interface name the IP is attached to. Status would just be a bonus.
Also, is there any whitepapers on this site for learning these types of querys? I hate having to ask so many questions.
