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????
Like this:
Your Search Here:
| search (state="Pending" OR state="Resolved")
| stats range(_time) AS duration BY number
| eval duration=tostring(duration, "duration")
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'
)
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())
Like this:
Your Search Here:
| search (state="Pending" OR state="Resolved")
| stats range(_time) AS duration BY number
| eval duration=tostring(duration, "duration")
Did not know about this | stats range(_time) AS duration BY number
. TIL something
That's what upvotes
are for! 😆
Never knew there was range(_time) option..Thanks..
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
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.