Linking Asset table to Request table by Request ID

[Claire B]

Good morning all,

I'm trying to produce a report where you can see a list of requests, with linked/affected asset displayed.

For content, when a user raises a call, they are presented with a number of questions, including linking the assets (recorded on CMDB as being in use by them (Used By Column)) affected relating to the call. This is what I am trying to capture in a report. 

At the moment, I am only able to produce report linking the request table and asset table using "Used By"/"CustomerName" as a relationship, which does not produce the desired result I need e.g. a user has 4 assets against his name, meaning the report will show a call repeating four times in the report, against the name of the 4 assets:

IN00001234 - Hardware>Laptop>Broken - Joe Bloggs - MobilePhone123
IN00001234 - Hardware>Laptop>Broken - Joe Bloggs - Laptop123
IN00001234 - Hardware>Laptop>Broken - Joe Bloggs - USBStick456
IN00001234 - Hardware>Laptop>Broken - Joe Bloggs - Monitor789
IN00001235 - Hardware>Mobile Phone>Broken - Jane Smith - Mobile Phone124
IN00001236 - Hardware>Desktop>Broken - Tom Bond - Desktop890
IN00001236 - Hardware>Desktop>Broken - Tom Bond - MobilePhone125

What I need to see is this:

IN00001234 - Hardware>Laptop>Broken - Joe Bloggs - Laptop123
IN00001235 - Hardware>Mobile Phone>Broken - Jane Smith - MobilePhone124
IN00001236 - Hardware>Desktop>Broken - Tom Bond - Desktop890

Is there a way of linking request table and asset table by Request ID please? I've had a look on the forums to see if anyone had the same issues as me but to no avail.

I also had a look in the Question table, as well as Configuration Item Explorer tables to see if this is captured but sadly it is not there.

Hope someone can help.

Cheers,

Claire.

[Steve Giller]

The relationships between Assets and Requests are stored in the h_cmdb_links table.

The Assets and Requests are stored as URNs, not IDs so you will need to manipulate the data in order to achieve this report.
Please bear in mind that this is not a one-to-one replationship when viewing the results.

[Berto2002]

We needed Expert Services to help with this. It's something like this you need on CMDB links:

and this on the CMDB assets:

our report shows us the list of assets per service but similar principle