topic Re: How can I optimize my search? in Splunk Search

How can I optimize my search?

Deepali529 — Thu, 24 Nov 2016 05:10:21 GMT

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

Re: How can I optimize my search?

skoelpin — Thu, 24 Nov 2016 05:15:03 GMT

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?

Re: How can I optimize my search?

Deepali529 — Tue, 29 Sep 2020 11:52:24 GMT

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

Re: How can I optimize my search?

skoelpin — Thu, 24 Nov 2016 14:25:54 GMT

"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")

Re: How can I optimize my search?

Deepali529 — Fri, 25 Nov 2016 09:03:57 GMT

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