How to calculate the days between earliest date and now

[Mike6960]

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.

[gcusello · Accepted solution]

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

[Mike6960]

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

[gcusello]

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

[Mike6960]

sadly, this does not work, somehow bij 'events' it gives result but in 'statistics'and 'visualization' it gives 'No results found'

[gcusello]

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

[Mike6960]

with some tweaking it now works, thanks!!