Reporting on "time since last comment added" or "tickets with no comments"

[Chris W1]

Hi All,

Has anyone had any success with this in any form? I'm up to 3 separate leaders asking for some form of a report with "number of tickets with no comment added" or "number of tickets with no comment for (time period)."

I understand the limitations, given that comments (and work notes) are stored on the journal entries table.

Has anyone explored creating a database view joining task and journal entry tables?

Is there anything in PA that could cover this? Number of incidents not updated for 5 days is an indicator, but isn't exactly what i need as updates could be things other than comments 

Appreciate any insights.

Thanks

[Adam Stout · Accepted solution]

Take a read through this:  https://community.servicenow.com/community?id=community_blog&sys_id=37c045cadbcf409023f4a345ca961921

This was also discussed at an Office Hours session in February:  https://community.servicenow.com/community?id=community_blog&sys_id=0740de96dbcf889023f4a345ca96195d

[Chris W1]

Hi Adam,

Thanks for the info. 

Can I check I am understanding how to take the scenario in your first link and apply it to my scenario:

1. Instead of the project table use the task table
2. Instead of the "latest status report" custom field use a "latest additional comment" custom field
3. Apply the same logic in the Business Rule, but adjusting for 1 and 2 above
4. Use the "latest additional comment" custom field to report tickets with no comment, time since last comment, etc.

Thanks

[Adam Stout]

That sounds about right.  I may put the fields on incident instead of task, but that depends on if you want to use these on many other task extensions. 

This will allow you to put in some logic about what qualifies as an additional comment. For instance, if the customer puts a comment in "Any update" every day, do you want that to show as have a recent comment or so you only track the latest comment from the assigned to user (or at least not the caller).

[Chris W1]

Thanks Adam!