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

marktait
Giga Contributor

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

 

 

 

 

 

1 ACCEPTED SOLUTION

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

8 REPLIES 8

Andreas Borell
Kilo Guru

Have you first analyzed the reassignment count on your incidents? That OotB counter is on every incident (you find it easily using the built in reporting looking at incident table).  What is a high number of re-assignments is contextual; a low number is usually desirable, but some high numbers are typically allowable and can even be natural for some services - it depends on your organizational setup and issue complexity. We track and monitor the assignment counts over time, and mine the data for patterns only if there is a growth of high re-assignment counts.

marktait
Giga Contributor

Hi Andreas - thank you for your suggestion too.

 

I'm aware of the re-assignment count - but it's the number of times tickets go through different teams that I'm trying to measure.

 

So for example, 100 tickets may be assigned to "Team A" initially, then get assigned on to "Team B" or "Team C" depending on investigations, then ultimately assigned on to "Team D".

 

So if I look at the tickets when they are closed, it looks like "Team D" have done all the work - with nothing credited to "Team A, B or C".

 

The re-assignment count in that case, wouldn't help quantify the workload of the first three teams (A,B,C).

 

Without the audit history, I can't tell that Team B may have also touched 90 of the 100 tickets, and Team C maybe only touched 20. So it's a challenge to know which team to provide more staff resources to.

 

The only other way I can see of measuring that is to take very regular snapshots that may capture tickets when they are assigned to various different teams throughout their lifecycle.

 

If there's any other way of achieving this, I'd appreciate any suggestions.

 

All the best, Mark

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.

 

 

Alec Hanson
Tera Guru

Hi, @marktait as Andreas has said you might find the OOTB re-assignment count sufficient, but that might not give you the granularity if you are looking for counts per Group.

Instead you could look at the Metric Definitions, where you can track the change in field values and then produce a report against the data to get your count per group.

Alternatively using Performance Analytics might be another way.

Alec