How to calculate the sum of selected values by excluding other values in a multivalue field?

[ravinallaparedd]

Hello,

I would like to know how to calculate sum of selected values by excluding other values in a multivalue field.

Refer to the below ticket data sample. A ticket contains different status such as Created, Assigned, In Progress, Pending, Resolved, Closed, and some of the statuses are repeated and each row is associated with a timestamp when the ticket got updated with a different status.

I would like to calculate the actual Ticket time by excluding pending hours in the below sample data. If the total time taken for ticket closed is 100 hrs and out 100 hrs there are 25hrs pending status then the actual time taken for ticket closed is 75hrs.

TicketNum      Description  Group    Owner    Status        Date

INC200039404    No Access   Group A   XYZ,   Created,      Sep 9, 2016 3:42:53 AM
INC200039404    No Access   Group B                       Sep 9, 2016 4:31:07 AM
INC200039404    No Access                      In Progress   Sep 9, 2016 3:11:15 PM
INC200039404    No Access                      Resolved   Sep 9, 2016 3:12:47 PM
INC200039404    No Access                      Closed       Sep 15, 2016 6:05:16 AM
INC200039404    No Access   Group C         Assigned      Sep 9, 2016 2:48:45 AM
INC200039404    No Access                      Pending     Sep 9, 2016 2:52:21 AM
INC200039404    No Access                      Assigned   Sep 9, 2016 7:18:07 PM
INC200039404    No Access                      In Progress   Sep 9, 2016 7:19:05 PM
INC200039404    No Access                      Pending     Sep 9, 2016 8:30:21 PM
INC200039404    No Access                      In Progress   Sep 9, 2016 8:43:40 PM
INC200039404    No Access                      Pending     Sep 9, 2016 8:44:33 PM
INC200039404    No Access                      In Progress   Sep 9, 2016 9:00:45 PM
INC200039404    No Access                      Pending     Sep 9, 2016 9:01:34 PM
INC200039404    No Access                      In Progress   Sep 9, 2016 9:43:03 PM
INC200039404    No Access   Group A         Assigned      Sep 9, 2016 9:46:17 PM
INC200039404    No Access                                    Sep 9, 2016 9:48:49 PM
INC200039404    No Access                      Closed       Sep 9, 2016 10:04:45 PM

Thanks in Advance,

Regards,
Ravi

[sundareshr · Accepted solution]

Try this TicketNum Description Group Ower Status Date

... | streamstats window=1 current=f latest(_time) as nextTime latest(Status) as nextStatus by TicketNum| eval p_dur=if(Status="Pending", nextTime-_time, null()) | stats sum(p_dur) as pending earliest(_time) as create latest(eval(if(Status="Closed", _time, null()))) as closed by TicketNum | where isnotnull(closed) | eval total_time=(closed-open)-pending | eval total_time=tostring(total_time, "duration")

https://docs.splunk.com/Documentation/Splunk/6.5.1/SearchReference/Streamstats

[ravinallaparedd]

Hello Rich, Sundareshr,

I got the solution from inputs which you provided.

Thank you very much.
Regards,
Ravi

[ppablo]

Hi @ravinallapareddy

Glad you found a solution through @sundareshr , but please don't forget to resolve the post by clicking "Accept" directly below his answer. Also, be sure to upvote his answer for helping you out.

[ravinallaparedd]

Hi Rich,

Thanks for quick response.

By excluding the pending time, do you mean to exclude the time between two consecutive events if the earliest event of the pair is a "Pending?" Like this group which is worth about 4.5 hours

Yes, excluding the pending times which occurs multiple times for an incident.If the earliest event of the pair is "Pending" then calculate the time difference with next immediate status and then exclude the time in consolidate time( i.e Ticket Closed Time - Ticket Open Time ).

Example:
Ticket Opened - 12-01-2016 :01:0:0
Ticket In Progress-12-01-2016:03:0:0
Ticket Pending - 12-01-2016:08:0:0
Ticket In Progress - 12-01-2016:12:0:0
Ticket Assigned - 12-01-2016:14:0:0
Ticket Pending-12-01-2016 16:0:0
Ticket In Progress -12-01-2016 18:0:0
Ticket Pending 12-01-2016 20:0:0
Ticket In Progress -12-01-2016 :22:0:0
Ticket Closed - 12-01-2016 24.:0:0

Total Time - ( 12-01-2016 24.:0:0 ,12-01-2016 :01:0:0) ->23 hrs
Pending time - (12-01-2016:12:0:0 ,12-01-2016:08:0:0)->4hrs
Pending time - (12-01-2016 18:0:0 ,12-01-2016 16:0:0)->2hrs
Pending time - (12-01-2016:22:0:0 ,12-01-2016 20:0:0)->2hrs
Total Pending time - 4+2+2 ->8

Actual Time = Total Time - Pending Time
Actual Time = 23 -8 ->15 hrs

Regards,
Ravi

[Richfez]

Please clarify: By excluding the pending time, do you mean to exclude the time between two consecutive events if the earliest event of the pair is a "Pending?" Like this group which is worth about 4.5 hours:

INC200039404 No Access Pending Sep 9, 2016 2:52:21 AM
INC200039404 No Access Assigned Sep 9, 2016 7:18:07 PM

But not where the Pending is the latest event as in the pair below?

INC200039404 No Access Group C Assigned Sep 9, 2016 2:48:45 AM
INC200039404 No Access Pending Sep 9, 2016 2:52:21 AM

Granted there's probably no one else who needs this clarification, but I do. 🙂