Hello,This is my query
| loadjob savedsearch="myquery"
|where strftime(_time, "%Y-%m-%d") = "2020-02-24"
|eval show=if(STEP="show",strftime(_time, "%Y-%m-%d %H:%M:%S"),NULL),click=if(STEP="Click",strftime(_time, "%Y-%m-%d %H:%M:%S"),NULL)
|stats max(show) as show ,min(click) as click by client
I have two date : show and click
i want to calculate the difference between the two date by client
Exemple of the result :
client : RYU5890
Show : 2020-02-24 10:15:00
click: 2020-02-24 10:20:00
Diff: 5
client : FH5Y411
Show : 2020-02-24 09:20:00
click: 2020-02-24 09:21:00
Diff: 1
Give these a try
| loadjob savedsearch="myquery"
|where strftime(_time, "%Y-%m-%d") = "2020-02-24"
|eval show=if(STEP="show",_time, null()) ,click=if(STEP="click",_time, null())
|stats max(show) as show ,min(click) as click by client
| eval Diff=tostring(click-show,"duration")
| convert ctime(click) ctime(show) timeformat="%Y-%m-%d %H:%M:%S"
OR
| loadjob savedsearch="myquery"
|where strftime(_time, "%Y-%m-%d") = "2020-02-24"
| eval {STEP}=_time
|stats max(show) as show ,min(click) as click by client
| eval Diff=tostring(click-show,"duration")
| convert ctime(click) ctime(show) timeformat="%Y-%m-%d %H:%M:%S"
Give these a try
| loadjob savedsearch="myquery"
|where strftime(_time, "%Y-%m-%d") = "2020-02-24"
|eval show=if(STEP="show",_time, null()) ,click=if(STEP="click",_time, null())
|stats max(show) as show ,min(click) as click by client
| eval Diff=tostring(click-show,"duration")
| convert ctime(click) ctime(show) timeformat="%Y-%m-%d %H:%M:%S"
OR
| loadjob savedsearch="myquery"
|where strftime(_time, "%Y-%m-%d") = "2020-02-24"
| eval {STEP}=_time
|stats max(show) as show ,min(click) as click by client
| eval Diff=tostring(click-show,"duration")
| convert ctime(click) ctime(show) timeformat="%Y-%m-%d %H:%M:%S"
Thank you 😉