How to optimize the search with join command

[shashank_24]

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

index=myapp_pp sourcetype=access_combined GET host="my-server-*"
| join requested_conten
    [| inputlookup vanity.csv 
    | rename url as requested_content] 
| stats count by requested_content
| sort - count

[to4kawa · Accepted solution]

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.

[shashank_24]

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

[to4kawa]

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.

[shashank_24]

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

index=myapp_pp sourcetype=access_combined GET host="my-server-*"
| eval type="MainIndex" 
| fields requested_content type
| appendpipe  
    [| inputlookup vanity.csv  
    | eval type="lookup" 
    | rename vanity_url as requested_content 
    | fields type requested_content ] 
| stats dc(type) as pot, values(*) AS * by requested_content
| where pot=1 and type="lookup"

[to4kawa]

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

[twesty]

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