topic Re: Speed Search Review in Splunk Search

Speed Search Review

rafazurc — Wed, 30 Sep 2020 04:56:38 GMT

Hello Everyone.

I m new to splunk and I have one search which is taking a bit longer than others. Is there any suggestion on how to improve this search ?

index=mydatasource_* (sourcetype = x_connections OR sourcetype= x_collectors) engine="engine" Src_SubnetName = "vpn"| eval src= if(isnull(src),name, src)
| eval Dates = _time
| eval Src_SubnetName = Src_Sitename
| convert timeformat="%Y-%m-%d" ctime(Dates)
| stats dc(src) by src,Src_SubnetName, Dates

Re: Speed Search Review

richgalloway — Mon, 13 Apr 2020 15:07:57 GMT

How long is "a bit"? How much data is being searched? Searching more data will take more time.

Re: Speed Search Review

rafazurc — Mon, 13 Apr 2020 15:12:36 GMT

Hello @richgalloway .

To search the last 24hours (~200M events ) takes around 45 minutes. and generates ~80k Results.

Thanks.

Re: Speed Search Review

efavreau — Mon, 13 Apr 2020 17:21:25 GMT

@rafazurc The more specific you can make a search before the first |, the faster it will be. Do you need need blank src in your results? The put in src=*, to get rid of blanks. Do you need all those indexes? Is there any other detail, even a word or two that will appear in every result? Put all of that up front before the first pipe. Otherwise, it is what it is. The rest of your SPL isn't expensive.

Re: Speed Search Review

jpolvino — Mon, 13 Apr 2020 17:38:28 GMT

The dc aggregation function can be very expensive. Did you job inspector give any insight as to where the time is being spent? I'm also curious what you're ultimately trying to achieve...knowing that may help the community solve your challenge.

See this link for info on dc and how to work around it.

Re: Speed Search Review

rafazurc — Mon, 13 Apr 2020 17:49:37 GMT

Hello @efavreau. As I have 2 sourcetype and one has src and other name. Does it work to add before the first pipe (src=* OR name=*) Thanks

Re: Speed Search Review

rafazurc — Mon, 13 Apr 2020 18:00:53 GMT

Hello @jpolvino. I ve added the print of mu job inspector results. What I m trying to achieve is. I have 2 sourcetypes one is the connection and the other collector. The fist one, the field I need to use is src, the second is name. So I m trying to check each event and if src is null consider the name.

After that, I m formatting _time as date, and the SubnetName is a common field for both sourcetypes. The result I need is to list distinct src by each network by day.

I really would like to optimize this search to reduce the search cost. I m checking the link you ve sent looking for more hints.

Thanks

Re: Speed Search Review

efavreau — Mon, 13 Apr 2020 18:03:33 GMT

@rafazurc If you need these fields, then adding (src=* OR name=*) is better than not having it.

Re: Speed Search Review

PavelP — Mon, 13 Apr 2020 23:03:24 GMT

Hello @rafazurc ,

run these searches (use the "smart mode", use a short period like last 60min instead of last 24hours) and post their search.log (your search.log screenshot is not complete and some important information can be missed) :

search 1:

index=mydatasource_* (sourcetype = x_connections OR sourcetype= x_collectors) engine="engine" Src_SubnetName = "vpn"| eval src= if(isnull(src),name, src)
| eval Dates = _time
| eval Src_SubnetName = Src_Sitename
| convert timeformat="%Y-%m-%d" ctime(Dates)
| stats dc(src) by src,Src_SubnetName, Dates

search 2:

index=mydatasource_* (sourcetype = x_connections OR sourcetype= x_collectors) engine="engine" Src_SubnetName = "vpn"

search 3:

index=mydatasource_* (sourcetype = x_connections OR sourcetype= x_collectors)

by comparing durations of command.search component you'll get the idea if your search can be [easily] optimized.

search 4:

index=mydatasource_* sourcetype = x_connections

search 5:

index=mydatasource_* sourcetype= x_collectors

also check the splunk documentation and try to find out if this a rare/sparse or rare search

Re: Speed Search Review

to4kawa — Tue, 14 Apr 2020 00:45:15 GMT

index=mydatasource_* ((sourcetype = x_connections src=*) OR (sourcetype= x_collectors name=*)) engine="engine" Src_SubnetName = "vpn" 
| eval src= coalesce(src,name) 
| eval Dates = strftime(_time, "%F") 
| stats estdc(src) as distinct_src_count by Src_Sitename, Dates
| rename Src_Sitename as Src_SubnetName

Your query has extra calculations.
How about this?

Re: Speed Search Review

sectrainingjk — Tue, 14 Apr 2020 01:39:16 GMT

To add to what @efavreau said about identifying words that will end up in every result...

I've had a lot of success using the [Patterns] analysis of search results to identify these words.

Also, [All Fields] and then sorting to see fields with maximum "100% Event Coverage" and "# of Values" can help as well.