Splunk Search

using 2 stats queries in one result

jdepp
Path Finder

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.

interloper
Engager

This helped me too! Thanks!

0 Karma

somesoni2
Revered Legend

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

opaaa
Engager

Thanks somesoni2!
It worked!

0 Karma

gcusello
Legend

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

0 Karma

jdepp
Path Finder

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

nonduplicates uniqueCount
37694

0 Karma

gcusello
Legend

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

0 Karma