- Splunk Answers
- :
- Using Splunk
- :
- Dashboards & Visualizations
- :
- Re: How to display lookup table records in search ...
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark Topic
- Subscribe to Topic
- Mute Topic
- Printer Friendly Page
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
How to display lookup table records in search table?
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:
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.
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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:
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.
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
try this ,
index=foo sourcetype=bar [ | inputlookup "cookies.csv" ] | rename cookie as query | table _time, query, field1, field2
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Unfortunately, that search does not work. It results in zero events found.
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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).
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Does the foo index have a field called "query" in it?
If this reply helps you, Karma would be appreciated.
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
No. Using this query...
index=foo | stats values(*) AS * | transpose | table column | rename column AS Fieldnames
...the field "query" does not show up.
.conf24 | Registration Open!
ICYMI - Check out the latest releases of Splunk Edge Processor
Introducing the 2024 SplunkTrust!
