how to track time spent on any incident or ad-hoc, summing time worked field and all Time cards

[BrendaT26789571]

I have been tasked with tracking team performance - I need to know how to track time spent on any incident or ad-hoc, summing time worked field and all Time cards entries. 

[Vikram Reddy]

Hi @BrendaT26789571,

var totalMs = 0;

// 1) time logged directly on tasks (incident, problem, change, etc.)
var ttw = new GlideRecord('task_time_worked');
ttw.addQuery('task.assignment_group', groupSysId);
ttw.query();
while (ttw.next()) {
    totalMs += ttw.time_worked.dateNumericValue();
}

// 2) ad-hoc / non-task work logged on Time Cards
var tc = new GlideRecord('time_card');
tc.addQuery('user', userSysId);
tc.query();
while (tc.next()) {
    totalMs += tc.duration.dateNumericValue();
}

var total = new GlideDuration(totalMs);
gs.info(total.getDisplayValue());

The reason you can't just build one report is that these are genuinely two different mechanisms. Every save on a task with the timer running writes a row to task_time_worked (task, user, time_worked, comments), and that same table is what rolls up into the task's own time_worked field, so incident, problem, change all feed it the same way. Ad-hoc work has nowhere to attach a task, so it goes through the Worker Portal onto time_card, tied to a weekly Time Sheet, and it never touches task_time_worked.

The trap is that both time_worked and duration are duration fields, and ServiceNow stores those internally as an epoch date, not a plain number, so a GlideAggregate SUM on either one comes back wrong or empty. You have to loop the GlideRecord and pull the raw value with dateNumericValue() (milliseconds since epoch), add it up yourself, then wrap the total back in a GlideDuration to get a readable value. For reporting to a wider audience rather than a script, build separate reports on task_time_worked and time_card grouped by user or assignment group with a Time aggregate on the duration field (the report builder handles the conversion correctly, GlideAggregate does not), and combine the two totals for a per-agent figure.

Thank you,
Vikram Karety
Octigo Solutions INC

[BrendaT26789571]

that may be A solution...but not practical, for me.  ServiceNow should have a better time tracking field for each 'ticket' - task, incident, ad-hoc etc., that is created and completed.  Ticket duration is not correct on anything I have checked. It only counts the initial time entered as time worked. (Unless our SN version is not correct.) We have just upgraded to Australia.

[Tanushree Maiti]

Hi @BrendaT26789571 

The time card retrieves time accrued on a project or spent working on any record in the Task table from the Time worked field.

This field does not appear by default on the Project Task, Incident, Problem, and Change forms and must be added by personalizing the form. Time recorded in this field is used to populate an existing time card or to create a new time card if one does not exist. A time sheet policy controls this behavior.

You can then view the incident_time_worked /task_time_worked table to view all updates. This will track how much time each person has worked on each task.

Refer: Record time worked 

Time cards 

https://www.servicenow.com/community/itsm-forum/effort-spent-on-a-incident/td-p/582997