All Apps and Add-ons

Lookup Search is taking over an hour to complete

ramprakash
Explorer

index=iso_wa sourcetype=iso_wa_pages | where isnotnull(nv_usr_agt) | table nv_usr_agt | rename nv_usr_agt as http_user_agent | dedup http_user_agent | lookup browscap_lookup_express http_user_agent | inputlookup http_user_agent append=true | dedup http_user_agent | outputlookup http_user_agent

Can anyone help me in understanding why it is taking long time to complete and how can i optimize ?

0 Karma

richgalloway
SplunkTrust
SplunkTrust

In addition to kamlesh_vaghela's good comment,
Replace table with fields. The fields command is processed by indexers whereas table is performed by the search head.
Replace dedup http_user_agent with stats count by http_user_agent | fields - count.
How big is the iso_wa index? A large index takes a long time to search and the only way around that is to distribute the index across more indexers.
How big is the browsecap_lookup_express lookup? Large lookup files can take a long time to ship from search head to indexers. If this is the case, try lookup local=true ....

---
If this reply helps you, Karma would be appreciated.
0 Karma

ramprakash
Explorer

Thanks for the Suggestions. I am out of office for Holidays. Requesting you to please continue following this post. I will try once i get into office.

Also could you please let me know if it is needed to update browscap.csv file or query only?

0 Karma

richgalloway
SplunkTrust
SplunkTrust

I see no reason to change the CSV file now.

---
If this reply helps you, Karma would be appreciated.
0 Karma

ramprakash
Explorer

Hi Richgalloway...I have modified the query and the issue still persists. It is checking almost 50k events and when i checked the Search Job Inspector, I found that lookup command is taking 99% of time.

0 Karma

ramprakash
Explorer

This add on which works as a lookup is installed on indexer..will local=true work here

0 Karma

richgalloway
SplunkTrust
SplunkTrust

local=true will work if the lookup is installed on the search head.

---
If this reply helps you, Karma would be appreciated.
0 Karma

ramprakash
Explorer

I am not getting clear idea where this lookup is actually installed. How can i verify this thing through search head as i don't have admin access to check config files of Indexers and Search head.

0 Karma

richgalloway
SplunkTrust
SplunkTrust

Go to Settings->Lookups->Lookup Files. If you can't see that option then you'll need to get an admin to help.

---
If this reply helps you, Karma would be appreciated.
0 Karma

jkat54
SplunkTrust
SplunkTrust

How large is the lookup file on disk? What speed is your network between search heads and indexers?

0 Karma

ramprakash
Explorer

Hi..How can i check the file, since it is a external lookup...actually i have installed Http_user_agent add on which consists dynamic lookup

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi ramprakash,
I don't understand why you used the lookup command, you don't use any additional field!
Anyway, try something like this:

index=iso_wa sourcetype=iso_wa_pages nv_usr_agt=* 
| fields nv_usr_agt 
| rename nv_usr_agt as http_user_agent 
| append [ | inputlookup http_user_agent append=true | fields http_user_agent ]
| dedup http_user_agent 
| outputlookup http_user_agent

Remember that using a subsearch, there's the limit of 50,000 results.

Bye.
Giuseppe

0 Karma

ramprakash
Explorer

Hi Cusello...I have checked the issue and the events are more than 50k

0 Karma

ramprakash
Explorer

Thanks for the Suggestions. I am out of office for Holidays. Requesting you to please continue following this post. I will try once i get into office.

Also could you please let me know if it is needed to update browscap.csv file or query only?

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@ramprakash

Can you please try initial search like index=iso_wa sourcetype=iso_wa_pages nv_usr_agt=* instead of index=iso_wa sourcetype=iso_wa_pages | where isnotnull(nv_usr_agt) ?

0 Karma

ramprakash
Explorer

Thanks for the Suggestions. I am out of office for Holidays. Requesting you to please continue following this post. I will try once i get into office.

Also could you please let me know if it is needed to update browscap.csv file or query only?

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...