topic Re: How to join two searches? in Splunk Search

How to join two searches?

skphi13 — Tue, 29 Sep 2020 20:36:50 GMT

I need help joining the following 2 searches.

Search 1:

app="atlas" source="/usr/local/homeaway/atlas-production/logs/*" index="aws_prod_applogs" titan | stats avg(*responseTime) by date_mday

Search 2:

app="atlas" source="/usr/local/homeaway/atlas-production/logs/*" index="aws_prod_applogs" titan statusCode=200 | stats avg(*responseTime) by date_mday

I tried using Join and Append but it's no working.

Re: How to join two searches?

hortonew — Thu, 26 Jul 2018 18:56:04 GMT

I believe with stats you need appendcols not append. For instance:

| appendcols [search app="atlas" source="/usr/local/homeaway/atlas-production/logs/*" index="aws_prod_applogs" titan statusCode=200 | stats avg(*responseTime) by date_mday]

Re: How to join two searches?

jplumsdaine22 — Thu, 26 Jul 2018 19:36:08 GMT

Assuming all your events have a status code, you can do it one:

app="atlas" source="/usr/local/homeaway/atlas-production/logs/*" index="aws_prod_applogs" titan statusCode=* 
|  eventstats sum(*responseTime) as total* count as count_events by date_mday
|  where status=200
| stats max(total*) as total* max(count_events) as count_events avg(*responseTime) as avg_*responseTime_200 by status date_mday
|  foreach total* 
    [eval <<FIELD>>=<<FIELD>>/count_events]
|  rename total* as avg*_all_status

Re: How to join two searches?

skphi13 — Fri, 27 Jul 2018 14:27:43 GMT

Yes all event should have a status code. When i tried running this, nothing shows up.

Re: How to join two searches?

jplumsdaine22 — Fri, 27 Jul 2018 14:30:16 GMT

Ah sorry in my test search I had just status. Change status to statsCode and you should be good to go

Re: How to join two searches?

skphi13 — Fri, 27 Jul 2018 14:36:22 GMT

so it does return events but no statistics or visualization.

Re: How to join two searches?

jplumsdaine22 — Fri, 27 Jul 2018 14:41:26 GMT

Er that has a stats command in there, it can't return events unless you're running in verbose mode, in which case just switch to the relevant tab

Re: How to join two searches?

jplumsdaine22 — Fri, 27 Jul 2018 14:44:25 GMT

You should see something like this:

Re: How to join two searches?

skphi13 — Fri, 27 Jul 2018 15:14:47 GMT

this is what i get

Re: How to join two searches?

skphi13 — Fri, 27 Jul 2018 15:16:42 GMT

i switch it to smart mode and ran the search again and nothing. I even change the timeframe to the last 7 days. I have posted the picture of the results but it's awaiting moderator approval.

Re: How to join two searches?

skphi13 — Fri, 27 Jul 2018 15:19:33 GMT

it's showing 0 events, but i know that's incorrect

Re: How to join two searches?

jplumsdaine22 — Fri, 27 Jul 2018 15:28:36 GMT

OK, step back through the search.

ie I assume you get events for this:

 app="atlas" source="/usr/local/homeaway/atlas-production/logs/*" index="aws_prod_applogs" titan statusCode=*

So just keep adding the search commands until you find the one that breaks it. Most likely it is a misnamed field

Re: How to join two searches?

skphi13 — Tue, 29 Sep 2020 20:37:47 GMT

so i ran the following in smart mode

app="atlas" source="/usr/local/homeaway/atlas-production/logs/" index="aws_prod_applogs" titan statusCode= | eventstats sum(responseTime) as total count as count_events by date_mday

It returns a bunch of events but no stats...

Re: How to join two searches?

jplumsdaine22 — Fri, 27 Jul 2018 15:52:52 GMT

Eventstats will add the stats to each event - this is so you can calculate the averages of all events even after dropping the others

Re: How to join two searches?

skphi13 — Fri, 27 Jul 2018 16:01:18 GMT

yea so when i ran the serach with eventstats no statistics show up in the results. But when i ran it with stats the statistics shows up in the result.

Re: How to join two searches?

jplumsdaine22 — Fri, 27 Jul 2018 16:35:29 GMT

See in the stats command you have by status

Change that to statusCode

Re: How to join two searches?

skphi13 — Fri, 27 Jul 2018 16:46:09 GMT

it works! thanks for pointing out that small details.