How to search for status change in field values?

[kiru2992]

Hello, 

I have events with id, status that is collected everyday for all the ids. I would like to know when the time(days) taken for the status to change from one value to another for each id. 

Sample Data:

_time     ID        Status

t1           100        open

t1           101        In progress

t1           102        open

t1           103        closed

-----------------------------------------------------

t2           100        open

t2           101        In progress

t2           102        In progress

t2           103        closed

----------------------------------------------------------

t3           100        In progress

t3           101        closed

t3           102        In progress

t3            103       closed

Expected Output:

ID            Time Taken from Open to In Progress

100                  t3-t1

102                  t2-t1

Can you please help me on how to proceed? Kindly let me know in case of any further clarifications. 

[bowesmana]

Here's an example of how it can be done, which you should be able to tailor to your environment

|  makeresults
|  eval id=mvrange(100,110)
|  mvexpand id
|  eval Status=split("Open:In Progress:Closed",":")
|  mvexpand Status
|  eval t=1
|  accum t
|  eval _time=_time-((200-id)*3600)+(((t-1)%3)*8*3600)+(random()%(8*3600))
|  rename COMMENT as "^^^^^^^^^^^^ Up to here is setting up dummy data ^^^^^^^^^^^^"
|  streamstats window=2 range(_time) as duration list(Status) as Transition by id
|  eval hours=floor(duration/3600), duration=duration%3600, minutes=floor(duration/60), seconds=duration%60
|  eval Duration=printf("%sh %sm %ss", hours, minutes, seconds)
|  where mvcount(Transition)=2 AND Status="In Progress"
|  eval Transition=mvjoin(Transition,"-->")
|  table id Transition Duration

Note that it is using streamstats to calculate range of _time between each pair of events, so the order of events must be sorted in ascending order of time in this example within the ID

The 'where' clause simply removes the unwanted data - if you remove the 'AND ... ' part, then you will also see transition times from In Progress to Closed

Hope this helps

[kiru2992]

I didn't get any result for the above query. When I debugged found that the value of Duration = Nullh Nullm Nulls. Any idea about the reason?

[kiru2992]

I have solved the issue of Duration being Null. But I the get below output from your query based on the sample data in my first post.

Output:

ID                            Transition                          Duration           Expected Duration

100           Open--->In Progress                     t3-t2                               t3-t1

101           In Progress--->In Progress         t2-t1                        Not required

102           Open--->In Progress                     t2-t1                                t2-t1

102           In Progress--->In Progress         t3-t2                        Not required

Can you please help in achieving the expected output?

[bowesmana · Accepted solution]

@kiru2992 

The incorrect cals is due to having multiple 'open' rows, which was not accounted for.

This example query uses your data and gives you the expected outcome

|  makeresults
| eval _raw="
X,ID,Status
t1,100,open
t1,101,In progress
t1,102,open
t1,103,closed
t2,100,open
t2,101,In progress
t2,102,In progress
t2,103,closed
t3,100,In progress
t3,101,closed
t3,102,In progress
t3,103,closed"
| multikv forceheader=1
| eval _time=case(X="t1",_time-7290,X="t2",_time-3645,1==1,_time)
| table _time ID Status X
|  streamstats earliest_time(eval(Status="open")) as FO earliest_time(eval(Status="In progress")) as IP list(Status) as Transition list(X) as X by ID
|  eval Transition=mvdedup(Transition)
|  where mvcount(Transition)=2 AND Status="In progress"
|  eval duration=IP-FO
|  eval hours=floor(duration/3600), duration=duration%3600, minutes=floor(duration/60), seconds=round(duration%60,0)
|  eval Duration=printf("%sh %sm %ss", hours, minutes, seconds)
|  eval Transition=mvjoin(Transition,"-->")
|  table ID Transition Duration
|  sort ID
|  dedup ID

From the streamstats down.

If looks for the first time the ID was open, then looks for the first time the ID was in progress and calculated duration (IP-FO).

The mvdedup removes duplicate states and the where clause removes all ones other than what you want.\

Hope this helps

[kiru2992]

Thanks a lot.. It worked like magic after few changes..