topic Re: How to display lookup table records in search table? in Dashboards & Visualizations

How to display lookup table records in search table?

beetlegeuse — Wed, 21 Apr 2021 13:24:53 GMT

I'm sure I'm missing something that's pretty obvious, and I'm hopeful that someone can show me the light. 🙂

I'm running a search that references a lookup table for the search criteria as follows:

index=foo sourcetype=bar [ | inputlookup "cookies.csv" | rename cookie as query | fields query ] | table _time, query, field1, field2

The "cookies.csv" lookup file looks like this:

cookie <-- header name

cookie1

cookie2

cookie3

...

As noted in the SPL, I'm running a text based search using the entries from the lookup file (searching on all cookies present). Once the search is complete, I produce a table with rows reflecting the index time, the matching cookie from the lookup file, and two additional fields for each event returned.

My use of the special sub-search field "query" comes from this Splunk community post:

https://community.splunk.com/t5/Splunk-Search/Subsearch-fields-quot-query-quot-quot-search-quot-How-...

The SPL executes correctly and returns a table with everything I'm expecting EXCEPT the cookie from the lookup file that was matched in the search; that field ("query", since I renamed it) returns as blank in the table.

What do I need to change to see the cookies from the lookup file in the table?

UPDATE: If I replace...

[ | inputlookup "cookies.csv" | rename cookie as query | fields query ]

...with...

[ | inputlookup "cookies.csv" | fields cookie | rename cookie as search | format ]

...I have the same issue. My table shows _time, field1 and field2 for all matching events, but not the cookie entry from the lookup that was used for the match.

Re: How to display lookup table records in search table?

richgalloway — Tue, 20 Apr 2021 16:37:55 GMT

Does the foo index have a field called "query" in it?

Re: How to display lookup table records in search table?

TheFlash — Tue, 20 Apr 2021 17:16:19 GMT

try this ,
index=foo sourcetype=bar [ | inputlookup "cookies.csv" ] | rename cookie as query | table _time, query, field1, field2

Re: How to display lookup table records in search table?

beetlegeuse — Tue, 20 Apr 2021 17:20:25 GMT

No. Using this query...

index=foo | stats values(*) AS * | transpose | table column | rename column AS Fieldnames

...the field "query" does not show up.

Re: How to display lookup table records in search table?

beetlegeuse — Tue, 20 Apr 2021 17:37:40 GMT

Unfortunately, that search does not work. It results in zero events found.

Re: How to display lookup table records in search table?

TheFlash — Tue, 20 Apr 2021 17:44:34 GMT

I tried something like this with my lookup it is working, can you please check whether your time-range picker is right ?

or you can try one more thing , add your lookup to automatic lookup and remove [ inputlookup cookies.csv] from your search query

Re: How to display lookup table records in search table?

beetlegeuse — Tue, 20 Apr 2021 18:41:14 GMT

I'm working through an automatic lookup definition now, but I'm stuck. I want the field in my lookup table (the cookie) to be used as a text match in a search; the automatic lookup assumes that I have a specific event field that I want to align my lookup field with (which I don't).

Re: How to display lookup table records in search table?

scelikok — Wed, 21 Apr 2021 04:50:23 GMT

Hi @beetlegeuse,

You answered no to @richgalloway's question. You do not have a field named query in your index. That is why it is normal that the query field is blank.

I think there is a misunderstanding on subsearch. You have a cookie field in your lookup. What is the field name in your index that you want to search/match with the cookie field in your lookup? It seems it is not "query". If you replace "query" with the field in your index everything will be fine.

Re: How to display lookup table records in search table?

beetlegeuse — Wed, 21 Apr 2021 13:18:17 GMT

My intent is to use the contents of the lookup file as part of a general text based search against all events that fall in line with the index and sourcetype I've defined. For example: If the string "cookie1" appears anywhere in an event (regardless of field), I want that event reflected in my table. The table should reflect all events that contain "cookie1", "cookie2", etc.

My use of the special sub-search field "query" comes from this Splunk community post:

https://community.splunk.com/t5/Splunk-Search/Subsearch-fields-quot-query-quot-quot-search-quot-How-do-I-know/m-p/62422

I'm able to use "query" to successfully perform the search I've detailed above (my table shows _time, field1 and field2 for all matching events as expected), but I'm unable to show the lookup entry that matched the event in my table as a result. So, I suppose the question is: How can I perform the text based search I need and still be able to reference the lookup entries in my table? The use of "query" seems to be restricting my ability to do that.