Report for SQL UUID

I cant figure out the dim_asset_unique_id

Trying to make simple report that pulls cisco unique identifiers within rapid7 just inventory list. using my sites as inventory to pull from…

Is there anything in particular you’re trying to figure out with this table? There’s an entry for the dim_asset_unique_id table here. The unique_id field might be what you’re looking for if you’re trying to get the actual identifiers. The source field would be something like “amazon-web-services.”

Yes, Since Cisco devices dont pull hostname only ip in addition no hostname due to the lack of the show command of “show hostname” not being available. I had director wanting an inventory of all the cisco switches we had in rapid7 being scanned. She was trying to do comparision of what was in vs what may have been left out. To make it easy on her as she only had the hostnames and uuid available to her i was going to generate a report of everything with uuid in that specific site for switches, but i wasnt able to get query to work with anything to pull the uuid. The dim_asset _unique id is what i was trying to get but i was not having any luck pulling it my validation keeps coming back with error using this query

SELECT ds.name AS site_name, da.ip_address, da.host_name, da.unique_id, da.mac_address, dos.description AS operating_system, dht.description, dos.asset_type, dos.cpe
FROM dim_asset da
JOIN dim_operating_system dos USING (operating_system_id)
JOIN dim_host_type dht USING (host_type_id)
JOIN dim_site_asset dsa USING (asset_id)
JOIN dim_asset_unique_id USING (unique_id) <------But this gives an error saying something about a left clause using “USING” ex: Error: column “unique_id” specified in USING clause does not exist in left table

JOIN dim_site ds USING (site_id)

Double checking the dim_asset table, it looks like there is no unique_id field there. In this case I believe you’d have to pull it from dim_asset_unique_id, and update how you’re joining to that table. I made some minor updates to your query:

SELECT ds.name AS site_name, da.ip_address, da.host_name, daui.unique_id, 
da.mac_address, dos.description AS operating_system, dht.description, 
dos.asset_type, dos.cpe
FROM dim_asset da
JOIN dim_operating_system dos USING (operating_system_id)
JOIN dim_host_type dht USING (host_type_id)
JOIN dim_site_asset dsa USING (asset_id)
JOIN dim_asset_unique_id daui USING (asset_id)
JOIN dim_site ds USING (site_id)

Thank you so much Holly this is amazing just what i needed!

1 Like