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
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!
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.
dbo.UDT_Endpoint - (endpointid, macaddress)
dbo.UDT_PortToEndpointCurrent - (portid, endpointid)
dbo.UDT_Port - (portid, portdescription)
dbo.networkinfo - (resourceid, macaddress)
dbo.resourcelocation - (resourceid, location)
x.location = p.portdescription
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
x.resourceid = rl.resourceid
and Isnull(rl.location,0) <> isnull(p.portdescription,0)
SolarWinds solutions are rooted in our deep connection to our user base in the THWACK® online community. More than 150,000 members are here to solve problems, share technology and best practices, and directly contribute to our product development process. Learn more today by joining now.