Splunk Search

Link two searches by common field

dreamwork801
Path Finder

So I am running this search which gives the top frequencies of URL's that have 500 errors.

"AppName" = "Master" "Status" = "500" | stats count as Frequency by  Url | sort -Frequency

The JSON looks like this:

AppName: Master
ContextId: 123456789
Status: 500
Url: https://master.

But I actually don't care to have the URL, I want the request name, which is stored in a different JSON with a different AppName but the same ContextId

It looks like:

AppName: AppServices-Master
ContextId: 123456789
Name: PaymentsRequest

So I want the list instead of giving me a list of URL's, instead give me a list of the Names that have 500 errors.

Any ideas? I'm not sure if I need a subsearch or if I can do without one.

1 Solution

dreamwork801
Path Finder

Actually I figured out the best answer to what I wanted to be done:

"AppName" = "AppServices.Master" [search "AppName" = "Master" "Status" = "500" | fields + ContextId] | stats values(ContextId) by Name | rename values(ContextId) as ContextId | eval Count = mvcount(ContextId) | sort -Count

And it gives me a list of Requests that have 500 errors. I didn't realize in subsearches, the subsearch is ran first and then the information is passed to the bigger search.

View solution in original post

dreamwork801
Path Finder

Actually I figured out the best answer to what I wanted to be done:

"AppName" = "AppServices.Master" [search "AppName" = "Master" "Status" = "500" | fields + ContextId] | stats values(ContextId) by Name | rename values(ContextId) as ContextId | eval Count = mvcount(ContextId) | sort -Count

And it gives me a list of Requests that have 500 errors. I didn't realize in subsearches, the subsearch is ran first and then the information is passed to the bigger search.

emiller42
Motivator

Subsearches can be useful, but be careful with them. They essentially add 'foo' OR 'bar' OR 'baz' OR ... to your search, which gets really messy when the sub search returns a lot of values.

I also highly recommend ending the sub search with the | format command, as that will format the output in proper search terms. (ContextId=12345) OR (ContextId=23456) OR ...

emiller42
Motivator

A easy way to do this is with the transaction command:

<search which gets both types of events> | transaction ContextId | search Status=500 | stats count by Name

But the more efficient way is with stats:

<search which gets both types of events> | stats first(Name) as Name first(Status) as Status by ContextId | search Status=500 | stats count by Name
0 Karma

emiller42
Motivator

You just search for exactly what you said:

(AppName="Master" Status=500) OR AppName="AppServices-Master" | stats first(Name) as Name first(Status) as Status by ContextId | stats count by Name

It is also worth noting that including filters on Index and Sourcetype can significantly help performance, so include those in your initial search whenever possible.

0 Karma

dreamwork801
Path Finder

The second one works for me but its super slow because the search is looking in "AppName: Master" and "AppName: AppServices-Master" Is there some type of if command that searches for "Status = 50" and "AppName = Master", and if it finds an event, it takes the ContextId of the event and searches "AppName = AppServices-Master" with the same ContextId and grabs the Name. And then does the stats count by Name.

0 Karma

somesoni2
SplunkTrust
SplunkTrust

You can use join for the same

"AppName" = "Master" "Status" = "500" | stats count as Frequency by ContextId | join type=left [search <<search to select 2nd json>> | stats count by ContextId, Name | fields - count] | table Name Frequency | sort - Frequency 
0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...