Splunk Search

TSTATS using a lookup table for a variable?

lennys26
Communicator

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?

Labels (1)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

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

 

View solution in original post

lennys26
Communicator

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

 

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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.

---
If this reply helps you, Karma would be appreciated.
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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
Communicator

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

 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

 

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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

 

---
If this reply helps you, Karma would be appreciated.
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...