topic Re: How to improve search performance with join alternative? Query frequently times out due to subsearch time limit in Splunk Search

How to improve search performance with join alternative? Query frequently times out due to subsearch time limit

JackNY07 — Mon, 04 Apr 2022 21:00:34 GMT

I have a query that frequently times out due to the subsearch time limit. I'd like to improve it's performance but I'm not sure how. Here's my query:

host=prod* source=user-activity.log sourcetype=log4j ID=uniqueID MESSAGE="LOGIN_SUCCESS*"| stats count as Logins by Full_Date, ID, DName, STATE | join type=left ID [ search host=prod* source=server.log sourcetype=log4j MESSAGE="[Dashboard User-Facing*" ID=uniqueID | stats count as Errors by Full_Date ,ID, DName, STATE ]|eval %=round((100*Errors)/Logins,0) |table ID, DName, Full_Date, STATE, Errors, Logins,%

Any help would be greatly appreciated.

Re: How to improve search performance with join alternative? Query frequently times out due to subsearch time limit

bowesmana — Mon, 04 Apr 2022 23:19:06 GMT

It's quite straightforward to get this into a single search, by searching data_set_1 OR data_set_2 then evaluating the data set in the stats command like this

host=prod* sourcetype=log4j (source=user-activity.log ID=uniqueID MESSAGE="LOGIN_SUCCESS*") OR (source=server.log MESSAGE="[Dashboard User-Facing*" ID=uniqueID) | stats sum(eval(if(MESSAGE="LOGIN_SUCCESS", 1, 0))) as Logins sum(eval(if(source=server.log, 1, 0))) as Errors by Full_Date, ID, DName, STATE | eval %=round((100*Errors)/Logins,0) | table ID, DName, Full_Date, STATE, Errors, Logins,%

In the stats/eval you are checking for the message login success to indicate a login data event and then the source for the error data set.

Re: How to improve search performance with join alternative? Query frequently times out due to subsearch time limit

JackNY07 — Tue, 05 Apr 2022 01:20:59 GMT

@bowesmana Thank you!

Quick question though. How would I dedup just 1 of the 2 searches by SESSID? stats dc? I had previously used eventstats dc(SESSID) as Errors but can't seem to work it into the new search.

Re: How to improve search performance with join alternative? Query frequently times out due to subsearch time limit

bowesmana — Tue, 05 Apr 2022 05:34:15 GMT

Depends on what that dedup is supposed to be doing. Is SESSID a unique session ID that is found in errors and are you trying to count unique sessions with errors?

Do both data sets have a SESSID field?

If you want to get unique sessions for the error data set, the something like this in the existing stats command

dc(eval(if(source="server.log", SESSID, null()))) as SessionsWithErrors

which is saying if it is the errors data set (source=server.log) then return the SESSID from that event otherwise return null. dc will then count unique sessions.