average number of days tickets are open report

[Edward Rosario]

I'm trying to create a report detailing the average number of days incident tickets are open for a specific Support team for a select-able date range.

I am trying to show it for current month, last month and previous month by month. It would be great to see just some time categories, <2 days, 3-5 days, 6 - 14 days and 15 days plus for each month (ideally with a total monthly ticket count as well. 

not necessarily how long from when they were opened until they were resolved. (we want the clock to start as soon as they get and incident number, not when we assign them internally) 

We're not working with Performance Analytics so to do this, I'm trying to put together a Database View joining incident table and an incident_metric table so I can then create the report.  

Am I heading in the right direction?

[Punit S]

Hi Edwards,

Please find following step by step procedure to get the result you are looking .

Create a  Scheduled Job...


...that calls a function in a Script Include:

function u_updateAgingCategoryField() {
  var elapsedTime = 0;
  var aging = '';
  var currentTimeNow = gs.nowDateTime();
  var gr = new GlideRecord('incident');
  gr.addEncodedQuery('u_aging_category!=>28^ORu_aging_category=');
  gr.query();
  while(gr.next()) {
    elapsedTime = (gs.dateDiff(gr.opened_at, currentTimeNow, true)) /60/60/24;

    //check to see when the item was created
    if (elapsedTime <= 2) aging = '0_2';
    if (elapsedTime > 2)  aging = '3_7';
    if (elapsedTime > 7)  aging = '8_14';
    if (elapsedTime > 14) aging = '15_21';
    if (elapsedTime > 21) aging = '22_28';
    if (elapsedTime > 28) aging = '>28';

    gr.setWorkflow(false);    //skip any Business Rules
    gr.autoSysFields(false);  //do not update system fields
    gr.u_aging_category = aging;
    gr.update();
  }
}

Notice in the function that I am excluding incidents >28 days because we do not want to keep updating them with ">28 Days" all the time.

Now for the trick so that the Pivot Table orders the columns properly - on the Aging Category dictionary record, set the Default value to be "0_2" and the Choice field to "Dropdown without -- None --" and create some Choice entries:



Otherwise the report will sort the columns alphabetically. And so you should end up with the following report:

Please mark this correct or helpful if this helps to resolve your issue. 

Thanks,

Punit