question on search query

[roayers]

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

[woodcock]

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

[roayers]

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

[roayers]

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 🙂

[woodcock]

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.

[woodcock]

Change lines 2-5 to this:

| eval raw="1 No 111::1 Yes 10"
| makemv delim="::" raw