sys_history_line - reporting on activity updates - eg. Field Changes, Email Sent etc

marktait · ‎11-27-2023

Running the latest version of ServiceNow (Utah I think) - when looking at an "incident" ticket, I can see a list of "Activities", eg:

Field Changes
Email Sent
Additional Comments
...

Is there any way of me reporting against the table that holds that data?

ie. Reports - Create New -> Source Type: Table -> Table: (sys_history_line - is not appearing in the Table dropdown).

Has the name of this table changed in the most recent SNOW, or is the data held elsewhere?

Essentially, our tickets may have been assigned and re-assigned to a number of assignment groups, and I want to be able to report on how many times tickets have gone through specific assignment groups, not necessarily the one the ticket is currently assigned to, or closed by.

Any help would be really appreciated,

Thanks, Mark

Andreas Borell · ‎11-28-2023

First of all, unless you are capturing time spent in a relevant way, how an issue moves around between groups does not reflect effort spent. An issue can sit with a team for days and then moved, but they only spent 1 minute. This should always be remembered.

But assuming you have identified some threshold and boundaries using the re-assignment count and still want to dig deeper, one way to do it is a report using metrics. Create a report like this

Table name: incident_metric

Type: List

Configure: Columns (ID, number, value, assignment group, start, end)

Group by : ID

Filter conditions :

"Definition" "is" "assignment group"

"Reassignment count" "greater than" "your threshold (3/4/5...)"

Save and run the report.

Now sort the "start" column in ascending order.

The result should be a report where you can follow for each incident how they have travelled through. Use relevant filters to narrow your analysis further - like limiting only to incidents where the assignment group is one that closes specific types of issues you are interested in. Often this kind of analysis requires recursive reasoning and analysis, and patterns are not clear. Going from group a->b->c->d might be an issue for incidents of archetype X, but f->g->h->i may be natural way of working for archetype Y. Multivariate testing is not always straightforward.

View solution in original post

Diego Peukert · ‎11-27-2023

Hello Mark,

Generally, system tables are very heavy and by default are not allowed for reporting, with the exception of a few.

However, you can include these tables in the (sys_properties.list) property and edit the glide.ui.permitted_tables property by adding the sys_history_line table, which should work.

s4scott · ‎11-27-2023

Reporting on system tables is not allowed because it can cause significant performance problems, even on instances with modest usage. It is not recommended.
Some alternatives ton consider:
SLA data is very useful for reassignment counts (if you just need a count, that is built into Incident) and the group data, and has the added benefit of being able to track SLAs
Consider creating a business rule to capture key events to a table specifically for reporting on Incidents like emails or field updates. The sys_history table contains the activity history for EVERY record in the system.

Whatever you select, ensure it is a solution that is right sized to the volume of usage for your instance.

marktait · ‎11-27-2023

Thank you - it is only the count (or throughput) for a particular assignment group I'm looking for, rather than an overall count.

I'm not certain how to create business rules, or creating tables just for my purposes - but I'll look more into that.

My workaround at the moment, is to capture a snapshot of records, where the assignment_group is the queue I'm interested in, and then exporting that (only a max of around 100 records - so no real overhead) - and then looking for unique SNOW reference numbers. It's not ideal, but hopefully your suggestion, and/or Diego's will ring a bell with the DEVs.

All the best, Mark

marktait · ‎11-27-2023

Hi Deigo - thank you for your suggestion.

I'm an end-user of SNOW - rather than a SNOW developer, so I'm not certain where to look for sys_properties.list or glide.ui.permitted_tables - I was hoping it would be a simple export (where I could narrow down the filter to just include the assignment group I was interested in).

I'll pass your suggestion to the SNOW devs though and see if it's something they may be able to do for me.

Thanks again,

Mark