fillnull is notworking

[abdul]

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

[somesoni2]

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

[ITWhisperer]

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!