Splunk Search

How to join two searches?

New Member

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.

Tags (2)
0 Karma
1 Solution

Influencer

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

View solution in original post

0 Karma

Influencer

You should see something like this:alt text

0 Karma

Influencer

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

View solution in original post

0 Karma

New Member

this is what i get alt text

0 Karma

New Member

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

0 Karma

Influencer

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

0 Karma

New Member

so it does return events but no statistics or visualization.

0 Karma

Influencer

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

0 Karma

New Member

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.

0 Karma

New Member

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

0 Karma

Influencer

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

0 Karma

New Member

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...

0 Karma

Influencer

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

0 Karma

New Member

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.

0 Karma

Influencer

See in the stats command you have by status

Change that to statusCode

0 Karma

New Member

it works! thanks for pointing out that small details.

0 Karma

Builder

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]

0 Karma