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

Live Database containing MAC address port locations (Solarwinds?)

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!!

0 Kudos
2 Replies

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!

Level 7

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)