Splunk Search

How to combine two searches into one table using count twice

Explorer

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

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

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

Explorer

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

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

Explorer

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

Communicator

What error does it show?

0 Karma

Explorer

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

0 Karma