topic Re: Lookup Search is taking over an hour to complete in All Apps and Add-ons

Lookup Search is taking over an hour to complete

ramprakash — Tue, 29 Sep 2020 22:32:40 GMT

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

Re: Lookup Search is taking over an hour to complete

kamlesh_vaghela — Thu, 27 Dec 2018 11:59:13 GMT

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

Re: Lookup Search is taking over an hour to complete

gcusello — Thu, 27 Dec 2018 15:16:23 GMT

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

Re: Lookup Search is taking over an hour to complete

richgalloway — Tue, 29 Sep 2020 22:36:55 GMT

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

Re: Lookup Search is taking over an hour to complete

ramprakash — Fri, 28 Dec 2018 14:04:22 GMT

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?

Re: Lookup Search is taking over an hour to complete

ramprakash — Fri, 28 Dec 2018 14:04:30 GMT

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?

Re: Lookup Search is taking over an hour to complete

ramprakash — Fri, 28 Dec 2018 14:04:36 GMT

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?

Re: Lookup Search is taking over an hour to complete

richgalloway — Mon, 31 Dec 2018 13:33:02 GMT

I see no reason to change the CSV file now.

Re: Lookup Search is taking over an hour to complete

ramprakash — Wed, 02 Jan 2019 10:51:51 GMT

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.

Re: Lookup Search is taking over an hour to complete

ramprakash — Wed, 02 Jan 2019 10:54:04 GMT

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

Re: Lookup Search is taking over an hour to complete

jkat54 — Wed, 02 Jan 2019 13:43:37 GMT

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

Re: Lookup Search is taking over an hour to complete

ramprakash — Tue, 29 Sep 2020 22:33:39 GMT

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

Re: Lookup Search is taking over an hour to complete

ramprakash — Wed, 02 Jan 2019 19:51:46 GMT

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

Re: Lookup Search is taking over an hour to complete

richgalloway — Fri, 04 Jan 2019 13:41:09 GMT

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

Re: Lookup Search is taking over an hour to complete

ramprakash — Fri, 04 Jan 2019 14:07:42 GMT

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.

Re: Lookup Search is taking over an hour to complete

richgalloway — Mon, 07 Jan 2019 13:52:00 GMT

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