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
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!!!
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
Hey,
So you mean for each R their can be multiple products in the lookup file and you need all of them?
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
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
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
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.
Try this!
map [search host=prod* R=$R$ |eval Pro=$P$ ]
↓
map search ="search host=prod* R=\"$R$\" |eval Pro=\"$P$\""
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
Try this!
map search ="search host=prod* R=\"$$R$$\" |eval Pro=\"$$P$$\""
Hi @Hiroshi,
Thanks for your response. Now I am getting the results on my dashboard panel.
Try this:
host=prod*
[
|inputlookup dashboard-lookup.csv
| rename P as Pro
| fields R Pro
| format
]
| stats count by Pro
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.