Hello,
Today i have this query in my dashboard :
| loadjob savedsearch="myquery"
|eval FromDate = "2020-01-23"
|eval ToDate = "2020-01-23"
| eval ToDateexpir = strptime("2020-01-23", "%Y-%m-%d")
| eval expiring = ToDate + 86400*4
| eval expiring = strftime(expiring, "%Y-%m-%d")
| where (strftime(_time, "%Y-%m-%d") >= FromDate) AND (strftime(_time, "%Y-%m-%d") <= ToDate)
| stats count(eval(if(MESSAGE="show",STEP,NULL))) AS showed,
count(eval(if(MESSAGE="send",STEP,NULL))) AS sent,
count(eval(if(MESSAGE="delete",STEP,NULL))) AS deleted by client |where showed>0
|stats sum(showed) AS showed,sum(sent) AS sent,sum(deleted) AS deleted
| eval AVG= round(((showed - (sent+deleted))/showed*100),2)." %"
I want to keep the same query and logic, but for the calculation of the fields "sent" i want it to be from "FromDate" to "expiring"
Thank you 🙂
| where (strftime(_time, "%Y-%m-%d") >= FromDate) AND (strftime(_time, "%Y-%m-%d") <= ToDate)
Is this query really work?
| loadjob savedsearch="myquery"
|eval FromDate = "2020-01-23"
|eval ToDate = "2020-01-23"
| eval ToDateexpir = strptime("2020-01-23", "%Y-%m-%d")
| eval expiring = ToDate + 86400*4
| eval expiring = strftime(expiring, "%Y-%m-%d")
| where (strftime(_time, "%Y-%m-%d") >= FromDate) AND (strftime(_time, "%Y-%m-%d") <= ToDate)
| stats count(eval(if(MESSAGE="show",STEP,NULL))) AS showed,
count(eval(if(MESSAGE="send",STEP,NULL))) AS sent,
count(eval(if(MESSAGE="delete",STEP,NULL))) AS deleted by client |where showed>0
|stats sum(showed) AS showed,sum(sent) AS sent,sum(deleted) AS deleted
| eval AVG= round(((showed - (sent+deleted))/showed*100),2)." %"