Two stats on different period

[tahasefiani]

Hello,
I have this query that return me the table below.

The query :

    | loadjob savedsearch="myquery"
          | where (strftime(_time, "%Y-%m-%d") >= "2019-12-09") AND (strftime(_time, "%Y-%m-%d") <= "2019-12-10")
          | stats dc(eval(if(((STEP=="Recieved",ID_MESSAGE,NULL))) AS Recieved,
                    dc(eval(if(((STEP=="answered",ID_MESSAGE,NULL))) AS answered,
                    dc(eval(if(((STEP=="deleted",ID_MESSAGE,NULL))) AS deleted by client
|fields client,Recieved,answered,deleted

Result :

The problem is , for some client like "35535" he receieved the Message 10/12 but he answered the 12/12, i want to give my client a margin of 4 days.
So,if my client recieves the message between the 09/12 and 10/12, i will check if his answered between the 09/10 and the 14/12.
the messages received and deleted must be over the period from 09/12 and 10/10, but those answered must be from 09/12 and 14/12
Thanks 🙂

[to4kawa]

| loadjob savedsearch="myquery" 
| stats dc(eval(if(STEP=="Recieved" AND (strftime(_time, "%Y-%m-%d") >= "2019-12-09" AND strftime(_time, "%Y-%m-%d") <= "2019-12-10"),ID_MESSAGE,NULL))) AS Recieved,
    dc(eval(if(STEP=="answered" AND (strftime(_time, "%Y-%m-%d") >= "2019-12-09" AND strftime(_time, "%Y-%m-%d") <= "2019-12-14",ID_MESSAGE,NULL))) AS answered,
    dc(eval(if(STEP=="deleted" AND (strftime(_time, "%Y-%m-%d") >= "2019-12-09" AND strftime(_time, "%Y-%m-%d") <= "2019-12-10"),ID_MESSAGE,NULL))) AS deleted by client 
| fields client,Recieved,answered,deleted

Be careful time picker.

[morethanyell]

Not an answer but just wanted to point out that this portion | where (strftime(_time, "%Y-%m-%d") >= "2019-12-09") AND (strftime(_time, "%Y-%m-%d") <= "2019-12-10") might not work as expected. It's better to make comparison operation like greater than if the time is in epoch, rather than formatted in string. maybe consider doing |where _time >= strptime("2019-12-09", "%F") AND _time <= strptime("2019-12-10", "%F")

[to4kawa]

| makeresults 
| eval check=if(strftime(now(),"%F") < strftime(relative_time(now(),"+1d@d"),"%F"), "OK" ,"NG")

I think so ,too. but Splunk can....

[to4kawa]

what's your expected result?

[tahasefiani]

@to4kawa my expected result is :
client : 35535
Recieved : 1
answered : 1
deleted : 0
knowing that the research period is from 09/12 to 10/12,but because my client answered between 09 and 14 I display 1 in answered