Report for all tickets opened in last 12 months that were opened for longer than 2 weeks

[Brent Cox]

Is there any way to run a report on the TASK or incident table for all tickets that have been opened in the last 12 months that were opened for longer than 2 weeks? Currently I have a filter for duration greater than 14 days, but I do believe duration is only populated once a ticket is closed. I would also like to include tickets that are still active, if possible. Can this be done? 

[AshishKM]

Hi @Brent Cox , 

Yes, duration field is calculated after incident is closed. You can re-write the same business logic update the duration field on regular basis ( if required )

Check this : similar question

Solved: Catalog Task duration Business Rule - ServiceNow Community

-Thanks,

AshishKM

[Shivalika]

Hello @Brent Cox 

In OOB we don't have such feature to do relative date with respect to a field, we could do relative date only with respect to a relative date. so you would need something custom. 

For now, I just wrote a script reading your requirement - you can also write this in your fix script or Background script and run it whenever you need a report - if you don't want anything custom. 

(function() {

    var now = new GlideDateTime();

    var oneYearAgo = new GlideDateTime();

    oneYearAgo.addMonthsUTC(-12);

    var gr = new GlideRecord('incident');

    gr.addQuery('opened_at', '>=', oneYearAgo); // Opened in the last 12 months

    gr.query();

    while (gr.next()) {

        var openedAt = new GlideDateTime(gr.getValue('opened_at'));

        var thresholdDate = new GlideDateTime(openedAt);

        thresholdDate.addDaysUTC(14); // Open + 14 days

        if (gr.active) {

            // Still open, check if open for more than 14 days

            if (now.after(thresholdDate)) {

                gs.print('ACTIVE: ' + gr.number + ' | Opened: ' + gr.opened_at);

            }

        } else {

            // Closed, check if closed_at is at or after open + 14 days

            var closedAt = new GlideDateTime(gr.getValue('closed_at'));

            if (closedAt.after(thresholdDate) || closedAt.equals(thresholdDate)) {

                gs.print('CLOSED >14d: ' + gr.number + ' | Opened: ' + gr.opened_at + ' | Closed: ' + gr.closed_at);

            }

        }

    }

})();

Its working perfectly fine.

Kindly mark my answer as helpful and accept solution if it helped you in anyway. This will help me be recognized for the efforts and also move this questions from unsolved to solved bucket. 

Regards,

Shivalika 

My LinkedIn - https://www.linkedin.com/in/shivalika-gupta-540346194

My youtube - https://youtube.com/playlist?list=PLsHuNzTdkE5Cn4PyS7HdV0Vg8JsfdgQlA&si=0WynLcOwNeEISQCY

[Kieran Anson]

Are you licensed for Performance Analytics? This is an exact use case for the product

[Shivalika]

Right ! PA is go to for these statistical intricacies. But @Brent Cox mentioned he only wants to use the standard filter page , hence gave him a workaround