Splunk Search

How to optimize the search with join command

shashank_24
Path Finder

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

 

 

Labels (2)
Tags (2)
0 Karma
1 Solution

to4kawa
Ultra Champion
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.

View solution in original post

0 Karma

to4kawa
Ultra Champion
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.

0 Karma

shashank_24
Path Finder

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

0 Karma

to4kawa
Ultra Champion

 

 

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.

0 Karma

shashank_24
Path Finder

@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_contentpresent
/checkoutyes
/your-detailsyes
/billingno

 

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"
0 Karma

to4kawa
Ultra Champion

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

0 Karma

twesty
Path Finder

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 


0 Karma
Get Updates on the Splunk Community!

Your Guide to SPL2 at .conf24!

So, you’re headed to .conf24? You’re in for a good time. Las Vegas weather is just *chef’s kiss* beautiful in ...

Get ready to show some Splunk Certification swagger at .conf24!

Dive into the deep end of data by earning a Splunk Certification at .conf24. We're enticing you again this ...

Built-in Service Level Objectives Management to Bridge the Gap Between Service & ...

Now On-Demand Join us to learn more about how you can leverage Service Level Objectives (SLOs) and the new ...