Splunk Search

Alternative suggestions needed for join and subsearch commands as my Splunk instance is limited to 50k results

Path Finder

I am facing an issue with the subsearch limitations when using the join statement. My organizations Splunk implementation has a 50k max on subsearches. I was unable to think up an alternative that did not use joins/appends/subsearches, so I thought to ask the community.

I am trying to join two sources on the requestID. Source A is webserver access logs, which contain a unique requestID and a user_agent field that I am using to identify and isolate consumers. Source B is application trace logs, which contain a whole lot more detail on the request made, including the backend request type they initiated.

My goal, is to identify the count of hits to specific backend systems from specified user_agents. To do this, I joined on the requestID, as it was the only common identifying value shared between these two log sources.

My search looks something like this:

index=foo source=bar/access.log user_agent="Mozilla*" | table requestID | join requestID [ search index=foo source=bar/trace.log "Request Complete" | *regex to extract backend system detail* |  table requestID backend ]| stats dc(requestID) by backend

So for every incoming request from a web broswer, give me a table of requestIDs, join said ID's to entries within the trace log, and count the total hits by backend request types. The timerange that I hope to search over, far exceeds 50k unique transactions.

Any suggestions to avoid the join statement and subsearch limitation?

1 Solution

SplunkTrust
SplunkTrust

Give this a try
Updated

index=foo (source=bar/access.log user_agent="Mozilla*") OR (source=bar/trace.log "Request Complete" )
| regex to extract backend system detail 
| stats values(backend) as backend dc(source) as sources by requestID | where isnotnull(backend) AND sources=2
| stats dc(requestID) by backend

OR

index=foo source=bar/trace.log "Request Complete" [search index=foo source=bar/access.log user_agent="Mozilla*" | stats count by requestID | table requestID]| regex to extract backend system detail | stats dc(requestID) by backend

View solution in original post

Esteemed Legend

Like this:

index=foo (source=bar/access.log user_agent="Mozilla*" OR source=bar/trace.log "Request Complete")
| *regex to extract backend system detail*
| fields requestID backend
| stats values(*) AS * BY requestID
| stats dc(requestID) BY backend
0 Karma

SplunkTrust
SplunkTrust

Hi,

Take a look at "multisearch", one of the best commands ever:

http://docs.splunk.com/Documentation/Splunk/6.5.2/SearchReference/WhatsInThisManual

| multisearch [ search index=foo source=bar/access.log user_agent="Mozilla*" ] [ search index=foo source=bar/trace.log "Request Complete" ]

It does not have any constraints like sub-searches does, create your stats results over your by statement you'll be good.

You can as well simply use a Boolean in your search and then do your stuff and create a table stats with a "stats values(foo) as foo by bar"

(index=foo source=bar/access.log user_agent="Mozilla*") OR (index=foo source=bar/trace.log "Request Complete")

In any case, avoid sub-searches like the evil.
And remove the useless "table", that's a bad habit that breaks the map reduce.

0 Karma

SplunkTrust
SplunkTrust

Give this a try
Updated

index=foo (source=bar/access.log user_agent="Mozilla*") OR (source=bar/trace.log "Request Complete" )
| regex to extract backend system detail 
| stats values(backend) as backend dc(source) as sources by requestID | where isnotnull(backend) AND sources=2
| stats dc(requestID) by backend

OR

index=foo source=bar/trace.log "Request Complete" [search index=foo source=bar/access.log user_agent="Mozilla*" | stats count by requestID | table requestID]| regex to extract backend system detail | stats dc(requestID) by backend

View solution in original post

Path Finder

Thanks for your time & response! Using your first example, I am getting all trace logs, not just events where the requestID was present in the access logs with user_agent="Mozilla*".

I suppose this relationship is what I am failing to understand without the use of joins. How to eliminate/ignore request events in trace.log that do not match the list of requestIDs available in access.log from specified user_agent.

I am getting an inaccurate count vs my expectations, as it is the request count to all backends, regardless of who the user_agent is. Am i mis-using your example? Could you provide any more detail?

Example #2 returned no results.

0 Karma

SplunkTrust
SplunkTrust

In search#1, we select all the records from both the sources (access and trace) , use stats to combine the result and then counting distinct requestID count for each backend. (please check if the base queries are correct). Could you tell if you're getting higher or lower count than your expectation with search#1?

The search#2 only selects trace.log events which have same requestID values from access logs (subsearch being used as filter), and then does the calculations only on filtered trace.log events. For that to work, it need requestID field to be available as field in the base search. In trace.log how do you get requestID field, from the regex?

0 Karma

Path Finder

For #1, I am getting higher counts than I would expect. Specifically, I am getting the count of all trace logs, instead of a count where the requestID exists in both sources. The backend detail is only present in the application trace.log.

For #2, it sounds like what I am trying to accomplish, with the subsearch being used as the filter. I did get this to run, I must have made an error the first time around. However, the subsearch warning for max results is still present and truncates the result set. So I don't believe this will work.

0 Karma

SplunkTrust
SplunkTrust

I missed a critical step in #1. Try the updated query.

0 Karma

Path Finder

Cheers! That does the trick. I started thinking along these same lines as soon as I typed out the 'where request ID exists in both sources' as an emphasis when filtering.

Thanks again for your time and follow-up!

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!