topic Re: For loop within Lookup Table in Splunk Search

For loop within Lookup Table

dlawler1 — Mon, 08 Nov 2021 21:05:18 GMT

Hello!

I have a lookup table that looks like the following:

host	timestamp
host1	10:33
host2	4:24

What I would like to do is "iterate" through the lookup table using the host field for host, and the timestamp for the search. Does anyone have any opinions/thoughts?

Re: For loop within Lookup Table

bowesmana — Tue, 09 Nov 2021 06:33:51 GMT

Can you expand on what you mean by iterate and what you want do during the iteration.

You can use

| inputlookup lookup_name

to collect all the rows from the table, but I am not sure what you are trying to achieve.

Re: For loop within Lookup Table

dlawler1 — Tue, 09 Nov 2021 11:59:39 GMT

@bowesmana wrote:
Can you expand on what you mean by iterate and what you want do during the iteration.

My plan would be to use cell one as a host, and search for the timestamp in cell two.

index=index sourcetype=sourcetype host=<from cell one> "<from cell two>

And then repeat this, for the next row, and so on and so fourth.

In bash/shell it would be pretty easy to accomplish this using the API, but unfortunately I do not have access to the API.

Re: For loop within Lookup Table

PickleRick — Tue, 09 Nov 2021 12:55:42 GMT

There is a solution for that but you shouldn't use it 🙂

But seriously - you can iterate over results of one search and call a subsearch for every row of the base search but it spawns a new search for every row in base search results so it's highly ineffective. And has some more limitations because of subsearch use.

But you might use the subsearch to generate sets of conditions for the base search.

If you have a subsearch returning sets of fields, they results are by default rendered as (pseudocode):

((row1field1name=row1field1value AND row1field2name=row1field2value AND ...) OR (row2field1name=row2field1value AND row2field2name=row2field2value AND ...) OR ...)

So you can just use

[ | inputlookup <yourlookup.csv> ]

to generate set of rules for your search.

You just have to be sure that your subsearch returns proper fields. So if your lookup contains different field names, you might want to | rename them.

Re: For loop within Lookup Table

bowesmana — Tue, 09 Nov 2021 21:02:33 GMT

So, if I understand correctly, you want to get data from an index relating to all hosts in the lookup and then get the timestamp from the lookup.

index=index sourcetype=sourcetype [| inputlookup yourlookup.csv | fields host ] | lookup yourlookup.csv host

What this is doing

Line 1 - search your index

Line 2 - Use a subsearch to add an additional constraint on your line 1 search which is derived from all the hosts in your lookup file.

Line 3 - then for all the events found from the above search, lookup the host in the event from the lookup file and get the timestamp.

I am sure there is more to your needs than just this, but hopefully this will help you get started.