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
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.
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.
@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.
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.
@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"
I see your problem. but your problem is another question. Please ask a separate question.
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