Reporting on requests without a linked asset ID

[AlexOnTheHill]

Do you happen to know whether it is possible to report on requests that have been logged that do not have a linked asset ID?

I know how to report to see which asset IDs are most frequently used however we also need to report on requests missing the linked asset so we know whether training is required or if the asset needs to be created.

 

Many thanks

[Steve Giller]

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

I'm sure this is possible, although frankly beyond my SQL skillset, but you'd need to convert the Request Reference into a URN and then find which Requests did not have an entry in that table - I know how to do the opposite, but for this you're finding something that isn't there, which is a bit more of a challenge.

 

[Steve Giller]

@AlexOnTheHill You might want to try this as a starting point, it may need tweaking to exactly achieve what you need.

I can't take the credit, a couple of our Developers built it, but it's daylight in their bit of the World so they wouldn't come outside!

requests-with-no-linked-assets.report_2.txt

[AlexOnTheHill]

This is brilliant, thank you!

I'm tweaking the report as I speak and it looks exactly what I was looking for.

It seems the answer was in the join all along