How can I optimize my search?

[Deepali529]

This search is taking too much time to execute, around 20 mins. Is it because of the lookup?

index="access_log_index" sourcetype="access_log" | rename agent as http_user_agent | lookup user_agents http_user_agent | stats count by  ua_os_family | rename ua_os_family as os_type

How can I optimize my search? Any suggestions will be appreciated.
Thanks

[skoelpin · Accepted solution]

You should look at the job inspector and see where most of the time is being spent

https://docs.splunk.com/Documentation/Splunk/6.5.0/Search/ViewsearchjobpropertieswiththeJobInspector

How big is your lookup table?

[Deepali529]

Thanks skoelpin 🙂
It reduces the execution time of query from 816 seconds to 28 seconds

[Deepali529]

It's quite large lookup. I want to find the OS types so installed TA_user_agents add-on. Lookup is present in it

[skoelpin]

"quite large" can be relative.. If your lookup is as massive as you claim then you could always use a case statement to match on rather than a lookup table.. It should be faster but you can test this by creating a simple match case and run it over a small time frame then look at the job inspector to see how long it took. Then run the same sample over the lookup and see how long that took

... | eval os = case(match(useragent,"Windows .. 5\.1"),"Windows XP",match(useragent,"droid"),"Android",match(useragent,"Windows NT 6.1"),"Windows 7") | eval browser = case(match(useragent,"MSIE 10.0"),"Internet Explorer 10.0",match(useragent,"Chrome"),"Chrome",match(useragent,"Safari/"),"Safari") | eval arch = case(match(useragent,"droid"),"android",match(useragent,"iPad"),"ipad",match(useragent,"iPod"),"ipod")