Archive

## How to calculate time difference in statistics using another field

Path Finder

Hi,this is the statistics i have

``````dv_cmdb_ci  number  _time   state
``````

1 A B 2018-04-03 15:00:51 Assigned
2 A B 2018-04-03 15:03:28 Work in Progress
3 A B 2018-04-03 18:09:46 Work in Progress
4 A B 2018-04-03 18:11:36 Assigned
5 A B 2018-04-03 18:14:53 Assigned
6 A B 2018-04-03 18:22:14 Work in Progress
7 A B 2018-04-03 18:51:07 Work in Progress
8 A B 2018-04-03 18:57:26 Work in Progress
9 A B 2018-04-03 19:18:41 Work in Progress
10 A B 2018-04-04 17:25:59 Work in Progress
11 A B 2018-04-04 17:26:25 Pending
12 A B 2018-04-19 16:18:43 Resolved
13 A B 2018-04-22 17:00:07 Closed

This is for single host and single ticket data.

How can i get the time difference when ticket was in pending to Resolve.

From above based on state my time difference should be (2018-04-19 16:18:43)-(2018-04-04 17:26:25)=difference in second.

So how to calculate it????

Tags (2)
1 Solution
Esteemed Legend

Like this:

``````Your Search Here:
| search (state="Pending" OR state="Resolved")
| stats range(_time) AS duration BY number
| eval duration=tostring(duration, "duration")
``````
SplunkTrust

Another approach (A generic one which allows finding duration between any two states)

``````your base search
| eval temp=dv_cmdb_ci."##".number
| chart max(_time) over temp by state
| rex field=temp "(?<dv_cmdb_ci>.+)##(?<number>.+)" | fields - temp
| table dv_cmdb_ci number *
``````

Above will give time for each state for a host-ticket combination as a column. You can now add eval statements to find duration between any two states, by adding it to end of above search, e.g.

``````above search
| eval PendingToResolvedDuration=Resolved-Pending
``````

Any state value which has spaces in them, enclose them in single quotes. (e.g. `| eval WIPtoResolved=Resolved-'Work In Progress'`)

Esteemed Legend

Or like this (gives you options to do any kind of state-change timing all together, which the other answer does not):

``````Your Search Here
| eval PendingTime=if(state="Pending", _time, null())
| eventstats min(PendingTime) AS PendingTime BY number
| eval PendingToResolvedTime = if(state="Resolved", tostring((_time - PendingTime), "duration"), null())
``````
Esteemed Legend

Like this:

``````Your Search Here:
| search (state="Pending" OR state="Resolved")
| stats range(_time) AS duration BY number
| eval duration=tostring(duration, "duration")
``````
Motivator

Did not know about this `| stats range(_time) AS duration BY number`. TIL something

Esteemed Legend

That's what `upvotes` are for! ๐

Path Finder

Never knew there was range(_time) option..Thanks..

Motivator

In your base search add `(state="Pending" OR state="Resolved")`, then use the transaction command as follows:

``````| transaction <field(s) to group by> startswith="Pending" endswith="Resolved"
``````

The transaction command automatically gives you a "duration" field in seconds.

Hope this helps

Esteemed Legend

True, but one should NEVER use `transaction` if there is ANY other way. In this case there is. The overhead and unscalability of `transaction` means that it should be avoided at all costs.