Splunk Search

Use lookup table values as a input for search query and display lookup table values with query manipulated fields

mala_splunk_91
Explorer

Hi
I have a lookup table with the field (indexname). I want to use each lookup table field (indexname) values as a input to index in the search query.
so each time , query should pick one value from lookup table sequentially and execute search and display results as new column in same lookup table.

For example:

mylookuptable.csv

alt text

result as :
alt text

please, help me to derive query for this.

Thanks
Mala S

Tags (1)
0 Karma

DalJeanis
SplunkTrust
SplunkTrust

It is generally not a good idea in Splunk to think in terms of "sequentially" doing anything. Usually you can code a search so that all the searching happens at the same time, or in exactly two phases, rather than one search per input record.

Let's suppose that you have a file which has the index name and a value for another field, fieldA. In that situation, doing this...

| inputcsv myindexandfieldA.csv | table Indexname fieldA | rename Indexname as index | format

... creates a single field called "search" that contains this...

(  ( index="index1" AND fieldA="value1" ) OR  ( index="index2" AND fieldA="value2" ) OR .... )

If you include that all within square braces at the beginning of your search, then it will bring back all the relevant records for processing. (note - the word "format" is optional if the rest of the code is inside of square braces.

   [| inputcsv myindexandfieldA.csv | table Indexname fieldA | rename Indexname as index | format]
    | stats sum(someotherfield) as manipulatedresult by index fieldA
    | table index fieldA manipulatedresult
0 Karma

mala_splunk_91
Explorer

Thanks for suggestion.
Here is my query. But I'm not able to get result.

[|inputlookup mylookuptable.csv | table Application Normal_Traffic_status| rename Application as index format]|search host=xxx* |stats count as RR by index|eval TrafficStatus=if(RR>1000,"Reston","Chicago")|table index TrafficStatus Normal_Traffic_status

pls, check if anything worn in this.

Thanks
Mala

0 Karma

mala_splunk_91
Explorer

In addition to above query,
I want "Application" fields values to be added in search, but not "Normal_Traffic_status" field values.
As a result , I should get "Application", Normal_Traffic_status" ,"TrafficStatus " fields in a table.

0 Karma

JDukeSplunk
Builder

So I have a working example of this

The PegaVerbose.csv looks like this

PegaAlertNumber PegaAlertV PegaLevel
PEGA0001 PEGA0001-HTTP interaction time exceeds limit Medium
PEGA0002 PEGA0002-Commit operation time exceeds limit High
PEGA0003 PEGA0003-Rollback operation time exceeds limit Medium
PEGA0004 PEGA0004-Quantity of data received by database query exceeds limit High
PEGA0005 PEGA0005-Query time exceeds limit Medium

And the search I use is

index=application sourcetype=Pega:Prod (PegaAlert=*) |lookup PegaVerbose.csv PegaAlertNumber as PegaAlert OUTPUT PegaAlertNumber PegaAlertV PegaLevel

It matches up an event in the actual indexed data (the field named PegaAlert) with PegaAlertNumber in the csv, and then adds two new fields PegaAlertV PegaLevel that are available then for whatever you want.

We also use this app for editing lookup files.
https://splunkbase.splunk.com/app/1724/

Good luck.

alt text

0 Karma

somesoni2
Revered Legend

On top of my mind, you can run a search for all indexes of the lookup table and display final result like this:-

[|inputlookup mylookuptable.csv | table Indexname | rename Indexname as index]
| your search to generate required statistics, make sure to include field 'index'
| table index anyotherfield

You can get better suggestions if you could share your actual query.

0 Karma

mala_splunk_91
Explorer

Here is my query, I need to check TrafficStatus for each Application. When i execute this query am not getting expected result.

[|inputlookup mylookuptable.csv | table Application| rename Application as index1] |search index=$index1$ host=XXXX* | stats count as RR| eval TrafficStatus=if(RR>1000,"X1","X2")|table index1 TrafficStatus

Help on this!!

0 Karma
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!