report with Interface's IP Addresses

when you search for IP there should be several variations of IP available, I believe the one that reads like this "IPAddresses" is the one you want

This should be the variable

${N=SwisEntity;M=Node.IP_Address}

Hi raymond.chen ,

I'm assuming that you're looking for something that gets you data like this:

In the screenshot you'll see that I used the "WHERE NodeID = ####" to limit the results to a single node.  You could limit by other details, or not use the WHERE clause at all to get all IP/MAC address on all nodes.  You'll also see that this is giving interfaces which don't have a MAC address.  This will also exclude any interfaces which do not have an IP address configured.

I did this in SWQL studio, If you want to do this in a regular webreport, you can probably just look for the column names, or use this query in a "Custom Table" and "Advanced Database Query (SQL,SWQL)" configuration.

If this is what you were looking for, here's the query I used:

SELECT
    N.Caption,
    N.Vendor,
    N.MachineType,
    NIPA.NodeID,
    NIPA.IPAddress,
    NPMI.PhysicalAddress,
    NPMI.Name,
    NIPA.InterfaceIndex,
    NIPA.SubnetMask
FROM Orion.NodeIPAddresses NIPA
INNER JOIN Orion.NPM.Interfaces NPMI
ON NIPA.NodeID=NPMI.NodeID AND NIPA.InterfaceIndex=NPMI.InterfaceIndex
INNER JOIN Orion.Nodes N
ON NIPA.NodeID=N.NodeID
WHERE NIPA.NodeID = 1551

 It might be possible to get the MAC addresses of interfaces which don't have an IP Address assigned.  I would need to try to reorder the query and see if that changes anything.

Hi raymond.chen ,

Are these the types of results you're looking for?

I want to select interface ip instead of node ip.

Yes.

Can be configured on the web interface

THX.

Hi raymond.chen,

Sorry for the long delay. Yes, this can be executed in the Web-based report. You'll need to use custom SWQL.

Here are the steps:
1. Write a query. The columns listed in the 'SELECT' statement will be what is available in the report. (examples at the end)

2. Create a 'Custom Table' type Report.

3. Change the 'Selection method' to 'Advanced Database Query (SQL, SWQL)' and paste your query into the query box

4. Add columns

NOTE: if you include certain columns, sometimes you need to remove a 'Display setting', like this:

5. Rearrange or change display names of columns as appropriate.

6. Finish the report as normal.

Example queries:

SELECT
    N.Caption,
    N.IP_Address AS [Node IP],
    N.Vendor,
    N.MachineType,
    NIPA.IPAddress,
    NPMI.PhysicalAddress,
    NPMI.Name AS [Interface Name],
    NIPA.SubnetMask
FROM Orion.NodeIPAddresses NIPA
INNER JOIN Orion.NPM.Interfaces NPMI
ON NIPA.NodeID = NPMI.NodeID AND NIPA.InterfaceIndex = NPMI.InterfaceIndex
INNER JOIN Orion.Nodes N
ON NIPA.NodeID = N.NodeID
ORDER BY N.Caption ASC, NIPA.InterfaceIndex ASC

This example includes custom properties:

SELECT
    N.Caption,
    N.IP_Address AS [Node IP],
    NCP.Comments,
    N.Vendor,
    N.MachineType,
    NIPA.IPAddress,
    NPMI.PhysicalAddress,
    NPMI.Name AS [Interface Name],
    NIPA.SubnetMask
FROM Orion.NodeIPAddresses NIPA
INNER JOIN Orion.NPM.Interfaces NPMI
ON NIPA.NodeID = NPMI.NodeID AND NIPA.InterfaceIndex = NPMI.InterfaceIndex
INNER JOIN Orion.Nodes N
ON NIPA.NodeID = N.NodeID
INNER JOIN Orion.NodesCustomProperties NCP
ON N.NodeID = NCP.NodeID
ORDER BY N.Caption ASC, NIPA.InterfaceIndex ASC

Useful

Useful

Finding a need for almost this exact report. However what if I only wanted to pull from a specific interface on every device in my environment. Say for a cisco router just the Gi0/0/0 interface, but also the sub interfaces that could be on that so Gi0/0/0.5. I guess what I am asking is there a way to add a filter to this script. or if i want to filter by a certain device name or something like that. 

do you also know if its possible to query the static routes that might be setup on a router with a similar report? 

I am just learning to use orion and sql/swql so I apologize if this is really easy and something i should know. Do you have any recommendations on courses or training for creating these type of scripts and reports?