topic Re: How do I also include the values which has stats count 0 in the table ? in Splunk Search

How do I also include the values which has stats count 0 in the table ?

shaal89 — Thu, 23 Mar 2017 13:05:54 GMT

I'm trying to get the usage of some values (say, xyz) by "stats count by xyz" where i am getting the results of xyz which has count greater than 0 like,
xyz | count
nasj | 10
asjn | 40
asjd | 23

Re: How do I also include the values which has stats count 0 in the table ?

DalJeanis — Thu, 23 Mar 2017 14:29:08 GMT

One way to do this is if you pull, from somewhere, a list of all the values of xyz that you always want on the list. Then, you use sum() on a field with either a one (selected records) or a zero (all values to report) and it looks like this...

...your search that gets all xyz records you want to count...
| table xyz | eval mycount=1
| append [...your search that gets ALL xyz values that you want to report... | table xyz | eval mycount=0]
| stats sum(mycount) as count by xyz

So, here's a run-anywhere code sample demonstrating the technique with your fake data...

| makeresults 
| eval xyz="nasj nasj nasj nasj nasj nasj nasj nasj nasj nasj asjn asjn asjn asjn asjn asjn asjn asjn asjn asjn asjn asjn asjn asjn asjn asjn asjn asjn asjn asjn asjn asjn asjn asjn asjn asjn asjn asjn asjn asjn asjn asjn asjn asjn asjn asjn asjn asjn asjn asjn asjd asjd asjd asjd asjd asjd asjd asjd asjd asjd asjd asjd asjd asjd asjd asjd asjd asjd asjd asjd asjd asjd asjd" 
| makemv xyz 
| mvexpand xyz

| table xyz | eval mycount=1
| append 
    [| makeresults | eval xyz="nasj asjn asjd ansj sfdn" | makemv xyz | mvexpand xyz
     | table xyz | eval mycount=0]
| stats sum(mycount) as count by xyz

Re: How do I also include the values which has stats count 0 in the table ?

woodcock — Thu, 23 Mar 2017 15:02:32 GMT

You need source for a canonical list of "all the XYZ things". For simplicity, let's not use xyz but rather host.

Let's assume you have the canonical list in a set of tags, then you can use this search to obtain it:

| rest/servicesNS/-/-/configs/conf-tags 
| search YourTagNameHere=enabled 
| fields title 
| rex field=title mode=sed "s/host=//" 
| rename title AS host

Let's assume it is in a CSV, then you can use this search to obtain it:

| inputcsv MyCSV | table host

In any case, once you have the search that generated the canonical list of hosts, you can do a search like this:

YOUR DATA SEARCH HERE
| append [YOUR SEARCH FOR CANONICAL LIST HERE] 
| stats values(*) AS * BY host

You might start with a tstats search because it is so much more efficient:

| tstats count where index=_* OR index=* BY host sourcetype index 
| append [YOUR SEARCH FOR CANONICAL LIST HERE] 
| stats values(*) AS * BY host

Be aware that if you are doing stats count instead of stats count(something) you will have to do this at the end to get rid of the added non-data list:

| eval count = count - 1