Splunk Search

Fill in 0 if no result is returned

rajnish1202
Explorer

I am showing list of stopped services by host on a dashboard panel. I have 3 servers to show to show stopped services for each server. Results are to be shown as below

Host Services_Stopped
Server1 3
Server2 2
Server3 0

Problem is that my search does not show the row for a server which does not have a stopped service. In above example, my search does not show last row i.e. (Server3 0).

My search is simple as given below

index=my_index host=*  sourcetype=my_source  state=STOPPED | dedup service_name | stats count by host

any help is really appreciated.

Regards,
Rajnish Kumar

Tags (3)
0 Karma
1 Solution

woodcock
Esteemed Legend

You are asking to count zero of something that splunk cannot know that it doesn't have! So without adding more knowledge to the search, it is impossible. What you are asking can be achieved if you have (or can generate) a "Master List" of servers somewhere. Typically this will be done with a CSV that has all the servers. Let us say you have a file called AllMyHosts.csv and your search is My Base Search | stats count by host. Your solution would be like this:

My Base Search | appendpipe [|inputcsv AllMyHosts.csv] | stats count by host | eval count = count - 1

Here is a full description of this approach being used with a second data set as the source for the "Master List" but it is the same thing:

https://answers.splunk.com/answers/306123/i-need-help-filling-null-fields-with-zero.html

Also, I am using appendpipe instead of append in case you have more than 50K hosts. If not, you should use append. Go here for more details:

https://answers.splunk.com/answers/318428/how-can-i-escape-the-50k-subsearch-limit-while-lin.html

View solution in original post

woodcock
Esteemed Legend

You are asking to count zero of something that splunk cannot know that it doesn't have! So without adding more knowledge to the search, it is impossible. What you are asking can be achieved if you have (or can generate) a "Master List" of servers somewhere. Typically this will be done with a CSV that has all the servers. Let us say you have a file called AllMyHosts.csv and your search is My Base Search | stats count by host. Your solution would be like this:

My Base Search | appendpipe [|inputcsv AllMyHosts.csv] | stats count by host | eval count = count - 1

Here is a full description of this approach being used with a second data set as the source for the "Master List" but it is the same thing:

https://answers.splunk.com/answers/306123/i-need-help-filling-null-fields-with-zero.html

Also, I am using appendpipe instead of append in case you have more than 50K hosts. If not, you should use append. Go here for more details:

https://answers.splunk.com/answers/318428/how-can-i-escape-the-50k-subsearch-limit-while-lin.html

View solution in original post

vitoravancini
Loves-to-Learn Lots

I don't seem to have permission to use inputcsv.

 

How would I use the same query hard coding?

 

Regards

0 Karma

rajnish1202
Explorer

Hi woodcock,
Thanks for you response. I am open to use masterlist. Can you please tell me when should I store this csv file? I mean which folder in my app I should put this csv file in?

Thanks,
Rajnish Kumar

0 Karma

woodcock
Esteemed Legend

Just upload it as a Lookup File inside your app using Settings -> Lookups -> Lookup table files -> New.

0 Karma

rajnish1202
Explorer

Thanks Woodcock,
Should I add server list in the csv as given below? Sorry if its a dumb question, I am quite new to this.

Host
Server1
Server2
Server3
Server4

Thanks,
Rajnish Kumar

0 Karma

woodcock
Esteemed Legend

I would do host instead of Host because that is the field that splunk uses. Other than that, it is fine.

0 Karma

rajnish1202
Explorer

Many Thanks Woodcock.

0 Karma

lennys26
Path Finder

Hi Rajnish.

Try playing with fillnull.

fillnull value=0
0 Karma

rajnish1202
Explorer

Hi lennys26,
I tried fillnull already but does not help in my case. In my case entire row(3rd row in my example in question) is not available for a server if any service is not stopped on the same.
I think fillnull works only when I get the server3 listed in first column but there is nota value in Services_Stopped column.

Thanks,
Rajnish

0 Karma

mtranchita
Communicator
0 Karma

rajnish1202
Explorer

Hi mtranchita,
I tried fillnull already but does not help in my case. In my case entire row(3rd row in my example in question) is not available for a server if any service is not stopped on the same.
I think fillnull works only when I get the server3 listed in first column but there is nota value in Services_Stopped column.

Thanks,
Rajnish

0 Karma

mtranchita
Communicator

looking at your base search you could try to reorder it like this:
"index=my_index host=* sourcetype=my_source | stats c(state) by host | search state=STOPPED"
That is a rough cut and could probably be improved, but I think its closer to what you want.

0 Karma

rajnish1202
Explorer

unfortunately it does not work in this case 😞

0 Karma