topic Re: using 2 stats queries in one result in Splunk Search

using 2 stats queries in one result

jdepp — Fri, 20 Jan 2017 10:17:42 GMT

I have tried multiple ways to do this including join, append but in each case all I get is one column result being displayed. I asked a similar but more difficult question related to dupes but the counts are still off so I went with the simpler query option.

source="/logs.stats/tcp/1000" message_type="Log" | bucket _time span=day | stats count(log_id) as totalCount by _time | stats dc(log_id) as nonduplicates by _time | eval dupCount = totalCount - nonduplicates | table nonduplicates uniqueCount

Please assist with this as I did not realize how difficult some of these panel creations and queries can be.

Re: using 2 stats queries in one result

gcusello — Fri, 20 Jan 2017 10:28:39 GMT

Hi jdepp,
you should try the appendcols command:

source="/logs.stats/tcp/1000" message_type="Log" 
| bucket _time span=day 
| stats count(log_id) as totalCount by _time 
| appendcols [ search 
   source="/logs.stats/tcp/1000" message_type="Log" 
   | bucket _time span=day 
   | stats dc(log_id) as nonduplicates by _time
   ]
| eval dupCount = totalCount - nonduplicates 
| table nonduplicates uniqueCount

in addition I'd use timechart instead stats command

source="/logs.stats/tcp/1000" message_type="Log" 
| bucket _time span=day 
| timechart count(log_id) as totalCount
| appendcols [ search 
   source="/logs.stats/tcp/1000" message_type="Log" 
   | bucket _time span=day 
   | timechart dc(log_id) as nonduplicates
   ]
| eval dupCount = totalCount - nonduplicates 
| table nonduplicates uniqueCount

Bye.
Giuseppe

Re: using 2 stats queries in one result

jdepp — Fri, 20 Jan 2017 10:37:45 GMT

thanks appreciate the response. Tried the first one and still getting only one column value

nonduplicates uniqueCount
37694

Re: using 2 stats queries in one result

gcusello — Fri, 20 Jan 2017 10:50:34 GMT

sorry, I was wrong| use appendpipe!
otherwise there is also join but I usually am trying to avoid this command.
Bye.
Giuseppe

Re: using 2 stats queries in one result

somesoni2 — Sat, 21 Jan 2017 21:31:58 GMT

Try like this. Since the base search and by clause in stats is same for both your aggregation, you just keep them in one search like this.

 source="/logs.stats/tcp/1000" message_type="Log" | bucket _time span=day | stats count(log_id) as totalCount dc(log_id) as nonduplicates by _time | eval dupCount = totalCount - nonduplicates | table nonduplicates uniqueCount

Re: using 2 stats queries in one result

opaaa — Mon, 14 Oct 2019 16:04:27 GMT

Thanks somesoni2!
It worked!

Re: using 2 stats queries in one result

interloper — Fri, 17 Dec 2021 20:46:40 GMT

This helped me too! Thanks!