topic Re: How do you return a conditional count and grand-total in a query? in Splunk Search

How do you return a conditional count and grand-total in a query?

skribble5 — Wed, 19 Dec 2018 16:30:57 GMT

Hi all,

Novice here. I have two separate queries that are doing a simple calculation each, but I would like to combine them. What would be the best way to do this?

The first query returns the distinct number of users within a data source:

Here is the result which says that the campaign "DM664023" had 297 recipients. The recipients are in the field "user_only"

The query used to get to that result is:

sourcetype="xxx" source="yyy" 
campaign_delivery="DM664023"
| stats dc(user_only) as distinctRecipients by campaign_delivery

Then, the second query returns the number of users who have more than one record in the data-source. If a user only has 1 record in the data source, then that person only access the campaign once. If the user has > 1 record in the data source, then that person revisited the campaign.

The second query returns the number of revisits. Here is the result which says that the campaign DM664023 had 117 revisits.

The query used to get to second result is:

sourcetype="xxx" source="yyy" 
campaign_delivery="DM664023"
| stats count by user_only | where count > 1
| chart count as "Revisits"

Ideally, I want one search which returns the following:

"campaign" | "# total ppl who accessed the campaign" | "number of people who accessed campaign more than once | % of people who revisited campaign
DM664023 | 297 | 117 | 39.39%
campaign2 | x | y | y/x
campaign3 | a | b | b/a

Any help will be greatly appreciated!

Thanks!

Re: How do you return a conditional count and grand-total in a query?

Vijeta — Wed, 19 Dec 2018 17:05:14 GMT

Try this if it works-

sourcetype="xxx" source="yyy" 
 campaign_delivery="DM664023"
 | eventstats count as revisit by user_only | stats dc(user_only) as distinctRecipients , count(eval(revisit>1)) as Revisit by campaign_delivery| eval perc= Revisit/distinctRecipients*100 | table campaign_delivery Revisit distinctRecipients perc

Re: How do you return a conditional count and grand-total in a query?

skribble5 — Wed, 19 Dec 2018 19:12:06 GMT

Thanks Vijeta for coming back to me. I ran this query and I am getting the correct grand total but the revisit number is much larger than expected.

I get the 297 for the grand total but 247 for revisits which is too high. I am expecting around 117.

Any idea why?

Re: How do you return a conditional count and grand-total in a query?

Vijeta — Wed, 19 Dec 2018 19:25:18 GMT

Try dedup user_only before stats command

Re: How do you return a conditional count and grand-total in a query?

skribble5 — Wed, 19 Dec 2018 19:38:48 GMT

That worked beautifully. Thank you so much!