In the following search I want to have the average for the events where GB_w is < 15 days
| stats earliest(A_Z) AS A_Z earliest(D_A) AS D_A
| eval eA_Z=strptime(A_Z,"%Y-%m-%d %H:%M:%S.%N")
| eval eD_A_I=coalesce(strptime(D_A, "%Y-%m-%d %H:%M:%S.%N"),now())
| eval GB_w=floor((eD_A_I-eA_Z)/86400)
| chart avg(GB_w) As GDA
| eval GDA=round(GDA,0)
I thought i could use something like this:
| eval GB_w=floor((eD_A_I-eA_Z)/86400)
|where GB_w <15
| chart avg(GB_w) As GDA
| eval GDA=round(GDA,0)
Somehow the result is 0. It also seems that the search always uses the 'now'time, even if there is a D_A time present in the event.
What am i doing wrong?
do eA_Z
, eD_A
, GB_w
and GDA
all come back with expected values?
I think you could do something like this, as a different approach:
| convert mktime(A_Z) as eA_Z mktime(D_A) as eD_A timeformat="%Y-%m-%d %H:%M:%S.%N"
| stats earliest(eA_Z) AS eA_Z earliest(eD_A) AS eD_A
| eval eA_Z_I=relative_time(eA_Z,"@d")
| eval eD_A_I=if(isnull(eD_A),relative_time(now(),"@d"),relative_time(eD_A,"@d"))
| eval GB_w=eD_A_I-eA_Z_I
| search GB_w<15
| chart avg(GB_w) as GDA
| eval GDA=round(GDA,0)