topic Re: search in foreach subquery in Splunk Search

search in foreach subquery

dmitrymi — Wed, 06 Oct 2021 20:55:32 GMT

I have items visit log index with fields: category, item each event is a visit

In addition, I have an index with all items in the system in form category, items_count

I want to create a timechart of categories: <category> -> <visited items>/<all items> other time

What I did:

index="visited" | eval cat_item = category."/".item | timechart dc(cat_item) by category | foreach * [ search index="cat" category="<<FIELD? >>" | eval <<FIELD>>= '<<FIELD>>'/items_count ]

But this does not work

timechart here creates a table with categories as columns and, each row contains the count of visited items

Now the problem is how I get column name, and value in the subquery. In the examples, the <<FIELD>> is used for the column name and column value alike.

Please help

Re: search in foreach subquery

PickleRick — Wed, 06 Oct 2021 20:28:45 GMT

Are you sure your problem is with fieldnames? I'd say it's with the subsearch itself.

As per the docs, foreach runs a streaming subsearch for each field. But your subsearch starts with the search command which is a generating one.

Re: search in foreach subquery

ITWhisperer — Wed, 06 Oct 2021 20:33:31 GMT

This

index="visited" | eval cat_item = category."/".item | timechart dc(cat_item)

does not give you a column for each cat_item as you seem to be suggesting - it gives you a count of distinct cat_items for each time period.

Re: search in foreach subquery

dmitrymi — Wed, 06 Oct 2021 20:35:34 GMT

I'm not sure, what is the right way to do it? How to select a value for each column using the column name, and update cell value using this

Re: search in foreach subquery

dmitrymi — Wed, 06 Oct 2021 20:49:15 GMT

Sorry my mistake this is actually:

index="visited" | eval cat_item = category."/".item | timechart dc(cat_item) by category

but still not working.

Without foreach the query returns number of items visited by category over time

Re: search in foreach subquery

PickleRick — Thu, 07 Oct 2021 10:25:25 GMT

If you really need a dynamicaly constructed search, try the map command. But I'd rather do a groupped stat (i.e. count by)