topic Re: How to show results if 2 values are true in Splunk Search

How to show results if 2 values are true

Dallastek1 — Mon, 13 Nov 2023 22:14:25 GMT

Im trying to get specific results if two values in the same field are true but I keep failing

I want to count the number of times a sc_action=REQ_PASSED when sc_action=REQ_CHALLENGE_CAPTCHA was required

I tried this :

My search | eval activity=if(IN(sc_action, "REQ_CHALLENGE_CAPTCHA", "REQ_PASSED")"passed","captcha") | stats count by activity

I tried if/where and evals, I either get get an error or I get all the results where both are true. Maybe im overthinking it

Re: How to show results if 2 values are true

bowesmana — Mon, 13 Nov 2023 22:50:15 GMT

Your eval is wrong - you don't need IN

search... | eval activity=case(sc_action="REQ_CHALLENGE_CAPTCHA", "captcha", sc_action="REQ_PASSED","passed", true(), sc_action) | stats count by activity

but that will just give you counters of each - are you looking to relate that to a user or IP and should one event follow the other - if so, that's not enough.

Re: How to show results if 2 values are true

Dallastek1 — Tue, 14 Nov 2023 00:08:17 GMT

I may not totally understand how imperva identifies unique events
This query shows alot of confusing results. seems for every event our main site also gets a cs_sessionid which I was led to believe was a unique identifier. AS you can see in the screenshot, the results are kina skewed.
index=imperva sourcetype=imperva:waf (sc_action="REQ_CHALLENGE_CAPTCHA" OR sc_action="REQ_PASSED") s_computername=*
| transaction maxspan=1m startswith="sc_action=REQ_CHALLENGE_CAPTCHA" endswith="sc_action=REQ_PASSED"
| where sc_action="REQ_PASSED" OR sc_action="REQ_CHALLENGE_CAPTCHA"
| eval human_readable_time=strftime(min(_time),"%Y-%m-%d %H:%M:%S")
| mvexpand human_readable_time
| table human_readable_time, s_computername, sc_action, c_ip, cs_sessionid | rename human_readable_time AS Date/Time, s_computername AS "Web Server", sc_action AS "Request Response", cs_sessionid AS "Client Session ID", c_ip AS "client IP"

Re: How to show results if 2 values are true

bowesmana — Tue, 14 Nov 2023 01:04:57 GMT

If you use transaction (which I advise against) you need to correlate with the session id - as you can see in your rows 2 and 3, the session id ending in 93 is out of sync with your rows

Generally the way to find these things is to use something like

search.... | stats min(_time) as min max(_time) as max values(*) as * by cs_sessionid

and in the stats, collect the values you want (instead of values(*) as *)

You won't hit the limitations of transaction with large data sets which silently break your results.