Citry contains 12 names. in result i am able to see only city name with product
if product is zero it is not showing the Citry name
base search |stats count(product) AS Total BY City |fillnull value=0 City
Citry | Total |
citry1 | 1 |
citry5 | 50 |
citry10 | 15 |
expectation
Citry | Total |
citry1 | 1 |
citry2 | 0 |
citry3 | 0 |
citry4 | 0 |
citry5 | 50 |
citry6 | 0 |
citry7 | 0 |
citry8 | 0 |
citry9 | 0 |
citry10 | 15 |
citry11 | 0 |
citry12 | 0 |
The fillnull works for populating columns with missing data when the row exists. Your query will only list Cities for which it finds data. To get data for allCities, you'll need to provide the whole list to Splunk so that even the missing ones show up with 0 count.
Step 1: Build a list of allCities in a lookup table. Say All_Cities.csv. This can be a static CSV or dynamically generated, based on your data.
Step 2: Append data from this lookup to your search and re-build stats. Like this
base search |stats count(product) AS Total BY City
| append [| inputlookup All_Cities.csv | table City | eval Total=0]
| stats max(Total) as Totak by City
fillnull works, the issue is that you don't have any events returned by the stats for all the cities you were expecting - splunk doesn't usually make stuff up unless you tell it to!