Is there a way to add an extra row based on a pre-defined list of stores if the stores don't exist in the search results?

[wesbrowntech]

I've searched around and I've been having a hard time finding an answer to this probably due to how I'm phrasing the issue.

We have a search that returns logs with the field Store= in it. After transforming the data we end up with a table that looks like of like this:

Store | Result 1 | Result 2 | Result 3
123 | 4 | 15 | 2
231 | 5 | 5  | 34
987 | 3 | 2 | 1

The issue I'm having is that lets say that Store 987 has 0 results, it won't be returned because there will be no logs to build that row off of. Is there a way to add an extra row based on a pre-defined list of stores if the stores don't exist in the search results?

[stuartidelta01 · Accepted solution]

Add a lookup file - stores.csv - that just lists you store numbers.

then use:

|inputlookup stores.csv | join Store type=outer [search <your-search>] | fillnull

The following search worked for me (I added a csv list of dummy hostname + one that matched my local server name into hosts_text.csv)

|inputlookup hosts_test.csv|join hostname type=outer [search index=_internal |stats count by host|rename host as hostname] | fillnull

Example hosts_test.csv for above search:

hostname
myserver
localhost
notaserver

Results:

count     hostname
4845       myserver
0         localhost
0         notaserver

[wesbrowntech]

This works great but its really slow. I'll poke at it and see if there is a way to speed it up. Thanks very much!