Splunk Search

Why does my AND clause not work?

jip31
Motivator

hi

 

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?

2) 

thanks

Labels (1)
Tags (1)
0 Karma
1 Solution

somesoni2
Revered Legend

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  

View solution in original post

0 Karma

somesoni2
Revered Legend

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
0 Karma

jip31
Motivator

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

 

 

0 Karma

somesoni2
Revered Legend

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
0 Karma

jip31
Motivator

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)

 

0 Karma

somesoni2
Revered Legend

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  

0 Karma

jip31
Motivator

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 

0 Karma

gcusello
Esteemed Legend

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

0 Karma

jip31
Motivator

hi

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)

?? 

0 Karma

gcusello
Esteemed Legend

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

0 Karma

jip31
Motivator

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!

 

0 Karma

gcusello
Esteemed Legend

Hi @jip31,

could you share the full search?

maybe the problem is before.

Ciao.

Giuseppe

0 Karma

jip31
Motivator

Hi

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 

 

 

0 Karma

gcusello
Esteemed Legend

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

0 Karma

jip31
Motivator

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.....

0 Karma
Get Updates on the Splunk Community!

The Splunk Success Framework: Your Guide to Successful Splunk Implementations

Splunk Lantern is a customer success center that provides advice from Splunk experts on valuable data ...

Splunk Training for All: Meet Aspiring Cybersecurity Analyst, Marc Alicea

Splunk Education believes in the value of training and certification in today’s rapidly-changing data-driven ...

Investigate Security and Threat Detection with VirusTotal and Splunk Integration

As security threats and their complexities surge, security analysts deal with increased challenges and ...