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

DirtyDB: How can we find everything on our networks without scanning?

I'll start by saying that I am not a SQL guy, a network guy, or a systems guy.  I'm just a guy that hacked this SQL query together because I was sick of scanning for devices on my network. Please pick at it and try to make something more out of it.

You need NCM AND NPM for this to work.

Custom SWQL Query:


SELECT

NodesCustomProperties.Center AS [Center], REMOVE OR REPLACE THIS WITH WHATEVER YOU USE  FOR LOCATION GROUPINGS

Nodes.NodeCaption AS [Node Name],

Interfaces.InterfaceName AS [Interface Name],

Interfaces.InterfaceAlias AS [Subnet Description],

RoutingTable.RouteDestination AS [Subnet],

RoutingTable.RouteMaskLen AS [CIDR],

ArpTables.IPAddress AS [IP],

ArpTables.MAC AS [MAC],

ArpTables.RDNSLookup AS [DNS Name]

FROM Orion.Routing.RoutingTable

INNER JOIN Cirrus.Nodes ON Nodes.CoreNodeID=RoutingTable.NodeID

INNER JOIN Orion.NodesCustomProperties ON NodesCustomProperties.NodeID=RoutingTable.NodeID

INNER JOIN Cirrus.ArpTables ON ArpTables.InterfaceIndex=RoutingTable.InterfaceIndex AND ArpTables.NodeID=Nodes.NodeID

INNER JOIN Cirrus.Interfaces ON Interfaces.InterfaceIndex=RoutingTable.InterfaceIndex AND Interfaces.NodeID=Nodes.NodeID

WHERE ProtocolName='Local' AND IP_Version<>'6' AND RouteMaskLen<'30'

Search SWQL Query:


SELECT

NodesCustomProperties.Center AS [Center], REMOVE OR REPLACE THIS WITH WHATEVER YOU USE  FOR LOCATION GROUPINGS

Nodes.NodeCaption AS [Node Name],

Interfaces.InterfaceName AS [Interface Name],

Interfaces.InterfaceAlias AS [Subnet Description],

RoutingTable.RouteDestination AS [Subnet],

RoutingTable.RouteMaskLen AS [CIDR],

ArpTables.IPAddress AS [IP],

ArpTables.MAC AS [MAC],

ArpTables.RDNSLookup AS [DNS Name]

FROM Orion.Routing.RoutingTable

INNER JOIN Cirrus.Nodes ON Nodes.CoreNodeID=RoutingTable.NodeID

INNER JOIN Orion.NodesCustomProperties ON NodesCustomProperties.NodeID=RoutingTable.NodeID

INNER JOIN Cirrus.ArpTables ON ArpTables.InterfaceIndex=RoutingTable.InterfaceIndex AND ArpTables.NodeID=Nodes.NodeID

INNER JOIN Cirrus.Interfaces ON Interfaces.InterfaceIndex=RoutingTable.InterfaceIndex AND Interfaces.NodeID=Nodes.NodeID

WHERE ArpTables.IPAddress LIKE '%${SEARCH_STRING}%' OR ArpTables.MAC LIKE '%${SEARCH_STRING}%' OR ArpTables.RDNSLookup LIKE '%${SEARCH_STRING}%'

Get as much network infrastructure you can into NCM inventoried.

I decided to post this as a discussion just because I am unsure of my SQL and did not want to post content that didn't work for the masses. I really didn't have anything to discuss other than improving this.  I figured someone would go to town with it and come up with tons of slick extended things based off this.  I wanted to find a better way to organize the ARP report that NCM has.  I wanted to add my custom center groups.

I've found this to be very helpful and hope others will too.

WOOT CENTER
Vl112
OMGROFL_Data_Vlan_Floor_2
ROUTERWITHTHEARP10.99.2.02410.99.2.2100THEMAC00R-RDNS-00
ROUTERWITHTHEARP10.99.2.02410.99.2.13000THEMACE4R-RDNS-E4
ROUTERWITHTHEARP10.99.2.02410.99.2.1900THEMAC06FR-RDNS-6F
ROUTERWITHTHEARP10.99.2.02410.99.2.2200THEMAC07DS-RDNS-G
Vl114
OMGROFL_Data_Vlan_Floor_4
ROUTERWITHTHEARP10.99.4.02410.99.4.11000THEMAC0A7R-RDNS-A7
Vl115
OMGROFL_Data_Vlan_Floor_5
ROUTERWITHTHEARP10.99.5.02410.99.5.10000THEMAC0DR-RDNS-1D
Vl116
OMGROFL_Data_Vlan_Floor_6
ROUTERWITHTHEARP10.99.6.02410.99.6.1000THEMAC0519R-RDNS-19
Vl117
OMGROFL_Data_Vlan_Floor_7
ROUTERWITHTHEARP10.99.7.02410.99.7.1000THEMACB0AR-RDNS-0A
ROUTERWITHTHEARP10.99.7.02410.99.7.110THEMACF29RN-RDNS-F29
ROUTERWITHTHEARP10.99.7.02410.99.7.21718THEMACB20NC-RDNS-D01
ROUTERWITHTHEARP10.99.7.02410.99.7.1531CCTHEMAC794S-RDNS-

