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
SplunkTrust
SplunkTrust

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
SplunkTrust
SplunkTrust

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
SplunkTrust
SplunkTrust

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!

.conf23 | Get Your Cybersecurity Defense Analyst Certification in Vegas

We’re excited to announce a new Splunk certification exam being released at .conf23! If you’re going to Las ...

Streamline Data Ingestion With Deployment Server Essentials

REGISTER NOW!Every day the list of sources Admins are responsible for gets bigger and bigger, often making the ...

Remediate Threats Faster and Simplify Investigations With Splunk Enterprise Security ...

REGISTER NOW!Join us for a Tech Talk around our latest release of Splunk Enterprise Security 7.2! We’ll walk ...