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
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
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
I don't seem to have permission to use inputcsv.
How would I use the same query hard coding?
Regards
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
Just upload it as a Lookup File
inside your app using Settings
-> Lookups
-> Lookup table files
-> New
.
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
I would do host
instead of Host
because that is the field that splunk uses. Other than that, it is fine.
Many Thanks Woodcock.
Hi Rajnish.
Try playing with fillnull.
fillnull value=0
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
I think you want the fill null command
http://docs.splunk.com/Documentation/Splunk/6.3.0/SearchReference/Fillnull
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
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.
unfortunately it does not work in this case 😞