Splunk Search

Timechart Table with "No results found"

kaeleyt
Path Finder

Hi all, 

I have a request from a tenant in our environment that requires us to create a dashboard where each column is a date and each row has various criteria. We accomplished this by using the following search structure:

 

[base search]
| timechart limit=0 span=1d useother=false count as "Row 1" by sourcetype
| fillnull
| reverse
| untable _time, sourcetype, "Row 1"
| eval Time= strftime(_time, "%m-%d-%y")
| table Time, "Row 1"
| transpose header_field=Time 0
|append [search [base search] | timechart limit=0 span=1d useother=false count as "Row 1" by sourcetype
| fillnull
| reverse
| untable _time, sourcetype, "Row 2"
| eval Time= strftime(_time, "%m-%d-%y")
| table Time, "Row 2"
| transpose header_field=Time 0]
...

 

Due to the variations in search criteria for each row, it makes the most sense to simply append a new row. The results end up looking like the following:

SampleTable.PNG

The problem I am having is that one of the searches produces no results almost all of the time (note that Row3 is missing). This tenant would like this to show "Row3" as a row of 0's implying that there were no events that match the specified criteria for that row.

Does anybody have a good way to create a timechart table of all 0's for searches that return "No results found"?

I have seen a lot of questions and answers on here that basically use an append to give a single value of 0 but for this use case I would essentially like to get a "0" for each date on the table like the following :

SampleTable2.PNG

Labels (1)
0 Karma
1 Solution

kaeleyt
Path Finder

That got me really close. I tried it over "Last 7 Days" and it gave me 6 days with 9-4-20 missing for some reason. I took your search and changed around some things and got this working:

| makeresults 
| eval count=0
| eval rows=split("Row 1|Row 2|Row 3|Row 4", "|")
| mvexpand rows
| timechart values(count) by rows limit=0
| fillnull value=0
| eval _time= strftime(_time, "%m-%d-%y")
| append 
    [search that may or may not produce results]
| stats max("Row 1") as "Row 1",
max("Row 2") as "Row 2",
max("Row 3") as "Row 3",
max("Row 4") as "Row 4"
by _time

 

In my appended search I called each aggregation "Row 1",  "Row 2", etc. so at the end in the last stats it will pull the highest number for each row and time, so if there are results it will pull the value and if there aren't results it will pull the 0 created in the makeresults search.

For some reason it worked for me when you make the null/filler results first and then append your actual search.

 

@yeahnahthank you for your help!

View solution in original post

yeahnah
Motivator

Hi @kaeleyt 

Interested to know if this helped solve your problem? 

0 Karma

to4kawa
Ultra Champion

[base search1] OR [base search2]  OR . . . |bin span=1d _time
| stats count as by _time sourcetype
| eval Time= strftime(_time, "%m-%d-%y")
| xyseries sourcetype Time count 

0 Karma

yeahnah
Motivator

Hi @kaeleyt 

Append the following to the end of your search query ...

 

| append [ |makeresults | eval column=split("Row 1|Row 2|Row 3|Row 4", "|") | mvexpand column | table column ]
| stats values(*) AS * BY column
| fillnull value=0

 

Here's a run anywhere example with some dummy data which should help demonstrate how it works ...

 

| makeresults
| eval column=split("Row 1|Row 2|Row 4", "|")
| mvexpand column
| eval date1=48, date2=0, date3=99
| table column date*
 `comment("# append this to the end of your search query")`
| append [ |makeresults | eval column=split("Row 1|Row 2|Row 3|Row 4", "|") | mvexpand column | table column ]
| stats values(*) AS * BY column
| fillnull value=0

 

 Hope it helps.  Please mark post as answered, if it does.

0 Karma

kaeleyt
Path Finder

This didn't seem to work how I was expecting. I was hoping to get an empty timechart that uses the time range of the search to determine the dates. For this solution it seems you would have to hard code the dates.

0 Karma

yeahnah
Motivator

Hi @kaeleyt 

The question you asked was...

Does anybody have a good way to create a timechart table of all 0's for searches that return "No results found"?

The solution I provided does show an example way to solve this problem, based on your examples.  However, your reply indicates that it does work, but then you say it due to needing to hard code the dates.  So maybe there is some confusion with my answer, or your question has not been clear enough.  

This is the provided solution.

| append [ |makeresults | eval column=split("Row 1|Row 2|Row 3|Row 4", "|") | mvexpand column | table column ]
| stats values(*) AS * BY column
| fillnull value=0

It does not care what the date columns and only hard codes the first row values (based on the example).  But, I agree, the solution does expect that the initial base search(es) did return at least one result.   So, is this the problem?  At times,  all your base searches return no results and hence the solution does not work?  There is probably a way to fix this too, if that is the case, but you need to be clear with what is required.

 
, what they are named or how many, but it does expect that the initial base search does find some results.    

0 Karma

kaeleyt
Path Finder

Hi yes, sorry I had put that in the question title but probably was not clear enough in my description. The problem I'm having with this is that I only really need this solution because some of these searches return "no results found" sometimes. Per the tenants request we would like to find a way to keep the dashboard uniform so that there is a table of 0's for example when there are no errors for the last 7 days for example. 

I'd like it to show all 0's when there are no events but everything I've read leads me to believe there is no clean way to do this, especially with a timechart search.

It seems the only way for there to be a table/visualization is if the search returns at least 1 result but that's exactly the issue I'm trying to solve if that helps explain what I'm trying to get at.

0 Karma

yeahnah
Motivator

Hi @kaeleyt 

The following will account for no results.  The first append section ensures a dummy row with date column headers based of the time picker (span=1).  This is ensure a result set no matter what you base searches do.  It does expect the date columns to have the same date format, but you could adjust as needed.

... your base searches ... 
| append [ | makeresults
 | addinfo | eval min_max_time=mvappend(info_min_time, info_max_time)
 | mvexpand min_max_time
 | streamstats count
 | eval _time=if(count=1, info_min_time, info_max_time)
 | makecontinuous _time span=1d
 | where isnull(count)
 | eval Time= strftime(_time, "%m-%d-%y")
 | table column Time count
 | fillnull column value="dummy"
 | fillnull count value=0
 | xyseries column Time count
]
| append [ | makeresults | eval column=split("Row 1|Row 2|Row 3|Row 4", "|") | mvexpand column | table column ]
| stats values(*) AS * BY column
| fillnull value=0
| search column="Row*"

The second append is as before and ensures the rows are created etc.

You may want to make this a macro if it works.

Hopefully, this suits your use case better now.

kaeleyt
Path Finder

That got me really close. I tried it over "Last 7 Days" and it gave me 6 days with 9-4-20 missing for some reason. I took your search and changed around some things and got this working:

| makeresults 
| eval count=0
| eval rows=split("Row 1|Row 2|Row 3|Row 4", "|")
| mvexpand rows
| timechart values(count) by rows limit=0
| fillnull value=0
| eval _time= strftime(_time, "%m-%d-%y")
| append 
    [search that may or may not produce results]
| stats max("Row 1") as "Row 1",
max("Row 2") as "Row 2",
max("Row 3") as "Row 3",
max("Row 4") as "Row 4"
by _time

 

In my appended search I called each aggregation "Row 1",  "Row 2", etc. so at the end in the last stats it will pull the highest number for each row and time, so if there are results it will pull the value and if there aren't results it will pull the 0 created in the makeresults search.

For some reason it worked for me when you make the null/filler results first and then append your actual search.

 

@yeahnahthank you for your help!

yeahnah
Motivator

Great to see you arrived at a solution that works for you.

Please mark this answer as solved in case it is useful for other Splunk community members.

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 ...