Good Morning Everyone,
Is it possible to use delta with a conditional statement?
As in: Only give me the delta p=1 if field1=xyz
Thank you in advance
This is how the data currently looks.
I only want the delta between the very first event with a new status and the event that immediately follows it. The result can be output to a new field.
So in essence: If the status is new then I want the delta between that event and the event immediately after it (# 1 & 2 in the example)
Difference.......Difference Duration.........First Createdby Date.........First Modifiedby Date...........Status
0.000...................00:00:00.000.....................1554766782.......................1554766782...................New
66383.000...........18:26:23.000.....................1554766782.......................1554833165..................*(null)*
67674.000...........18:47:54.000.....................1554766782.......................1554834456...................(null)
I only want the delta between the very first event with a new status and the event that immediately follows it. The result can be output to a new field.
So in essence: If the status is new then I want the delta between that event and the event immediately after it (# 1 & 2 in the example)
As other people have said, you can recreate the function of delta
by using streamstats
in many ways which gives you the benefit of many other logical controls. You might try something like this:
| makeresults
| eval raw="Difference=0.000,Difference_Duration=00:00:00.000,First_Createdby_Date=1554766782,First_Modifiedby_Date=1554766782,Status=New Difference=66383.000,Difference_Duration=18:26:23.000,First_Createdby_Date=1554766782,First_Modifiedby_Date=1554833165 Difference=67674.000,Difference_Duration=18:47:54.000,First_Createdby_Date=1554766782,First_Modifiedby_Date=1554834456"
| makemv raw
| mvexpand raw
| rename raw AS _raw
| kv
| eval _time = First_Modifiedby_Date, host="foo"
| sort 0 - _time
| rename COMMENT AS "Everything above generates sample event data; everything below is your solution"
| streamstats count(eval(Status="New")) AS SessionID BY host
| dedup 2 host SessionID
| stats range(First_Modifiedby_Date) AS p BY host
Thanks! You guys are so helpful! I am going to try these out this week 😄
Hi @ryhluc01,
You can compare the previous event with the current event using a combination of streamstats current=f
and last()
| makeresults
| eval raw="Difference=0.000, DifferenceDuration=00:00:00.000,FirstCreatedByDate=1554766782,FirstModifiedByDate=1554766782,Status=New ###
Difference=66383.000, DifferenceDuration=18:26:23.000,FirstCreatedByDate=1554766782,FirstModifiedByDate=1554833165, Status= ###
Difference=67674.000, DifferenceDuration=18:47:54.000,FirstCreatedByDate=1554766782,FirstModifiedByDate=1554834456, Status="
| makemv delim="###" raw
| mvexpand raw
| rename raw as _raw
| extract
| eval Comment = "----- This will create a delta field if the previous Status field was 'New' ----"
| fillnull value="blank" Status
| streamstats current=false last(Status) as PrevStatus
| eval Delta=if(PrevStatus=="New" AND Status=="blank",DifferenceDuration,null)
| eval Status=if(Status=="blank",null,Status)
| fields - PrevStatus - Comment
The start of this search just generates your dummy data. The second part works like this:
Does that do what you were looking for?
Wow thank you! I'll have to give this a try this week 😄
@somesoni2 Sure.
This is how the data currently looks.
What I am asking is to only calculate the delta for the events directly after the event with the new status (bolded event).
So basically, I want the first modified event directly after the event was first created.
Difference.......Difference Duration.........First Createdby Date.........First Modifiedby Date...........Status
0.000...................00:00:00.000.....................1554766782.......................1554766782...................New
66383.000...........18:26:23.000.....................1554766782.......................1554833165..................*(null)*
67674.000...........18:47:54.000.....................1554766782.......................1554834456...................(null)
So what should be the output (new field??) with difference between 2nd and 3rd event only?
@somesoni2 Apologies, I just re-read my response to you and I can see that I worded that incorrectly : )
I only want the delta between the very first event with a new status and the event that immediately follows it. The result can be output to a new field.
So in essence: If the status is new then I want the delta between that event and the event immediately after it (# 1 & 2 in the example)
Could you provide little more details with some sample data and corresponding expected output?