topic Re: How to append a value from lookup file to the event based on some condition in Splunk Search

How to append a value from lookup file to the event based on some condition

sangs8788 — Fri, 18 May 2018 09:05:06 GMT

I have a lookup file in below format
Product|R
AAAA|/ffff/*

I have some events i like R="/fff/abc" and some like R="/ffff/xyz.jsp"

Using this query i am able to fetch R counts
index=prod* |search [|inputlookup product-dashboard-lookup.csv |fields R ]|stats count as Rcount by R

Result for the above query is
R | Rcount
/fff/abc|10
/fff/xyz.jsp | 10

But i would like to get by Product instead of R something like below

AAAA | 20

How do i achieve this ?

Re: How to append a value from lookup file to the event based on some condition

HiroshiSatoh — Fri, 18 May 2018 09:26:05 GMT

Try this!

index=prod*  [|inputlookup product-dashboard-lookup.csv |fields R ]
|lookup product-dashboard-lookup.csv R
|stats count as Rcount by ProductName

Re: How to append a value from lookup file to the event based on some condition

sangs8788 — Fri, 18 May 2018 12:39:26 GMT

Doesnt work since the R has wildcard in lookup file whereas the events contain the actual R.

Re: How to append a value from lookup file to the event based on some condition

HiroshiSatoh — Fri, 18 May 2018 15:06:16 GMT

You can use wild card for LOOKUP.
https://answers.splunk.com/answers/52580/can-we-use-wildcard-characters-in-a-lookup-table.html

If the number of cases is small, you can also use MAP.
| inputlookup product-dashboard-lookup.csv
| map [search index=prod* R=$R$|eval ProductName=$ProductName$]
| stats count as Rcount by ProductName
※We do not consider duplication and number limit.

It is complicated when there are many cases.
Please make your own with reference to the link below.
https://answers.splunk.com/answers/595766/need-to-display-zero-if-count-is-zero-for-data-tha.html

Re: How to append a value from lookup file to the event based on some condition

somesoni2 — Fri, 18 May 2018 15:46:28 GMT

Wildcard lookup is the way to go here.

Re: How to append a value from lookup file to the event based on some condition

sangs8788 — Mon, 21 May 2018 10:03:17 GMT

Map does work. But the problem i am facing now is i am unable to add this as one of the panel in the Dashboard. It says "Search is waiting for Input". I guess its becuase of the dynamic parameter passed. How do i make the search as part of a Dashboard ?

Re: How to append a value from lookup file to the event based on some condition

HiroshiSatoh — Wed, 23 May 2018 01:28:15 GMT

Please tell us the search sentences and tokens you are using.

Re: How to append a value from lookup file to the event based on some condition

sangs8788 — Mon, 28 May 2018 07:29:31 GMT

Below is the query added as a panel to dashboard,

The dashboard contains "Time" as input panel.

Re: How to append a value from lookup file to the event based on some condition

HiroshiSatoh — Mon, 28 May 2018 09:35:19 GMT

I made a grammar mistake. Also escape the double quotes.

|inputlookup product-dashboard-lookup.csv |search Product=* AND R=*
| map search="search host=prod* R=\"$R$*\" |eval Product=\"$Product$\""
|stats count by Product

Re: How to append a value from lookup file to the event based on some condition

sangs8788 — Mon, 28 May 2018 09:51:13 GMT

Still no luck. It is still waiting for input

Re: How to append a value from lookup file to the event based on some condition

sangs8788 — Mon, 28 May 2018 09:52:16 GMT

Individually both query works. But it doesnt work when added as a panel in a dashboard