Splunk Search

How to combine two searches into one table using count twice

aking76
Path Finder

I have two searches, one getting the current connections and the other getting an average. I'm trying to grab the fields from both and combine them into one table. Both searches work by themselves, however, it looks like something is off when it comes to the count field.

When I run them by themselves, I can get the current_count or avg_count, when they run together, it doesn't display the values for these fields.

index=network* (src_ip = 10.0.0.0/8 OR src_ip=172.16.0.0/12 OR src_ip=192.168.0.0/16) AND (dest_ip=10.0.0.0/8 OR dest_ip=172.16.0.0/12 OR dest_ip=192.168.0.0/16) dest_port=* action=allowed
 | bin _time span=15m
 | eventstats count BY src_ip dest_ip _time
 |rename count AS current_count
 | rename src_ip as Source_ip, dest_ip AS Destination_ip, count AS curr_count
 | eventstats first(current_count) AS Current_Connections BY Source_ip Destination_ip 

|append
[search earliest=-24h latest=+23h
| bin _time span=15m
|eventstats count BY src_ip dest_ip _time
|rename count AS avg_count
|eventstats avg(avg_count) BY src_ip dest_ip AS average_count

]
table *
0 Karma
1 Solution

woodcock
Esteemed Legend

Do it in a single search like this:

index=network* (src_ip = 10.0.0.0/8 OR src_ip=172.16.0.0/12 OR src_ip=192.168.0.0/16) AND (dest_ip=10.0.0.0/8 OR dest_ip=172.16.0.0/12 OR dest_ip=192.168.0.0/16) dest_port=* action=allowed
| bin _time span=15m 
| stats count AS current BY src_ip dest_ip _time 
| sort 0 - _time 
| streamstats dc(_time) AS which 
| eval which=if(which==1, "CURRENT", "PAST") 
| eventstats avg(current) AS avg BY src_ip dest_ip 
| where which=="CURRENT"

View solution in original post

woodcock
Esteemed Legend

Do it in a single search like this:

index=network* (src_ip = 10.0.0.0/8 OR src_ip=172.16.0.0/12 OR src_ip=192.168.0.0/16) AND (dest_ip=10.0.0.0/8 OR dest_ip=172.16.0.0/12 OR dest_ip=192.168.0.0/16) dest_port=* action=allowed
| bin _time span=15m 
| stats count AS current BY src_ip dest_ip _time 
| sort 0 - _time 
| streamstats dc(_time) AS which 
| eval which=if(which==1, "CURRENT", "PAST") 
| eventstats avg(current) AS avg BY src_ip dest_ip 
| where which=="CURRENT"

aking76
Path Finder

Can you perhaps help me understand the last part of the search? I would like to learn what's happening here. I really appreciate the help.

| eval which=if(which==1, "CURRENT", "PAST") --Where is the "CURRENT" and "PAST" coming from?

| where which=="CURRENT" --Why are you looking for where which == current?

0 Karma

jaime_ramirez
Communicator

Have you tried specifying the search string inside the append?:

index=network* (src_ip = 10.0.0.0/8 OR src_ip=172.16.0.0/12 OR src_ip=192.168.0.0/16) AND (dest_ip=10.0.0.0/8 OR dest_ip=172.16.0.0/12 OR dest_ip=192.168.0.0/16) dest_port=* action=allowed
  | bin _time span=15m
  | eventstats count BY src_ip dest_ip _time
  |rename count AS current_count
  | rename src_ip as Source_ip, dest_ip AS Destination_ip, count AS curr_count
  | eventstats first(current_count) AS Current_Connections BY Source_ip Destination_ip 
 |append
 [search index=network* (src_ip = 10.0.0.0/8 OR src_ip=172.16.0.0/12 OR src_ip=192.168.0.0/16) AND (dest_ip=10.0.0.0/8 OR dest_ip=172.16.0.0/12 OR dest_ip=192.168.0.0/16) dest_port=* action=allowed earliest=-24h latest=+23h
 | bin _time span=15m
 |eventstats count BY src_ip dest_ip _time
 |rename count AS avg_count
 |eventstats avg(avg_count) BY src_ip dest_ip AS average_count
 ]
 table *
0 Karma

aking76
Path Finder

Yeah I have, it errors out.
I've also saved both searches and called them using the savedsearch function, but that just sits there and spins forever.

0 Karma

jaime_ramirez
Communicator

What error does it show?

0 Karma

aking76
Path Finder

Error in 'append' command: The last argument must be a subsearch.

0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...