I'm trying to run a report on a subset of devices in our network. The system names of these devices start with 'AAA' or 'BBB'. There are several pieces of information that I'm trying to pull from these devices to a table and I'm not having much luck with it. Here they are:
- Caption or System name
- All IP addresses on the devices
- Subnet mask for above IPs
- interface alias that above IPs are assigned to
Bare minimum, I would like to pull the first 3 pieces of information listed above. Here is what I've found and got working thus far pulling only the first two pieces of information:
SELECT
n.caption AS [DEVICE NAME],
ni.IPAddress AS [IP Addresses]
FROM Orion.Nodes n
JOIN Orion.NodeIPAddresses ni ON n.nodeid=ni.nodeid
WHERE SysName LIKE 'AAA%' OR Sysname LIKE 'BBB%'
However, subsequent attempts to pull more info have not been successful. Here is how I've edited it:
SELECT
n.sysname AS [Name]
, ni.IPAddress AS [IP Addresses]
, ni.SubnetMask AS [Subnet]
FROM Orion.Nodes n,
Orion.NodeIPAddresses ni
JOIN Orion.NodeIPAddresses ON n.nodeid=ni.nodeid
WHERE SysName LIKE 'AAA%' OR Sysname LIKE 'BBB%'
I add the Name, IP Addresses, and Subnet to the table, group the results by Name and I get a bunch of garbage when I do a preview. For example, I'll get a single device name with a ton of IP addresses below it and subnet masks beside the IPs. Can someone tell me what I'm doing wrong?
