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
@holly_wilsey Can we fetch the more than one unique_id ? here source is amazon-web services ?
@vanessa_villalpando @john_hartman
This query should list out each Unique ID, I’m not sure what you’re referring to. Can you provide a little more detail or a screenshot perhaps?
I am trying to fetch the Unique ID for all the assets such as the assets which have been discovered by AWS connectors as well. How can I get the Unique ID of Assets which has been discovered by AWS connectors?
@john_hartman @holly_wilsey @vanessa_villalpando
Any reference - since the assets were discovered by AWS asset sync as type = “ASSET-IMPORT”.
When I fetched the details we are only able to fetch the Unique ID for the assets which were scanned.
@john_hartman @holly_wilsey @vanessa_villalpando
Any luck on the above request?
@john_hartman @holly_wilsey @zacharysmith_zacharysmith @zach_hartman @zyoutz
I don’t have my lab currently hooked up with an AWS instance so I can’t completely test this out. However if I’m not mistaken you’re trying to list out the assets that have also been DISCOVERED by the discovery connection and not just the ones SCANNED by the site. If I’m not mistaken, the UUID would only be provided after a scan as the AWS connector itself does not pass that info.
@john_hartman Assets which are discovered by AWS connector has Amazon web services-instance id which we are trying to fetch. By doing the same we will be able to understand the assets which doesn’t have R7 agents and reference of the instance-id ?