topic Re: Why does my AND clause not work? in Splunk Search

Why does my AND clause not work?

jip31 — Tue, 22 Mar 2022 11:52:00 GMT

I have 2 pb with my eval clause below

1) when I have a look to the events collected, they dont correspond to the domain specified and the url specified

so the sum on the field tpscap is wrong

| eval tpscap =if(domain="stm" AND url="*%g6_%*" OR url="*WS_STOMV2_H55*" AND web_dura > 50, 1, 0) | chart sum(tpscap) as tps

so what is wrong please?

thanks

Re: help on AND clause which doenst works

gcusello — Tue, 22 Mar 2022 10:35:33 GMT

Hi @jip31,

you have to use parenthesis in the condition:

| eval tpscap =if(domain="stm" AND (url="*%g6_%*" OR web_url="*WS_STOMV2_H55*") AND web_dura > 50, 1, 0) | chart sum(tpscap) as tps

Ciao.

Giuseppe

Re: help on AND clause which doenst works

jip31 — Tue, 22 Mar 2022 13:08:06 GMT

sure, it's proper with parenthesis but it's not the problem

the problem is on the field url

If I am doing this I have results

| eval tpscap =if(domain="stm" AND web_dura > 50, 1, 0)

But if am doing

| eval tpscap =if(domain="stm" AND (url="*%g6_%*" OR url="*WS_STOMV2_H55*") AND web_dura > 50, 1, 0)

I have no results

It's the same pb if i put url="*"

| eval tpscap =if(domain="stm" AND url="*" AND web_dura > 50, 1, 0)

following the same principle how is it possible to have a result

| eval errcap =if(web_count >= 1 AND domain="abc", 1, 0)

but that I have no resulst if I add an argument in my clause

| eval errcap =if(web_count >= 1 AND domain="abc" AND web_url="*", 1, 0)

Re: help on AND clause which doenst works

gcusello — Tue, 22 Mar 2022 13:02:28 GMT

HI @jip31,

you have to do two checks:

the fieldname is "url" and not "usi" or "Url", it's case sensitive,
check in the events where the conditions domain="stm" AND web_dura>50 are satisfied if there's the url field, maybe it's in other events to corrlate to the ones with the other conditions.

Ciao.

Giuseppe

Re: help on AND clause which doenst works

jip31 — Tue, 22 Mar 2022 14:08:18 GMT

I am sure about the fieldname syntax which is url and i am sure that the conditions are satisfied!

despite this, when I exceute this, the results is always 0 !

Its incomprehensible

