topic TSTATS using a lookup table for a variable? in Splunk Search

TSTATS using a lookup table for a variable?

lennys26 — Mon, 13 Mar 2023 13:55:23 GMT

I am building a query where I want to use a top 10 list of values from a lookup table, and then run a search against those entries (each entry in a different query).

The basic search is something like this:

| tstats count as count where index="myindex" id="some id value" by _time CAUSE_VALUE span=5m | timechart sum(count) as total_count span=5min

The query in the lookup table to provide the variable for the ID is something like this:

TSTATS needs to be the first statement in the query, however with that being the case, I cant get the variable set before it.

The perfect query should be something like this, however it is not (because of the above):

I did read some similar questions where it was suggested to use |where id= <whatever> but that doesn't work in my case because of the TSTATS.

Any suggestions?

Re: TSTATS using a lookup table for a variable?

richgalloway — Mon, 13 Mar 2023 15:37:03 GMT

Try using a subsearch to set the variable.

Re: TSTATS using a lookup table for a variable

ITWhisperer — Mon, 13 Mar 2023 12:35:58 GMT

Your search is a bit confusing but seems to be just using the top value and I am presuming that you want the id to match the oper from the sorted list?

Re: TSTATS using a lookup table for a variable?

lennys26 — Mon, 13 Mar 2023 15:01:18 GMT

@ITWhisperer , @richgalloway . Thanks -- I do believe that the subsearch is the answer, however both of the queries only insert the value, but do not have the fieldname (id).

In the TSTATS I need to have an id=id (@ITWhisperer's solution) or an id=top1 ( @richgalloway's solution).

When I try either of the below, it does not work.

| tstats count as count where index="titan" sourcetype="titan:cdr*" ID=top1 ROUTING_CDN!=BA* REL_CAUSE=* [| inputlookup ...

| tstats count as count where index="titan" sourcetype="titan:cdr*" ID=id ROUTING_CDN!=BA* REL_CAUSE=* [| inputlookup ...

Re: TSTATS using a lookup table for a variable?

ITWhisperer — Mon, 13 Mar 2023 15:35:53 GMT

Neither of these are quite the same as @richgalloway and I showed. The subsearch needs to be inserted so that it is part of the where clause

The format command effectively expands the rows and fields into an expression like this

( ( fieldA="Row 1" AND fieldB="Row 1" ) OR ( fieldA="Row 2" AND fieldB="Row 2" ) OR ( fieldA="Row 3" AND fieldB="Row 3" ) OR ( fieldA="Row 4" AND fieldB="Row 4" ) OR ( fieldA="Row 5" AND fieldB="Row 5" ) )

Re: TSTATS using a lookup table for a variable?

richgalloway — Mon, 13 Mar 2023 15:37:59 GMT

If the field name in the subsearch doesn't match that in the index then the return command can be used to create an alias. See my modified answer.

Re: TSTATS using a lookup table for a variable?

lennys26 — Tue, 14 Mar 2023 10:46:08 GMT

Thanks @ITWhisperer , @richgalloway. Karma to you both.