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
Esteemed 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
Esteemed 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
Get Updates on the Splunk Community!

Build Scalable Security While Moving to Cloud - Guide From Clayton Homes

 Clayton Homes faced the increased challenge of strengthening their security posture as they went through ...

Mission Control | Explore the latest release of Splunk Mission Control (2.3)

We’re happy to announce the release of Mission Control 2.3 which includes several new and exciting features ...

Cloud Platform | Migrating your Splunk Cloud deployment to Python 3.7

Python 2.7, the last release of Python 2, reached End of Life back on January 1, 2020. As part of our larger ...