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