TSTATS using a lookup table for a variable?

[lennys26]

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:

| inputlookup lookuptable.csv
| sort 10 -dm
| table oper, dm
| transpose 10
| rename "row "* AS "value_in*"
| eval top1=value_in1

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):

'''~ Set Variable top1 ~'''
| inputlookup lookuptable.csv 
| sort 10 -dm
| table oper, dm 
| transpose 10 
| rename "row "* AS "value_in*"
| eval top1=value_in1
'''~ Use Variable ~'''
| tstats count as count where index="myindex" id=top1 by _time CAUSE_VALUE span=5m 
| timechart sum(count) as total_count span=5min

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?

[lennys26]

@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
...

OR

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

[richgalloway]

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.

[ITWhisperer · Accepted solution]

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

| tstats count as count where index="titan" sourcetype="titan:cdr*" ROUTING_CDN!=BA* REL_CAUSE=* [| inputlookup lookuptable.csv 
  | sort 10 -dm
  | head 1 
  | rename oper as id
  | fields id
  | format ] ...

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" ) )

[lennys26]

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

[ITWhisperer]

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?

| tstats count as count where index="myindex" [ | inputlookup lookuptable.csv 
  | sort 10 -dm
  | head 1 
  | rename oper as id
  | fields id
  | format ] by _time CAUSE_VALUE span=5m 
| timechart sum(count) as total_count span=5min

[richgalloway]

Try using a subsearch to set the variable.

| tstats count as count where index="myindex" [ | inputlookup lookuptable.csv 
  | sort 10 -dm
  | table oper, dm 
  | transpose 10 
  | rename "row "* AS "value_in*"
  | eval top1=value_in1
  | return id=top1 ] by _time CAUSE_VALUE span=5m 
| timechart sum(count) as total_count span=5min