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 ?
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 ...
.
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?
I see no reason to change the CSV file now.
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.
This add on which works as a lookup is installed on indexer..will local=true work here
local=true
will work if the lookup is installed on the search head.
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.
Go to Settings->Lookups->Lookup Files. If you can't see that option then you'll need to get an admin to help.
How large is the lookup file on disk? What speed is your network between search heads and indexers?
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
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
Hi Cusello...I have checked the issue and the events are more than 50k
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?
@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)
?
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?