SQL Query to list sites

I have been working on making sure all assets are in their proper sites. When I use dim_site to display the site name I only get either “Global” or whatever site the asset is in individually. Is there a way to show all sites that a particular asset belongs to?

@c_woolard There are two different ways to approach this. First, you can use the dim_asset table which includes a column for sites. This column will return a comma separated list of site names:

asset_id mac_address ip_address host_name operating_system_id host_type_id match_value sites last_assessed_for_vulnerabilities
13 00:00:00:00:00:00 9 -1 0.159999996 site1,site2 2020-06-16 08:13:29.704

In addition, the dim_site_asset table will include the relationship between assets and sites and it an easy way to get the list of site IDs for each asset as well as joining other information to return with your report.

1 Like

This was exactly what I was looking for. Thank you!

1 Like