Reporting on the activity log

[nikeng]

Hi,

Lately I've been asked by our users to build reports based on events logged in the activity log of a record.

For example, reports mainly in PA about things like:

•  Numbers of emails (external comments) added to records daily, by breakdowns like business service, user, assignment group etc.
•  Summed incidents sent from groups to group X (for example, how many records have been sent to our desktop support assigntment group from other groups? Which groups is the servicedesk sending their records to if not resolved by the first call?).

•  Number of work notes added to records daily.
•  Number of changes of states in records.
•  Number of records which have been sent to external suppliers daily.

I can certainly see the merits of such reports, especieally in order to track flows of records between groups.

All of these KPI:s and reports are based on the activity log from what I understand. I initially tried creating an indicator from the [sys_history_line] table, but I couldn't find a usable reference field to the main task in that table, making me unable to use my defined breakdowns.

Upon further research I found that running queries against these sys-tables was genereally risky business, and not recommended because of potential system instability. 

So my question is, in a  broad perspective, what is the best strategy for reporting on these facts?

I know the system allows for metrics to be created, but from what i gather those aren't retoractive, and as time goes by I anticipate new user request for different reports and breakdown all based on the activity log. Users want historic reports, not reports spanning from the date they ordered the report. That's why we purchased PA. So what's the best strategy for solving this?

One possible solution I guess is to somehow add a field to the [sys_hisotirc_line] table containing a proper reference to the actual record, or maybe to create this in some cpy of the sys-table.

[Adam Stout · Accepted solution]

I'm not sure that I would report on sys_history_line directly.  My recommendation would be to build metrics and then report on those (with the aid of some views).  If you look at Metric -> Definitions and incident_metric you should see samples of what you want.

[nikeng]

I guess that's the way to go. We'll have to find a way to add the previous value to each row in the Incident Metric table and make our calculations based on that.