| eval errcap = if((web_error_code=400 OR web_error_code=500 OR web_error_code=503) AND (url="*ws_stomv2_h51*" OR url="*ws_stomv2_h52*" OR url="*ws_stomv2_h53*", 1, 0) | stats sum(errcap) as errcap

If I just dio this, it works!

| eval errcap = if((web_error_code=400 OR web_error_code=500 OR web_error_code=503), 1, 0) | stats sum(errcap) as errcap

So the AND clause dont works!

Re: help on AND clause which doenst works

gcusello — Tue, 22 Mar 2022 14:07:54 GMT

Hi @jip31,

could you share the full search?

maybe the problem is before.

Ciao.

Giuseppe

Re: help on AND clause which doenst works

jip31 — Tue, 22 Mar 2022 14:18:40 GMT

here is

errshare, errcont and errwest return results but no errcap (except if I delete the AND clause and I just keep for example the condition on web_error_code)

`index` (sourcetype="web:requ" OR sourcetype="web:error") earliest=@d+8h latest=@d+14h | bucket _time span=1h | eval Time=strftime(_time,"%H:%M") | eval errcap = if((web_error_code=400 OR web_error_code=500 OR web_error_code=503) AND (web_url="*ws_stomv2_h51*" OR web_url="*ws_stomv2_h52*" ), 1, 0) | eval errshare =if(web_error_count >= 1 AND web_domain="lapte.sharepoint.com", 1, 0) | eval errcont =if(web_error_count >= 1 AND web_domain="lbp.contacts.worldline)", 1, 0) | eval errwest =if(web_error_count >= 1 AND web_domain="ihm", 1, 0) | chart sum(errcap) as "Erreurs CAP", sum(errcont) as "Erreurs CONTACTS", sum(errwest) as "Erreurs W", sum(errshare) as "Erreurs SHA" over Time

Re: help on AND clause which doenst works

gcusello — Tue, 22 Mar 2022 14:38:57 GMT

Hi @jip31,

debug your search deleting one by one rows from the end, finding where is the problem.

I suppose that the problem is in

| eval errcap = if((web_error_code=400 OR web_error_code=500 OR web_error_code=503) AND (web_url="*ws_stomv2_h51*" OR web_url="*ws_stomv2_h52*" ), 1, 0)

In addition, I don't understand the logic of

| bucket _time span=1h | eval Time=strftime(_time,"%H:%M")

but it isn't relevant for the probem.

Ciao.

Giuseppe

Re: help on AND clause which doenst works

jip31 — Tue, 22 Mar 2022 15:24:09 GMT

I have debugged many times

and it seems obvious that the problem comes from

| eval errcap = if((web_error_code=400 OR web_error_code=500 OR web_error_code=503)  AND (web_url="*ws_stomv2_h51*" OR web_url="*ws_stomv2_h52*" ), 1, 0)

.but why.....

Re: Why does my AND clause not work?

somesoni2 — Tue, 22 Mar 2022 15:29:14 GMT

Wild card for eval and where is not "*" (it only works with search command). You should be either be using "like" command (wildcard=%) or match command (wildcard may not be required). Try something like this

| eval tpscap =if(domain="stm" AND (match(url,"%g6_%") OR match(url,"WS_STOMV2_H55") ) AND web_dura > 50, 1, 0) | chart sum(tpscap) as tps OR | eval tpscap =if(domain="stm" AND (like(url,"%%g6_%%") OR like(url,"%WS_STOMV2_H55%" ) AND web_dura > 50, 1, 0) | chart sum(tpscap) as tps

Re: Why does my AND clause not work?

jip31 — Tue, 22 Mar 2022 16:31:45 GMT

thanks but something is wrong because when I run the search the amount of results is anormaly high

and what is all the more strange is that event displayed after running the seatch doesnt correspond to my eval condition!

for example, I have events with web_dura < 50000 and web_domain different to "xx"!

How is it possible?

| eval tpscap = if( web_domain=xx AND like(url,"%ER_%") OR like(url,"%WS_STOMVX_H51%") OR like(url,"%WS_STOMVZ_H52%") AND web_dura > 500000, 1, 0) | chart sum(tpscap) as tps

Re: Why does my AND clause not work?

somesoni2 — Tue, 22 Mar 2022 16:30:15 GMT

You've to use parenthesis else your search criteria will not be applied properly.

| eval tpscap = if( web_domain=xx AND (like(url,"%ER_%") OR like(url,"%WS_STOMVX_H51%") OR like(url,"%WS_STOMVZ_H52%")) AND web_dura > 500000, 1, 0) | chart sum(tpscap) as tps

Re: Why does my AND clause not work?

jip31 — Tue, 22 Mar 2022 17:00:26 GMT

now my result is 0....

| eval tpscap = if(web_domain=xx AND (like(web_url,"%g6_%") OR like(web_url,"%WS_STOMV2_H51%") OR like(web_url,"%WS_STOMV2_H52%") OR like(web_url,"%WS_STOMV2_H53%") OR like(web_url,"%WS_Q4M/S4M/%")) AND web_dura > 10, 1, 0) | stats sum(tpscap)

Re: Why does my AND clause not work?

somesoni2 — Tue, 22 Mar 2022 17:14:30 GMT

Just make sure order or conditions (or the conditions itself) are correct. Your current condition makes tpscap=1 if web_domain has certain value AND web_dura>10 + web_url is in certain format. It'll set tpscap=1 only if all conditions are met. Try to add a table command before your eval and manually check if the value is being set correctly.

| table web_domain web_url web_duration | eval tpscap = if(web_domain=xx AND (like(web_url,"%g6_%") OR like(web_url,"%WS_STOMV2_H51%") OR like(web_url,"%WS_STOMV2_H52%") OR like(web_url,"%WS_STOMV2_H53%") OR like(web_url,"%WS_Q4M/S4M/%")) AND web_dura > 10, 1, 0)

_domain

Re: Why does my AND clause not work?

jip31 — Tue, 22 Mar 2022 17:42:14 GMT

unfortunately it doenst works 😥

in fact, I deleted this condition which not mandatory

web_domain=xx

et now my results seems to be coherent....

many thanks somesoni