Getting Data In

data is not appropriate

abhayneilam
Contributor

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

Tags (4)
0 Karma

woodcock
Esteemed Legend

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
0 Karma

lpolo
Motivator

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'
0 Karma

abhayneilam
Contributor

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

0 Karma

abhayneilam
Contributor

Guys any answer to this !!

0 Karma

niketn
Legend

@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"
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

abhayneilam
Contributor

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

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...