PA dashboard for itil user activity?

[Rick__M]

Can anyone offer some guidance on how to create a report or dashboard showing daily activity for each user with an itil role broken down by hour? Activity would include inserting/updating/viewing task records. The goal is a report highlighting busiest hours of the day and which user(s) have been most active.

I've looked at the Usage Overview report but that shows activity by application. Also tried generating a report from sysevent but that shows username as opposed to a friendly first/last name and I was not able to break down by hour.

[Adam Stout]

There may be other issues to resolve, but if you have the data you want and just need to translate the username to a nicer name, there is an easy solution for that.   It is also an issue if you need to breakdown by Updated By or Created By.

You have two options:

1) Create a view between your fact table and sys_user joined on username.

2) Create a script to query sys_user by username and use a scripted breakdown mapping.

As for the hour of day, create a bucket group with 24 hour buckets.   Create a script to get the hour of day from the time log.   Use this in a scripted breakdown mapping.

[Josh Cooper]

You can also report against the base task table, grouping on Created By and Updated By to get all of the task extended tables at once (Incident, Problem, Change, Request, etc.), but you may run into the same issue, because I believe those fields capture the username value as a string, not a reference, so you'd have to manually figure out what username was who.

One other thing to consider if you're going the route of true PA with Breakdowns, is that the user table is usually too big to breakdown on without pre-filtering to get the subset.   In your breakdown source, either consider adding a Group Type filter or using the sys_user_grmember table as your source with Group instead of sys_id to pick out specific groups you want to report on.

If you use PA vs Reporting, you DO have the option of using a breakdown mapping script to get the actual user value, like Adam pointed out above.   In the script you'd want to use the field you care about from the current form "sys_created_by" or "sys_updated_by" (or whatever the actual names for those fields are - then use a GlideRecord query to request their record from the usertable, and return the sys_id, something like:

getUser();

function getUser(){

var gr = new GlideRecord('sys_user');

gr.addQuery('user_name', current.sys_created_by);         //make sure to pass this to the script in the slushbucket above on the script form, and verify the field name!

gr.query();

if(gr.next()){

        return gr.sys_id;

}

}

[Adam Stout]

If you query the last update on task, you won't get intra-day updates (if that is important).   For instance, Josh works on an incident all day making an updated every hour.   I update it once.   My name is last updated, but Josh does all the work.

You could also look at the audit table but this could have serious performance implications so may not be advised (but sysevent may have the same issues).

[Rick__M]

Josh & Adam - thank you very much for your suggestions. I was able to create a PA Dashboard with a heatmap showing user activity from the sysevent table.

A bucket group was created to breakdown by hour but the data collected from sysevent is in UTC time. While I'm still trying to figure out how to convert to Eastern time, a workaround was to adjust the bucket groups to be -5 hours. However, that workaround resulted in the day beginning at 7PM.

Any thoughts on how to fix?