Dashboards & Visualizations

How to display lookup table records in search table?

beetlegeuse
Explorer

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.

Labels (1)
Tags (3)
0 Karma

scelikok
Champion

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.

 

If this reply helps you an upvote is appreciated.

beetlegeuse
Explorer

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-...

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.

0 Karma

TheFlash
Path Finder

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

0 Karma

beetlegeuse
Explorer

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

0 Karma

TheFlash
Path Finder

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

0 Karma

beetlegeuse
Explorer

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).

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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

---
If this reply helps you, an upvote would be appreciated.
0 Karma

beetlegeuse
Explorer

No. Using this query...

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

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

0 Karma
Take the 2021 Splunk Career Survey

Help us learn about how Splunk has
impacted your career by taking the 2021 Splunk Career Survey.

Earn $50 in Amazon cash!