This query was requested from a customer who wanted a list of all his assets for his assets groups. This would save him time in inventorying his assets without going to each asset group and generating a report. I thought this would be great to share in community for people who are looking for similar information.
You can also use aggregate functions to obtain a total of assets in your asset group by using the count() function. Try it out and if you encounter any trouble please feel free to post your questions below.
This Query will provide you with:
- Asset ID
- Asset IP Address
- Asset Hostname
- Asset Group Name
SELECT distinct(daga.asset_id), da.ip_address, da.host_name, dag.name AS asset_group FROM fact_asset_group fag JOIN dim_asset_group dag USING (asset_group_id) JOIN dim_asset_group_asset daga USING (asset_group_id) JOIN dim_asset da USING (asset_id) ORDER BY dag.name
As always to know more about our SQL Query Export feature, please check out the introductory blog.