topic Re: data is not appropriate in Getting Data In

data is not appropriate

abhayneilam — Mon, 28 Sep 2020 17:57:40 GMT

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.

Re: data is not appropriate

lpolo — Fri, 24 Oct 2014 13:28:41 GMT

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'

Re: data is not appropriate

abhayneilam — Fri, 24 Oct 2014 13:33:25 GMT

It is not coming as expected, even if there is no data for any date , it should show 0 against each _time

Re: data is not appropriate

abhayneilam — Fri, 24 Oct 2014 14:53:57 GMT

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

Re: data is not appropriate

abhayneilam — Sat, 21 Apr 2018 14:59:37 GMT

Guys any answer to this !!

Re: data is not appropriate

niketn — Sat, 21 Apr 2018 17:37:15 GMT

@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"

Re: data is not appropriate

woodcock — Sun, 22 Apr 2018 17:03:43 GMT

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