topic Re: fill in 0 when there is no data in Splunk Search

fill in 0 when there is no data

mcohen13 — Tue, 29 Sep 2020 19:52:49 GMT

i have an index that calc amount of events for a specific domain name
this index have 3 fields: date,domain_name, event_count
if a domain have no event_count for a specific date than i don't have that record in the index
can i manipulate splunk into thinking that on missing dates for the last month the value was 0 (besides adding this data to the file)?

Re: fill in 0 when there is no data

poete — Mon, 11 Jun 2018 08:47:32 GMT

Hi,

I think this is what you are looking for:
https://docs.splunk.com/Documentation/Splunk/7.1.0/SearchReference/Fillnull

Re: fill in 0 when there is no data

mcohen13 — Tue, 29 Sep 2020 19:52:52 GMT

fillnull will not to the job
because i don't have null values in that field for a specific date
i have no values for that date

for example :
Query:
index="someindex" "domain"="domain_x" ] | chart max(event_count) over date
data:
date domain_x
2018-06-02 128
2018-06-03 623
2018-06-04 331

now i want to add that on other dates of the last month the value was 0 so i can call

Re: fill in 0 when there is no data

niketn — Mon, 11 Jun 2018 11:19:28 GMT

@mcohen13, As far as your date field is having epoch time and not string time, fillnull should work. If it is string time then you either need to convert it to epoch using strptime() or use _time with span=1d instead.

Following is a run anywhere search based on Splunk's _internal index similar to your question (instead of 1d I have used 1h, to form more buckets).

index="_internal" "sourcetype"="splunkd" log_level=INFO
| chart span=1h max(cpu_seconds) as MaxValue over _time
| fillnull value=0 MaxValue

I have give max(cpu_seconds) an alias MaxValue and used fillnull for MaxValue. You can try without final fillnull command to see if Null Values are actually present or not.

Also, if you are plotting the result in chart, in the Chart Configuration Options i.e. Edit UI Panel and Format Visualization to change the Null Value to Zero to have similar efffect directly in chart (without using fillnull command).

Re: fill in 0 when there is no data

jlvix1 — Mon, 11 Jun 2018 13:49:01 GMT

I thought fillnull is only good for charting? He never said he was charting, I think he needs to put in a whole record for that entry he is missing...

Re: fill in 0 when there is no data

jlvix1 — Mon, 11 Jun 2018 13:51:16 GMT

Where are the events coming from that are in this index? Sounds to me like the data source itself is at fault and you're missing events, leaving you with gaping holes in your data because you should be getting zero-based events.

Re: fill in 0 when there is no data

skoelpin — Mon, 11 Jun 2018 14:29:29 GMT

Yes, you can use | makeresults in your search to create that missing data then create some conditional logic to fill null values OR leave it as-is. Here's an example

| makeresults | eval domain_name=""
| [search index=.... <YOUR SEARCH>]
| eval domain_name=if(isnull(domain_name),"0",'domain_name')

Re: fill in 0 when there is no data

mcohen13 — Tue, 29 Sep 2020 19:53:08 GMT

i get this error:
"Error in 'SearchParser': Subsearches are only valid as arguments to commands. Error at position '31' of search query '| makeresults | eval info="" | [search index="doma'."
The query:
| makeresults | eval info="" | [search index="domain_event_agg_info" event_domain="XXXX.YYY."] | eval info=if(isnull(event_count),"0",'event_count')