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