Splunk Search

How to take the values from lookup file as an input to a search query

Path Finder

I am trying to take the value of a field from the lookup file and passing that as an input value to a field in my search query. But I am missing something in my query. Due to this I am not getting the expected result. Below is the query which I am trying with:

{
|inputlookup dashboard-lookup.csv |search P=* AND R=* | map [search host=prod* R=$R$ |eval Pro=$P$ ]|stats count by Pro
}

Tried below also but no results

{
|inputlookup dashboard-lookup.csv |table P R | map search="search host=prod* R=$R$ |eval Pro=$P$" |stats count by Pro
}

Could anyone please help me in achieving this

0 Karma

Splunk Employee
Splunk Employee

Can you just try this out:

host=prod* [ | inputlookup dashboard-lookup.csv | fields R | format | table search ] | lookup dashboard-lookup.csv R OUTPUT P as Pro | stats count by Pro

Hope this helps!!!

Path Finder

Hi @rlalwani,

If I use the above query, I am getting results only based on the last product available in my lookup file. But I have some 8 to 10 products in my lookup file and their corresponding 'R' values also available in Splunk events.

The query is grouping only the events of the last Product from the lookup file

0 Karma

Splunk Employee
Splunk Employee

Hey,
So you mean for each R their can be multiple products in the lookup file and you need all of them?

0 Karma

Path Finder

Yes... Under each Product there are 2 to 3 R values are categorised in lookup file. So I want to group all those R from my events under their respective Product

0 Karma

Splunk Employee
Splunk Employee

So if the Product is a multi-valued field you could expand the value in multiple events like below:

host=prod* [ | inputlookup dashboard-lookup.csv | fields R | format | table search ] | lookup dashboard-lookup.csv R OUTPUT P as Pro | mvexpand Pro | stats count by Pro

0 Karma

Splunk Employee
Splunk Employee

You can also add a lookup field extraction like below in props.conf under the related sourcetype/host/source based stanza (Splunk best practices defines use sourcetype based stanza)
LOOKUP-product = dashboard-lookup.csv R OUTPUT P as Pro

And then you can execute the below query:
host=prod* Pro=* | stats count by Pro

If Pro is multi-valued then you could use below:
host=prod* Pro=* | mvexpand Pro | stats count by Pro

0 Karma

Path Finder

Hi @rlalwani,

Thanks for your response. Even after using "mvexpand Pro", I am getting only the last value of Product. May be I have to try something with the props.conf file.

0 Karma

Champion

Try this!

map [search host=prod* R=$R$ |eval Pro=$P$ ]
↓
map search ="search host=prod* R=\"$R$\" |eval Pro=\"$P$\""

Path Finder

If I use this kind of queries, it is working from the search app. But the same thing if we add it in dashboard as panel and when we open the dashboard it is showing as the Panel search is waiting for the input.

Because we are using "$R$" in the query in dashboard panel, so by default it is considered as a token and it is keep on waiting for that tokens input instead of referring to the lookup file

0 Karma

Champion

Try this!

 map search ="search host=prod* R=\"$$R$$\" |eval Pro=\"$$P$$\""
0 Karma

Path Finder

Hi @Hiroshi,

Thanks for your response. Now I am getting the results on my dashboard panel.

0 Karma

SplunkTrust
SplunkTrust

Try this:

host=prod*
[
|inputlookup dashboard-lookup.csv
| rename P as Pro
| fields R Pro
| format
]
| stats count by Pro

0 Karma

Path Finder

Hi @jkat54,

I tried with the above query but no luck. It is giving me 'No results found'.

Actually my search query "host=prod*" have 'R' field with 100+ values in it. My lookup file have 'R' field with selected values. So only those values of 'R' from lookup should be passed to the search query as input. So that the query fetches the results of only those particular values of 'R'.

Then I am taking the 'P' (it is dependent on 'R' which is available only in lookup) field from lookup to group the 'R' values from my search query.

Could you please let me know any other ways to achieve this.

0 Karma