variable where clause

[lain179]

Hi,

I need to set where clause based on certain condition. For example, if value=a, then where should be x>1. If value=b, then where clause should have x>100. If value=c, then x>1000, etc.

So I did something like:

eval condition=if(value=a,x>1,if(value=b,x>100,x>1000)) | stats values(blahblah) | where condition

As expected, that doesn't work =D

Please help and let me know how I can set up variable where clause.

Thanks!

[sideview · Accepted solution]

just move the conditional logic into the eval and have the eval create a field whose value is 1 or 0. then your where clause can just check for 1.

Like so:

eval is_match=case(value=="a" AND x>1,1,value=="b" AND x>100,1,x>1000,1,1==1,0) | stats values(blahblah) | where is_match="1"

[lain179]

Both of you are correct. It was a stupid typo. Here is my new search and it works!!! Thanks guys.

sourcetype="WMI*Security" Type="Audit Failure" Account_Name=* AND NOT (Account_Name=@@*) AND Client_Host=$click.value$ | eval status= if(len(Account_Name)=3, "InternalCount", if(like(Account_Name,"%admin%"),"AdminCount","ClientCount")) | stats values(Account_Name) values(status) as status by _time | where status="$click.name2$"

[aholzer]

Make sure that you list the fields in the original search that you will need in the drilldown. Otherwise your parent module won't pass down the fields.

Example:
Parent:
search "blah" | fields firstname, age
Drilldown:
search firstname=$click.name1$ AND lastname=$click.name2$ | table firstname, lastname, age

The above won't work, because your parent module will not pass the "lastname" field to the child module.

To make it work just include all the fields that you will need downstream in your parent module.

[sideview]

Run this same search, but end it after the | stats values(Account_Name) I'm not sure what you think stats values() does, but it doesn't do that. 😃 after that stats vlaues clause you'll have exactly one row, with exactly one field, whose field name is "values(AccountName)". So nothing after that will work as you expect. Also the extra | where conditional | clause looks like a typo.

[lain179]

My search result

The job appears to have expired or has been canceled. Splunk could not retrieve data for this search.

sourcetype="WMI*Security" Type="Audit Failure" Account_Name=* AND NOT (Account_Name=@@*) AND Client_Host=HL112SPRAX04| stats values(Account_Name) | where conditional | eval conditional = case(ClientCount = InternalCount AND len(Account_Name)=3, 1,ClientCount=AdminCount AND like(Account_Name,"%admin%"),1,ClientCount=ClientCount AND len(Account_Name)!=3,1,1==1,0) | stats values(Account_Name) | where conditional="1"

[lain179]

May be I am doing something wrong:

My search:

sourcetype="WMI*Security" Type="Audit Failure" Account_Name=* AND NOT (Account_Name=@@*) AND Client_Host=$click.value$| stats values(Account_Name) | where conditional | eval conditional = case($click.name2$ = InternalCount AND len(Account_Name)=3, 1,$click.name2$=AdminCount AND like(Account_Name,"%admin%"),1,$click.name2$=ClientCount AND len(Account_Name)!=3,1,1==1,0) | stats values(Account_Name) | where conditional="1"