Need where clause query to be implemented on specific value

[sahil237888]

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

[jnudell_2]

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:

[FrankVl]

Very elegant solution 🙂

[FrankVl · Accepted solution]

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.

[sahil237888]

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

[FrankVl]

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