Hello,
i Have this query that i want to improve
| loadjob savedsearch="myquery"
| where (strftime(_time, "%Y-%m-%d") = "2020-02-27")
| stats dc(eval(if(STEP="Sent",ID_MESSAGE,NULL))) AS sent,
dc(eval(if(STEP="RME",ID_MESSAGE,NULL))) AS RME by ID_MESSAGE
| join type=left ID_MESSAGE [| loadjob savedsearch="myquery"
| where (strftime(_time, "%Y-%m-%d") >= "2020-02-27") AND (strftime(_time, "%Y-%m-%d") <= "2020-03-03")
|stats dc(eval(if(STEP="Click",ID_MESSAGE,NULL))) AS click by ID_MESSAGE]
|where sent>0
|stats sum(sent) AS sent,sum(RME) AS RME,sum(click) AS click
This is the result that i have today
And this is the result that i want to have
knowing that RME and click are calculated on the ID_MESSAGE sent
@to4kawa I added a timechart and sum, this is what i have as result
For sent and RME ,i want only ther distinct count of ID_MESSAGE of the 27.
But for click I want distinct count of the ID_MESSAGE(that we have counted on sent) from 27 to 03/03.
The rest is like a countdown of sent
I see, the answers is updated.
UPDATE2:
| loadjob savedsearch="myquery"
| where (strftime(_time, "%Y-%m-%d") >= "2020-02-27") AND (strftime(_time, "%Y-%m-%d") <= "2020-03-03")
| bin span=1d _time
| stats dc(eval(STEP="Sent" AND strftime(_time,"%F")="2020-02-27")) AS sent
, dc(eval(STEP="RME" AND strftime(_time,"%F")="2020-02-27")) AS RME
, dc(eval(STEP="Click")) AS click by _time ID_MESSAGE
| search ID_MESSAGE="ABC" OR ID_MESSAGE="XYZ"
| fields - ID_MESSAGE
| stats sum(*) as * by _time
| eval The_rest=sent + RME - click
| streamstats sum(The_rest) as "The rest"
| table _time sent RME click "The rest"
I'm not sure the logs and recently query still
now.
Hi @tahasefiani,
Try this.
| loadjob savedsearch="myquery"
| where (strftime(_time, "%Y-%m-%d") >= "2020-02-27") AND (strftime(_time, "%Y-%m-%d") <= "2020-03-03")
| stats dc(eval(if(STEP="Click",ID_MESSAGE,NULL))) AS click by ID_MESSAGE
| timechart span=1d sum(click) AS click
| eval sent=
[| loadjob savedsearch="myquery"
| where (strftime(_time, "%Y-%m-%d") = "2020-02-27")
| stats dc(eval(if(STEP="Sent",ID_MESSAGE,NULL))) AS sent by ID_MESSAGE
| stats sum(sent) AS sent
| return $sent]
| eval RME=
[| loadjob savedsearch="myquery"
| where (strftime(_time, "%Y-%m-%d") = "2020-02-27")
| stats dc(eval(if(STEP="RME",ID_MESSAGE,NULL))) AS RME by ID_MESSAGE
| stats sum(RME) AS RME
| return $RME]
| eval the_rest=sent-(RME+click)
Thanks @manjunathmeti
But there is a problem in your query! The result which I want on "click" and "RME", it's always on ID_MESSAGE recover in "sent" of 27/02/2020. And besides that is why I had used the join in my query.
Example: Admitting that we have "XYZ" and "WVT" as ID_MESSAGE on "sent"
The search in "click" and "RME" must be on "XYZ", "WVT"
try this:
| loadjob savedsearch="myquery"
| where (strftime(_time, "%Y-%m-%d") >= "2020-02-27") AND (strftime(_time, "%Y-%m-%d") <= "2020-03-03")
| stats dc(eval(if(STEP="Click",ID_MESSAGE,NULL))) AS click by ID_MESSAGE
| timechart span=1d sum(click) AS click by ID_MESSAGE
| join type=left ID_MESSAGE
[| loadjob savedsearch="myquery"
| where (strftime(_time, "%Y-%m-%d") = "2020-02-27")
| stats dc(eval(if(STEP="Sent",ID_MESSAGE,NULL))) AS sent, dc(eval(if(STEP="RME",ID_MESSAGE,NULL))) AS RME by ID_MESSAGE
| stats sum(sent) AS sent, sum(RME) AS RME by ID_MESSAGE]
| eval the_rest=sent-(RME+click)