topic How to optimize the search with join command in Splunk Search

How to optimize the search with join command

shashank_24 — Mon, 15 Jun 2020 21:42:48 GMT

Hi, I have a query where I need to join it by a lookup to match the records. This is horribly slow and could be because of join command as it is very expensive.

Is there a way to optimize this search as I have to run this for last 90 days and it keeps running for ages.

My lookup only consist of one column i.e. URL against which I need to match the records and then count them

Let me know if someone can advice

Re: How to optimise the search with Join command

twesty — Fri, 12 Jun 2020 11:17:07 GMT

Assuming you're joining on requested_conten and not requested_content

something along the lines of lookup vanity.csv requested_conten OUTPUT url as requested_content

if the question contains a typo then you can easily modify the lookup command to fit your needs. Check out the documentation for lookup here: https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/lookup

Re: How to optimise the search with Join command

to4kawa — Fri, 12 Jun 2020 11:34:45 GMT

index=myapp_pp sourcetype=access_combined GET host="my-server-*" [|inputlookup vanity.csv | rename url as requested_content |fields requested_content] | stats values(requested_content) as requested_content

You don't need join , I guess.

Re: How to optimise the search with Join command

shashank_24 — Mon, 15 Jun 2020 09:17:32 GMT

@to4kawa that's right. It worked without join command.

Also is there a way to list out the unmatched url's from csv. I mean I want to run a search against those URL's in the CSV (around 900) for last 90 days and just list out the one's which are not present in the search/events.

Let me know if you can advice please.

Re: How to optimise the search with Join command

to4kawa — Mon, 15 Jun 2020 10:36:13 GMT

index=myapp_pp sourcetype=access_combined GET host="my-server-*" NOT [|inputlookup vanity.csv | rename url as requested_content |fields requested_content] | stats values(requested_content) as requested_content

try NOT for unmatched searching.

Re: How to optimise the search with Join command

shashank_24 — Mon, 15 Jun 2020 13:13:46 GMT

@to4kawa Thanks for your quick response but this search will give all the results from my search.

So what I am looking for this is -

probably a table something like this - My lookup contains one column with around 800 requested_content and I want to find if any of those present in my search. If yes then "yes" and if not then "no".

requested_content	present
/checkout	yes
/your-details	yes
/billing	no

Basically, I only want to list out the requested_content from the csv whether it's matched or not. I don't want to print anything else from the main query.

Hope I am able to explain. Let me know if it requires another question.

I have tried something like this but it doesn't work -

Re: How to optimise the search with Join command

to4kawa — Mon, 15 Jun 2020 21:01:42 GMT

I see your problem. but your problem is another question. Please ask a separate question.