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, an upvote 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, an upvote 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, an upvote 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, an upvote 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
Register for .conf21 Now! Go Vegas or Go Virtual!

How will you .conf21? You decide! Go in-person in Las Vegas, 10/18-10/21, or go online with .conf21 Virtual, 10/19-10/20.