Hi all,
I have a field that i am calling "code_load_date" and I am running a stats command that groups them by associated serial number and code level, so essentially the events look like this-
Serial Code code_load_date
8k12 1.1 1/01/15
--------------------------------------------------------------------
8k13 1.2 01/01/15
1.3 01/11/15
1.4 01/22/15
--------------------------------------------------------------------
8k14 1.2 01/01/15
1.3 01/15/15
and essentially I want to find the difference in the days between each date (code_load_date) that code was loaded onto a machine (serial) between each new code level so my desired results would look something like this-
Serial Code code_load_date time_difference
8k12 1.1 1/01/15 0
---------------------------------
8k13 1.2 01/01/15 10
1.3 01/11/15 11
1.4 01/22/15
--------------------------------
8k14 1.2 01/01/15 14
1.3 01/15/15
Is there any way that this can be achieved? Thank you in advance.
I'm guessing your query looks like this
your search before stats | stats values(Code) as Code values(code_load_date) as code_load_date by Serial
Try something like this
your search before stats | sort Serial code_load_date | streamstats current=f window=1 values(code_load_date) as prev by Serial | eval time_diff=round(strptime(code_load_date,"%m/%d/%y")-strptime(prev,"%m/%d/%y"))/86400) | stats values(Code) as Code values(code_load_date) as code_load_date values(time_difference) as time_difference by Serial | fillnull value=0 time_difference
I'm guessing your query looks like this
your search before stats | stats values(Code) as Code values(code_load_date) as code_load_date by Serial
Try something like this
your search before stats | sort Serial code_load_date | streamstats current=f window=1 values(code_load_date) as prev by Serial | eval time_diff=round(strptime(code_load_date,"%m/%d/%y")-strptime(prev,"%m/%d/%y"))/86400) | stats values(Code) as Code values(code_load_date) as code_load_date values(time_difference) as time_difference by Serial | fillnull value=0 time_difference
It worked! Thank you