topic Re: Need where clause query to be implemented on specific value in Splunk Search

Need where clause query to be implemented on specific value

sahil237888 — Wed, 30 Sep 2020 00:55:56 GMT

Hi, If anyone can help.
Below is my table which represents volume (count) Country wise. But I want to apply filter like -
If only country_code - US and count > 10 or country_code - PH and count > 30 or country_code - NR and count > 10 ,
then only these country_codes should be visible and for rest all with same value should be visible.

City_Code Count
US 10
FN 5
IN 50
PH 30
MN 4
KL 8
NR 11

So far I have used this query :

index="countries" | bucket span=10m _time
| stats count by country_code

Re: Need where clause query to be implemented on specific value

FrankVl — Fri, 14 Jun 2019 13:31:26 GMT

Like this:

index="countries" | bucket span=10m _time
| stats count by country_code
| search (country_code="US" count > 10) OR (country_code="PH" count > 30) OR (country_code="NR" count > 10) OR NOT country_code IN (US,PH,NR)

Note: the IN operator requires Splunk 6.6 or newer.

Re: Need where clause query to be implemented on specific value

jnudell_2 — Wed, 30 Sep 2020 00:56:00 GMT

Hi @sahil237888 ,

I would use a lookup to do this, setting a threshold list for the countries.

Example lookup CSV:



City_Code,Threshold

US,10

PH,30

NR,10

Then you could do the following search:



... [ you search ] ...

| table City_Code Count

| lookup threshold_lookup City_Code OUTPUT Threshold

| fillnull Threshold

| where Count > Threshold

Here is an example of how it would look using dummy events and data:

Re: Need where clause query to be implemented on specific value

FrankVl — Mon, 17 Jun 2019 06:43:45 GMT

Very elegant solution 🙂

Re: Need where clause query to be implemented on specific value

sahil237888 — Mon, 17 Jun 2019 07:49:02 GMT

How can I use * operator with search command or where command - if the country_code is different?

Re: Need where clause query to be implemented on specific value

FrankVl — Mon, 17 Jun 2019 07:52:22 GMT

I don't really understand your question, can you clarify a bit what you mean?