topic Re: Multiple 'Where' conditions in Splunk Search

Multiple 'Where' conditions

sweety1309 — Tue, 27 Oct 2020 04:38:01 GMT

Hi I have the below query.But its output is "no results found".I dont know what mistake am I making.Please help

index="entab_due" Session=2019 ClassName="* *"
| join type=outer AdmissionNo, FeeInstallmentName, Session
[search index="entab_collection"]
| eval start = strptime(DueDate, "%d/%m/%Y")
| eval end = strptime(RecDate, "%d/%m/%Y")
| eval duration = round((end-start)/86400)
| where (duration > 45 and duration <= 75) AND (duration > 105 and duration <= 120) AND (duration > 120 and duration <= 180) AND (duration > 180)
| table AdmissionNo,StudentName

Re: Multiple 'Where' conditions

renjith_nair — Tue, 27 Oct 2020 05:30:11 GMT

Possibly conditions in the where clause is negating each other.

What's your expected result?

Re: Multiple 'Where' conditions

danspav — Tue, 27 Oct 2020 05:33:42 GMT

Hi @sweety1309

I think the issue is with the where clause.

Say the duration is 108.

The where clause will not match on: (duration > 45 and duration <= 75)

But will match on: (duration > 105 and duration <= 120)

However, the where clause uses AND between these terms, so the duration must be both less than 75 AND greater than 105.

Try replacing your ANDs with ORs:

| where (duration > 45 AND duration <= 75) OR (duration > 105 AND duration <= 120) OR (duration > 120 AND duration <= 180) OR (duration > 180)

Seeing as you don't use the duration field, you could simplify it further:

| where (duration > 45 AND duration <= 75) OR (duration > 105)

Re: Multiple 'Where' conditions

sweety1309 — Tue, 27 Oct 2020 05:55:56 GMT

I need all data which is available in all three durations set

Re: Multiple 'Where' conditions

renjith_nair — Tue, 27 Oct 2020 06:46:46 GMT

try OR instead of AND

Re: Multiple 'Where' conditions

sweety1309 — Tue, 27 Oct 2020 10:24:40 GMT

I need common data which occurs in all the duration set thats why i m using AND here

Re: Multiple 'Where' conditions

sweety1309 — Tue, 27 Oct 2020 10:26:21 GMT

but OR will not give me the common data which occurs in all the duration set.I need common data

Re: Multiple 'Where' conditions

renjith_nair — Wed, 28 Oct 2020 04:13:48 GMT

As mentioned earlier, conditions are negating each other.

For e.g. take first and last condition

sample adata :

duration = 1, 2,55,160,180

condition:

where (duration > 45 and duration <= 75)

AND (duration > 180)

In the above case, first condition will result in 55 and will be negated by AND in the second condition which > 180. So you wont get any result

sample search

|makeresults|eval duration="1 2 55 160 180 200"|makemv duration|mvexpand duration | where (duration > 45 and duration <= 75) OR (duration > 105 and duration <= 120) OR (duration > 120 and duration <= 180) OR (duration > 180)

If this is not your requirement, please provide sample input duration and expected output