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

[shaal89]

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

but i m also pretty sure where some values of xyz also has count 0. how do i get that ? should be like,
xyz | count
nasj | 10
asjn | 40
asjd | 23
ansj | 0
sfdn | 0

[woodcock]

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

[DalJeanis]

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