Service Manager - Reporting on Updates

[Lauren]

Hi

We need to be able to report on how many times requests have been updated by a customer via the service portal. Previously, in Supportworks, there was updatedb table which enabled us to report on this.

Is there a similar table in Service Manager we can use for this?

Thanks

Lauren

[Lauren]

Hi @James Ainsworth @Daniel Dekel @Steven Boardman

Sorry to pick on you - I unfortunately need this information urgently, if it is possible.

Are you able to confirm whether we can extract this data, and if so what the table/field is?

Thanks

Lauren

[Steven Boardman]

Hi @Lauren All updates on a request are written to the h_buz_activities table.  You can join the h_itsm_requests table on the activity stream id and the target id from the h_buz_activities table.  What you will then need to do in order to limit the count to only those of the customer, you can filter the results matching the customer id in the request table with the actor_id in the h_buz_activities table.  The user id's are in slightly different formats in these two tables so you will need to use CONCAT to match.

I have attached a simple report example which does the above and prompt for the request id when running the report, so the results returned are a count of the customer updates on that specific request (when joining tables etc and large data sets we need to be mindful of load / performance etc, hence the request prompt), of course you could but in date ranges etc to limit the results returned. 

Hope that helps

Steve

 

customer-updates.report.txt

[chriscorcoran]

@Steven Boardman thanks, do you know which column shows the text of the update?

[Steven Boardman]

@chriscorcoran h_content in the h_buz_activities table 

[chriscorcoran]

@Steven Boardmangot it ta, content Preview, thanks!