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"

View solution in original post

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
.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!