Splunk Dev

## 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:

| search (state="Pending" OR state="Resolved")
| stats range(_time) AS duration BY number
| eval duration=tostring(duration, "duration")
Revered Legend

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

| 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):

| 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:

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

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.

Get Updates on the Splunk Community!

#### Discover SplunkTrust and MVP Articles, Instant Translation, and More on Splunk ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

#### Integrating Kubernetes and Splunk Observability Cloud

We need end-to-end insight into our application environments to confidently ensure everything is up and ...

#### Index This | What has a tail and a head but no body?

July 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with this month’s ...