Splunk Search

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
Engager

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?

0 Karma
1 Solution

stuartidelta01
Path Finder

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

View solution in original post

stuartidelta01
Path Finder

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
Engager

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!

0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...