How to combine two searches into one table using count twice

[aking76]

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 *

[woodcock · Accepted solution]

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]

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?

[jaime_ramirez]

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 *

[aking76]

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.

[jaime_ramirez]

What error does it show?

[aking76]

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