I have the following search:
..index bla bla...
| eval eD_A=strptime(D_A, "%Y-%m-%d %H:%M:%S.%N") , eD_AV=strptime(D_AV, "%Y-%m-%d %H:%M:%S.%N")
| eval days=floor((eD_A- e D_AV)/86400)
| stats count as daycount by days
| eval days = if(days<=7,"Binnen KPI","Buiten KPI")
| stats sum(daycount) as daycount by days
The problem I have is that some events don't have the D_A field because the process is not ready yet. I still would like to try calculate the days between the D_AV and now in the case when D_A has no date yet. Another question I have is how i can calculate with the earliest D_AV field because this is also a date that can differ.
Hi Mike 6960,
replace eD_A=strptime(D_A, "%Y-%m-%d %H:%M:%S.%N") with
eD_A=coalesce(strptime(D_A, "%Y-%m-%d %H:%M:%S.%N"),now())
For the second question, what do you mean with "D_AV field because this is also a date that can differ."?
Bye.
Giuseppe
Hi Mike 6960,
replace eD_A=strptime(D_A, "%Y-%m-%d %H:%M:%S.%N") with
eD_A=coalesce(strptime(D_A, "%Y-%m-%d %H:%M:%S.%N"),now())
For the second question, what do you mean with "D_AV field because this is also a date that can differ."?
Bye.
Giuseppe
For the same id an event can have a different D_AV date. For example:
ID D_AV STATUS
1 2017-10-10 A
2 2017-09-11 A
1 2017-10-23 A
For the calculation (| eval days=floor((eD_A- e D_AV)/86400)) I want to use the earliest D_AV . I tried this withe earliest but it doesn't seem to work
Hi Mike 6960,
do you have more events with the same ID that have different D_AV ?
If this is your situation you must add an additional stats command before the first eval
| stats values(eD_A) AS eD_A earliest(eD_AV) AS eD_AV BY ID
Bye.
Giuseppe
sadly, this does not work, somehow bij 'events' it gives result but in 'statistics'and 'visualization' it gives 'No results found'
Sorry I was in train and I written an error, try with:
..index bla bla...
| stats values(D_A) AS D_A earliest(D_AV) AS D_AV BY ID
| eval
eD_A=coalesce(strptime(D_A, "%Y-%m-%d %H:%M:%S.%N"),now()),
eD_AV=strptime(D_AV, "%Y-%m-%d %H:%M:%S.%N")
| eval days=floor((eD_A- e D_AV)/86400)
| stats count as daycount by days
| eval days = if(days<=7,"Binnen KPI","Buiten KPI")
| stats sum(daycount) as daycount by days
If you have more D_As for each ID use earliest instead values in the first stats command
Bye.
Giuseppe
with some tweaking it now works, thanks!!