topic Re: How to show null or empty feilds produced by a lookup table in Splunk Search

How to show null or empty feilds produced by a lookup table

jravida — Fri, 13 Mar 2015 21:25:33 GMT

Hi folks,

I'm doing a lookup table (on some data that would take too much time to explain without more confusion), invoked by a stats command.
For simplicity sake let's say it is food items. I would have a lookup table for every item in the store, but only want to see that which has no UPC associated. SO I would run:
... | lookup food2upc.csv itemName | stats list(upcCode) by itemName

I would get a list of all the items, and would see a blank/whitespace for the cells/value where there is no UPC in the database. This query works fine.
I want to further filter the list to only show the items with no UPC, filtering out the ones that do. I've tried many combinations of where,search and can't get any results. Among searches I've tried:
... | lookup food2upc.csv itemName | stats list(upcCode) by itemName | fillnull=blank |where upcCode=blank

---still nothing comes up in the search. Can someone assist?

Re: How to show null or empty feilds produced by a lookup table

maciep — Fri, 13 Mar 2015 23:11:15 GMT

I'm not sure what else you have you tried, but maybe put pipe to fillnull before your stats. Also in your example, isn't the field called list(upcCode) not upcCode after your stats? Meaning, the upcCode field in the where clause doesn't exist anymore at that point?

Re: How to show null or empty feilds produced by a lookup table

stephane_cyrill — Mon, 16 Mar 2015 08:22:02 GMT

Hi ,
I can see that you have misuse your fillnull. try to use

fillnull value=blank

isntead of

fillnull=blank

Re: How to show null or empty feilds produced by a lookup table

stephane_cyrill — Mon, 16 Mar 2015 08:23:59 GMT

... | lookup food2upc.csv itemName | stats list(upcCode) by itemName | fillnull value=blank |where upcCode=blank