Reporting Query - Assets Linked to Requests

[Stephen.whittle]

I would like to search for potential duplicate requests by running a report in Hornbill using the CSV to pivot the data and review requests linked to an asset. To do this I need to include the table that linked assets reside. I cannot locate it using database direct, can anyone help? 

 

I currently have requests table linked and would like to table join the associated linked asset to a request. 

[Stephen.whittle]

@Victor (Offline)Are you able to help with this at all please? You're the reporting guru! 

[Hornbill Staff DR]

Hi Stephen,

 thanks for your post.

Assets (stored in h_cmdb_assets) are linked to requests (stored in h_itsm_requests) via a link table (h_cmdb_links) so we'll be working with these three tables.

If you inspect h_cmdb_links, this table contains several columns, two of which are storing the objects being linked. This may be an asset, request, or service for example. Rather than a pure ID, you'll notice that the objects are referenced by what we call a Uniform Resource Name (URN) e.g. urn:sys:entity:com.hornbill.servicemanager:Asset:h_pk_asset_id. I won't got into the details as to why that is, but you'll see this standard used in various places throughout our applications.

While a URN maybe great for technical purposes, it does make reporting a bit more interesting, especially when there isn't a corresponding URN to make an obvious table join. 

You'll find that every request has a URN stored in h_itsm_requests.h_social_object_ref, which means joining h_cmdb_links with h_itsm_requests is easy, but we also need to join h_cmdb_links to h_cmdb_assets....and there isn't a URN stored for an asset in h_cmdb_assets...

This situation calls for the SQL "CONCAT" function. This allows us to concatenate two values into one single value which we can use to serve our needs i.e. make the necessary JOIN between the link table and asset table. In this case we would be constructing the value using the combination of a string of text (urn:sys:entity:com.hornbill.servicemanager:Asset:), and the value of a database column (h_cmdb_assets.h_pk_asset_id). So when using the function CONCAT('urn:sys:entity:com.hornbill.servicemanager:Asset:',h_cmdb_assets.h_pk_asset_id) the output would equate to a value such as "urn:sys:entity:com.hornbill.servicemanager:Asset:assetID" which is equivalent to what is stored in one of the link columns in h_cmdb_links.

This can be used in the manner shown below, by joining the tables using some custom criteria.

I've also uploaded a report definition so you can examine it for yourself.

I hope that helps!

Dan

assets-with-active-requests.report.txt

[Stephen.whittle]

Thanks that makes complete sense and I have followed the steps on an existing "active requests" report but it returns no values. I have also created a new report using your definition file but that also returns no values. I know we have lots of requests with associated assets that have been linked to the request so I am not sure why they would not display. 

[Hornbill Staff DR]

Hi All,

 since I posted my original explanation above (which included details on how to join the table h_cmdb_links with h_cmdb_assets using a SQL CONCAT function) there has been a change to h_cmdb_assets to include a column to hold the asset URN. This is called h_asset_urn.

This makes writing the above report much easier in that we can just select the columns from each table, without needing to use the CONCAT function in the custom criteria box.

The better configuration is shown below:

Thanks,
Dan