topic Re: Passing lookup file contents as individual search strings in Splunk Search

Passing lookup file contents as individual search strings

deepdive100 — Mon, 26 Feb 2024 21:46:44 GMT

Lookup file `tenants.csv`

tenant, tenant1, tenant2, tenant3, tenant4,

Desired query

index=index1 (tenant1xxx OR tenant2xxx OR tenant3xxx OR tenant4xxx)

I'm having a tough time getting this work. Trying lookup is not working because I am not searching any existing fields. Subsearching with inputlookup is not working at all, not sure why.
So in a nutshell, I'm trying to inject (not just each value from the lookup file but also appending `xxx`), as an OR list of raw strings. Any ideas?

Re: Passing lookup file contents as individual search strings

PickleRick — Tue, 27 Feb 2024 07:18:33 GMT

You were close.

index=whatever [ | inputlookup lookup.csv
| eval search=tenant."xxx"
| table search ]

Re: Passing lookup file contents as individual search strings

yuanliu — Tue, 27 Feb 2024 00:17:19 GMT

Don't forget to close subsearch 🙂 Here is an alternative to save a command.

index=whatever [ | inputlookup lookup.csv | stats values(eval(tenant."xxx")) as search]

Re: Passing lookup file contents as individual search strings

deepdive100 — Tue, 27 Feb 2024 00:33:54 GMT

This is returning 0 results.

I've checked the permissions and availability of the lookup file, all good.

I've run the desired query explicitly, and it returns many results.

Even this:

index=index1 [ | inputlookup tenants.csv | eval new_field=tenant ```<--- forgoing the append``` | table new_field]

is not returning anything.

Re: Passing lookup file contents as individual search strings

deepdive100 — Tue, 27 Feb 2024 00:52:03 GMT

Further, looking at the job I see this:

litsearch (index=index1 (tenant="tenant1xxx" OR tenant="tenant2xxx" OR tenant="tenant3xxx" OR tenant="tenant4xxx") (splunk_server::splkindx* | fields keepcolorder=t "*" "_bkt" "_cd" "_si" "host" "index" "linecount" "source" "sourcetype" "splunk_server" "new_field"

which is almost what I want. Again, the tenant field does not exist in the original index, I am looking for the explicit string "tenant1xxx" etc.

Re: Passing lookup file contents as individual search strings

yuanliu — Tue, 27 Feb 2024 01:01:30 GMT

You did not get essence of @PickleRick's solution. In subsearch (weirdly enough but documented nontheless), meta-keyword search has a special meaning. You cannot replace it with any other string. (Actually, t here is ONE synonym:-) But @PickleRick forgot to close subsearch. See explanation in my alternative.

Re: Passing lookup file contents as individual search strings

deepdive100 — Tue, 27 Feb 2024 01:13:44 GMT

OK, I found a different thread and can see that I have to use "search" in the eval. Awesome. But now instead of getting '(tenant1xxx OR tenant2xxx OR...)' I am only getting tenant1xxx

Re: Passing lookup file contents as individual search strings

deepdive100 — Tue, 27 Feb 2024 01:15:50 GMT

Yeah I tried your too, no dice. I missed your reply here, but if you look above I am now adding the meta search to the subsearch. The issue is that it is only adding the first lookup value

Re: Passing lookup file contents as individual search strings

yuanliu — Tue, 27 Feb 2024 01:23:00 GMT

To diagnose, run

| inputlookup lookup.csv | stats values(eval(tenant."xxx")) as search | format

This gives you the exact string passed to main search.

Alternatively, run

| inputlookup lookup.csv | fields tenant | eval search = tenant."xxx"

This way, you can see line by line substitution. If not, you need to post output of this diagnostic. (Anonymize as needed but must reproduce structure/characteristics precisely.)

Then, test

| inputlookup lookup.csv | fields tenant | eval search = tenant."xxx" | format

Re: Passing lookup file contents as individual search strings

deepdive100 — Tue, 27 Feb 2024 01:22:32 GMT

Ahhhhh got it, when i added [| format] it worked.

And I'm just now seeing your suggestion @yuanliu , Thanks!

Re: Passing lookup file contents as individual search strings

deepdive100 — Tue, 27 Feb 2024 01:28:46 GMT

well, now I have a new issue. Since the tenant field is changed over to 'search', how can I stats by tenant? Do i just use the lookup file again?

Re: Passing lookup file contents as individual search strings

yuanliu — Tue, 27 Feb 2024 01:35:10 GMT

One problem at a time 🙂 Your ask was free-hand search without matching specific field name. It is perhaps best to close this one and post another question with the need to extract freehand strings based on lookup values? These are very different search techniques. You will need to explain your lookup AND event data more specifically than mock values tenant1 tenant2 tenant3 and xxx. In particular, what does appended "xxx" signify? How would they appear in event data? (Anonymize, but be specific enough for volunteers without intimate knowledge about your data to be helpful.)

Re: Passing lookup file contents as individual search strings

PickleRick — Tue, 27 Feb 2024 07:18:06 GMT

Copy-pasted and missed that closing one. Good catch.