One for all IBM DEvices

SELECT

NodesCustomProperties.Center AS [Center],

Interfaces.InterfaceAlias AS [Subnet Description],

ArpTables.IPAddress AS [IP],

ArpTables.RDNSLookup AS [DNS Name]

FROM Orion.Routing.RoutingTable

INNER JOIN Cirrus.Nodes ON Nodes.CoreNodeID=RoutingTable.NodeID

INNER JOIN Orion.NodesCustomProperties ON NodesCustomProperties.NodeID=RoutingTable.NodeID

INNER JOIN Cirrus.ArpTables ON ArpTables.InterfaceIndex=RoutingTable.InterfaceIndex AND ArpTables.NodeID=Nodes.NodeID

INNER JOIN Cirrus.Interfaces ON Interfaces.InterfaceIndex=RoutingTable.InterfaceIndex AND Interfaces.NodeID=Nodes.NodeID

WHERE

ProtocolName='Local' AND ArpTables.MAC LIKE '000142%' OR

ProtocolName='Local' AND ArpTables.MAC LIKE '000142%' OR

ProtocolName='Local' AND ArpTables.MAC LIKE '40F2E9%' OR

ProtocolName='Local' AND ArpTables.MAC LIKE '98BE94%' OR

ProtocolName='Local' AND ArpTables.MAC LIKE 'A897DC%' OR

ProtocolName='Local' AND ArpTables.MAC LIKE '000255%' OR

ProtocolName='Local' AND ArpTables.MAC LIKE '0004AC%' OR

ProtocolName='Local' AND ArpTables.MAC LIKE '000629%' OR

ProtocolName='Local' AND ArpTables.MAC LIKE '00096B%' OR

ProtocolName='Local' AND ArpTables.MAC LIKE '000D60%' OR

ProtocolName='Local' AND ArpTables.MAC LIKE '001125%' OR

ProtocolName='Local' AND ArpTables.MAC LIKE '00145E%' OR

ProtocolName='Local' AND ArpTables.MAC LIKE '0017EF%' OR

ProtocolName='Local' AND ArpTables.MAC LIKE '0018B1%' OR

ProtocolName='Local' AND ArpTables.MAC LIKE '001A64%' OR

ProtocolName='Local' AND ArpTables.MAC LIKE '002035%' OR

ProtocolName='Local' AND ArpTables.MAC LIKE '00215E%' OR

ProtocolName='Local' AND ArpTables.MAC LIKE '002200%' OR

ProtocolName='Local' AND ArpTables.MAC LIKE '002503%' OR

ProtocolName='Local' AND ArpTables.MAC LIKE '005076%' OR

ProtocolName='Local' AND ArpTables.MAC LIKE '006094%' OR

ProtocolName='Local' AND ArpTables.MAC LIKE '08005A%' OR

ProtocolName='Local' AND ArpTables.MAC LIKE '0817F4%' OR

ProtocolName='Local' AND ArpTables.MAC LIKE '10005A%' OR

ProtocolName='Local' AND ArpTables.MAC LIKE '5CF3FC%' OR

ProtocolName='Local' AND ArpTables.MAC LIKE 'E41F13%' OR

ProtocolName='Local' AND ArpTables.MAC LIKE 'FCCF62%' OR

ProtocolName='Local' AND ArpTables.MAC LIKE '6CAE8B%' OR

ProtocolName='Local' AND ArpTables.MAC LIKE '749975%' OR

ProtocolName='Local' AND ArpTables.MAC LIKE '0010D9%' OR

ProtocolName='Local' AND ArpTables.MAC LIKE '000B62%' OR

ProtocolName='Local' AND ArpTables.MAC LIKE '70B3D5%'

Lets see what you guys build!

Tags (3)
0 Replies