topic Re: Use lookup table values as a input for search query and display lookup table values with query manipulated fields in Splunk Search

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

mala_splunk_91 — Tue, 05 Sep 2017 15:23:02 GMT

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

result as :

please, help me to derive query for this.

Thanks
Mala S

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

somesoni2 — Tue, 05 Sep 2017 15:53:20 GMT

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.

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

JDukeSplunk — Tue, 05 Sep 2017 18:16:21 GMT

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.

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

DalJeanis — Tue, 05 Sep 2017 18:28:49 GMT

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

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

mala_splunk_91 — Wed, 06 Sep 2017 08:33:38 GMT

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

Help on this!!

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

mala_splunk_91 — Tue, 29 Sep 2020 15:37:21 GMT

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

pls, check if anything worn in this.

Thanks
Mala

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

mala_splunk_91 — Tue, 29 Sep 2020 15:37:24 GMT

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.