Lookup Search is taking over an hour to complete

[ramprakash]

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 ?

[richgalloway]

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

[ramprakash]

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?

[richgalloway]

I see no reason to change the CSV file now.

[ramprakash]

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.

[ramprakash]

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

[richgalloway]

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

[ramprakash]

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.

[richgalloway]

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

[jkat54]

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

[ramprakash]

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

[gcusello]

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

[ramprakash]

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

[ramprakash]

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?

[kamlesh_vaghela]

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

[ramprakash]

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?