This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.

You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Live Database containing MAC address port locations (Solarwinds?)

glenh8 over 4 years ago

Hi Everyone,

I have set myself a side project for this year, which is to create an automated asset register.

Basically I want to know the physical locations of all my client devices, in a completely automated way. My thinking is something along the lines of this:

(I already know every outlet in each office and where it cables back to, and in which switch port)

1. Use Solarwinds (device tracker?) to interegate the switches to see which MAC address is connected to which switch port

2. Write the above into an SQL database (or if present, simply read it out from the original SW DB)

3. Write some code to read the data out of the database, run some logic to understand (based on the switch port) which physical location the machine is in

4. Search the manageengine servicedesk SQL database for the MAC address (we use this for assets)

5. Write data from 3. into the manageengine servicedeck SQL database

Thanks!!

Top Replies

0 nickzourdos over 4 years ago

I think Custom Properties would be a good bet, but it'd be tricky. You can't assign Custom Properties to a Port in UDT, but you can assign them to Interfaces in Orion. You could give your Interfaces a Custom Property with a description for a physical location and use some clever SWQL to pull that value from the corresponding UDT Port. I've got the SWQL for matching an Interface to a Port in a script somewhere, but I'm out of the office without access to it until the middle of the week. Ping me in a few days and I'll pull some more details for you!
Cancel
Vote Up +1 Vote Down

Cancel
0 glenh8 over 4 years ago in reply to nickzourdos

Thanks for the reply
I spent a few hours looking into this last week and was able to find all the required data within the SQL tables. Made a query which can do it.
Solarwinds
dbo.UDT_Endpoint - (endpointid, macaddress)
dbo.UDT_PortToEndpointCurrent - (portid, endpointid)
dbo.UDT_Port - (portid, portdescription)
Manageengine
dbo.networkinfo - (resourceid, macaddress)
dbo.resourcelocation - (resourceid, location)
Query
UPDATE
sqlserver2.servicedesk.dbo.resourcelocation x
SET
x.location = p.portdescription
FROM
sqlserver2.servicedesk.dbo.resourcelocation rl
INNER JOIN sqlserver2.servicedesk.dbo.networkinfo ni
ON cast(rl.resourceid as varchar) = cast(ni.workstationid as varchar)
INNER JOIN sqlserver1.solar_orion.dbo.UDT_Endpoint ep
ON replace(ni.macaddress,':','') = ep.macaddress
INNER JOIN sqlserver1.solar_orion.dbo.UDT_PortToEndpointCurrent epc
ON ep.endpointid = epc.endpointid
INNER JOIN sqlserver1.solar_orion.dbo.UDT_Port p
ON epc.portid = p.portid
WHERE
x.resourceid = rl.resourceid
and Isnull(rl.location,0) <> isnull(p.portdescription,0)
Cancel
Vote Up +3 Vote Down

Cancel