Looking for a single result that includes both values of clicked link then added up in a total column
search... | eval clicked=if("Clicked Link"="Yes",1,0),
notclicked=if("Clicked Link"="No",0,1) | stats count by Agency, "Clicked Link"
generates this result
Agency Clicked Link count
1 No 111
1 Yes 10
What id like is a results that looks like this
Agency Clicked Link Count Clicked Link count Total
1 No 10 Yes 111 121
This isn't exactly what you asked for but it is better (and you can modify it to suit if you really would like):
| makeresults
| eval raw="1 No 111
1 Yes 10"
| makemv delim="
" raw
| mvexpand raw
| rex field=raw "^(?<Agency>\S+)\s+(?<Clicked_Link>\S+)\s+(?<count>.*)$"
| fields - _time raw
| rename COMMENT AS "Everything above fakes sample data; everything below is your solution"
| eval {Clicked_Link}=count
| stats first(Yes) AS Yes first(No) AS No BY Agency
| eval Total = Yes + No
that generates a makeresults results error
There are about 100 agencies in the query each has a "Clicked Result" of Yes and a "Clicked Result" of No which should be totaled here is the whole search that i'm starting with, hope this will help
index="testing" * | lookup 1.csv email as Email output type as type one_id two_name one_two_three completion | search one_two_name="*" | rename one_two_three as Agency | eval clicked=if("Clicked Link"="Yes",1,0),
notclicked=if("Clicked Link"="No",0,1) | stats count by "Clicked Link"| addcoltotals count as Total
gets this result
"Clicked Link" count
No 644
Yes 93
737
Your query works without my search, when I incorporate it in the results do not include counts for Yes No or
Total
Thanks for all of your help 🙂
Perhaps "Yes" is "YES" or "No" is "NO" or somehow do not exactly match. As you noted, my solution works for the data that you gave in the question. I cannot give a better answer without you giving me better sample data.
Change lines 2-5 to this:
| eval raw="1 No 111::1 Yes 10"
| makemv delim="::" raw