Reporting on Timeline Comments

[Martyn Houghton]

We are in the process of enabling visibility of Change Requests on the customer portal (see post link at the bottom),  which will mean our existing change requests which where created before catalog options for changes where in place will be view-able to the customer.

Therefore before making this change I want to review all customer view-able comments on the timeline of the effected requests. I can see from other posts on the forum about reporting on the activities, which I have based my initial query on, but there appears to be a missing table relationship between the itsm_requests and the h_buz_comments.

select h_pk_reference,h_buz_comments.h_comment,h_buz_comments.h_visibility from h_itsm_requests

RIGHT JOIN h_buz_comments

ON h_itsm_requests.h_activity_stream_id = h_buz_comments.h_activity_id where h_pk_reference='IDXCR00076979'

I can link h_itsm_requests to h_buz_activity_streams using the h_activity_stream_id, but do not seem how that would then let me link to the timeline comments in h_buz_comments.

Therefore I must be missing one or more tables relationships which tie the timeline comments to the the request. Can you advise on what I am missing?

Cheers

Martyn

 

[Martyn Houghton]

After a bit more work and walking through the logic, I had forgotten comments exist against a post, which in the table structure are h_buz_activities, so I need to traverse through this table to link to the comments. 

Therefore in order to display the comments I used the following SQL.

select r.h_pk_reference,c.h_comment,c.h_visibility 

from h_itsm_requests r

RIGHT JOIN h_buz_activities a 

ON r.h_activity_stream_id=a.h_target

RIGHT JOIN h_buz_comments c

ON a.h_id = c.h_activity_id 

where r.h_pk_reference='IDXCR00060328'

Hopefully that might help someone else if they have the same requirement to report on comments.

Cheers

Martyn