Reporting on calls logged to Assets

[Kelvin]

I am trying to write a report/widget to show number of calls logged per week based on the Asset_Type.

e.g. number of PC Faults per week 
       number of Printer Faults per week

To be honest I am not sure where to start with the widget as the asset type would not be stored in the incident record.

What I think I need to know is the link field name between an incident and an asset.

[Guest Chaz]

@Kelvin the entity that links assets and requests is called AssetsLinks, depending on the direction that the link was created, the link will be in one of the following two columns:

h_fk_id_l

h_fk_id_r

 

[Kelvin]

Can I follow up on this and ask what tables I need to link so that I can report on a call and the asset details.

I can see that I Will need

h_itsm_requests

h_cmdb_links

h_cmdb_assets

but not sure how to link them together.

[Guest]

Hi @Kelvin,

My apologies in the delay in replying to you here. Looking at the report you would like to run, I believe you will need the following 4 tables:

h_itsm_requests - This contains the requests (linked on h_pk_reference)
h_cmdb_links - This contains the links between the requests (linked on h_fk_id_l) and the Assets (linked on h_fk_id_r)
h_cmdb_assets - This contains the assets (linked to the requests via h_pk_asset_ID) (Linked to the type via h_type)
h_cmdb_asset_types - This contains the asset type name (linked via h_pk_type_id)

Its becomes a little more complex because what you will notice is that the data in the attributes in the table h_cmdb_links is actually contained in a string that we call a URN. In order to be able to perform the joins on these attributes, we need to perform a concatenation so that the references from other tables match the URN. 

Below I have some screen shots of the set up of the joins:

 

 

I have not added any filtering to this, and simply have picked the 3 simple columns to show me every asset linked against every request, and the asset type. 

Please be aware - the preview may look incorrect in that data is repeated. This is a bug we are aware of - when you actually run the report it should show correctly:

I have actually exported the definition and will mail it to you - so you can import this onto you instance and have a look at what I have performed and tailor it to what you need.

I hope this helps!

Bob