Hi,
I am using the below query which I am running for the last 7 days , but I am getting the data for only 3 days, I want to show "0" for dates for which data is not available.
(index="AAA" sourcetype="XXXX" ) OR (index=BBB sourcetype=YYY service=ABC country=QQQQ ) | rex "sss][(?.?)]" | rex "](?.?) ::" | rex "(?.*?)]" | bucket span=1d _time | stats dc(AAAAAA) as something, list(BBBBBBB), list(ASDSA) by CD,_time | where something>1 | stats count as "Total Count" by _time
That is what timechart
does so try this (you could also use gentimes
similarly, but this is more clear and familiar, if somewhat less efficient):
(index="AAA" sourcetype="XXXX" ) OR (index=BBB sourcetype=YYY service=ABC country=QQQQ )
| rex "sss][(?.?)]" | rex "](?.?) ::" | rex "(?.*?)]"
| bucket span=1d _time
| append [ | makeresults | timechart span=1d count | table _time ]
| stats dc(AAAAAA) as something, list(BBBBBBB), list(ASDSA) by CD,_time
| where something>1
| stats count as "Total Count" by _time
Try this:
(index="AAA" sourcetype="XXXX" ) OR (index=BBB sourcetype=YYY service=ABC country=QQQQ ) | rex "sss][(?.?)]" | rex "](?.?) ::" | rex "(?.*?)]" | bucket span=1d _time | stats dc(AAAAAA) as something, list(BBBBBBB), list(ASDSA) by CD,_time | where something>1 | stats count as "Total Count" by _time|fillnull value=0 'Total Count'
Finally , my output is coming as :
10/21/2014 500
10/24/2014 600
But I am running my query from 17 to 26, so There is no data except 21 and 24, but I want the output like
10/17/2014 0
10/18/2014 0
10/19/2014 0
10/20/2014 0
10/21/2014 500
10/22/2014 0
10/23/2014 0
10/24/2014 600
10/25/2014 0
10/26/2014 0
Please help , I am in need of this output urgently
Guys any answer to this !!
@abhayneilam can you post your query using code button ( 101010
) or CTRL+ K
or add four spaces before each line of your SPL? This way special characters in your code will not be skipped.
Can you try using timechart for the second query which will give you buckets with 0 count using filnull?
<your Base search>
| bucket span=1d _time
| stats dc(AAAAAA) as something, list(BBBBBBB), list(ASDSA) by CD,_time
| where something>1
| timechart count as "Total Count"
| fillnull value=0 "Total Count"
It is not coming as expected, even if there is no data for any date , it should show 0 against each _time