topic Re: question on search query in Splunk Search

question on search query

roayers — Thu, 25 May 2017 20:44:03 GMT

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

Re: question on search query

woodcock — Thu, 25 May 2017 21:07:18 GMT

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

Re: question on search query

roayers — Tue, 29 Sep 2020 14:12:22 GMT

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

Re: question on search query

woodcock — Thu, 25 May 2017 22:42:34 GMT

Change lines 2-5 to this:

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

Re: question on search query

roayers — Fri, 26 May 2017 04:39:36 GMT

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 🙂

Re: question on search query

woodcock — Fri, 26 May 2017 16:22:58 GMT

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.