Assets and corresponding linked assets report

[Joyce]

Hi @Victor

I am trying to create a simple data list report of assets, and their corresponding linked assets.

So we have, for example, a site as an asset, which is then linked to riverbed ( which is also an assets), which in turn linked to a switch.

So I am looking for some kind of a report which could give me a list of sites, and what is linked with them.

Site asset type- 19 ( BAsic)

Linked assets as Network device type assets

Thanks.

Regards,

Joyce

[Guest]

@Joyce If you created a Single list of data report using an Entity and specifying the Asset as the entity, would you not get the desired result? When you do create this entity, under Data Collection you could specify fields site, name, building of the Asset table as shown in the screenshot below. The end result is a report with assets by site. Some sites have more than one asset as shown in the second screenshot.

Thanks

Pamela

[Joyce]

great will try this

[Joyce]

Hi Pamela,

This is only returning the whole list of assets, I need the relationship/ link between the assets. 

Can this be done as a sql schema design, to have that link?

Thanks.

 

Regards

Joyce

 

 

[Joyce]

any update/ help please?

 

[Guest]

@Joyce I see you raised a request regarding this issue. We shall continue from there

Thanks

Pamela

[Victor]

@Joyce : I understand you have a support request raised to assist you with this. For the benefit of all community, I will post the answer here. Basically, the query you need to return that information is this:

SELECT 
  a.h_name, b.h_name FROM h_cmdb_links 
JOIN 
  h_cmdb_assets a ON a.h_asset_urn = h_cmdb_links.h_fk_id_l
JOIN 
  h_cmdb_assets b ON b.h_asset_urn = h_cmdb_links.h_fk_id_r
WHERE 
  a.h_type = 19
ORDER BY h_cmdb_links.h_fk_id_l

You need to transpose this query into a report, something my colleagues can help in the support request you have ongoing. The fields in the statement are just an example, you can add more fields to the report as per your requirements.