Splunk Search

Lookup slowing performance ways to optimize

msrama5
Explorer

Hi , I have the following search query that lookups definition file TeamsLookupDef which has 200 mappings between apifamily and domain, running the query is always going to waiting for queued job to start, any ideas how can this query be improved for performance ?

Query -
| savedsearch AR_BaseQuery filter=*
| eventstats count as tc
| stats distinct_count(URI) as #APIs, first(tc) as tc by APIFamily
| lookup TeamsLookupDef APIFamily as APIFamily output Domain
| stats sum(#APIs) as Apis, first(tc) as tc by Domain

TeamsLookupDef definition file for mapping betwene apifamily and domain fields.
-APIFamily- -Domain-
/url1/v1 Unknown
/url2/v1 dp-iis1
/url3/v1 dp-iis2
... ...
200 mapping entries for definition

0 Karma

to4kawa
Ultra Champion
| savedsearch AR_BaseQuery filter=*

This is the cause of poor performance.

please provide sample logs and explain fields.

0 Karma

msrama5
Explorer

Following is description of saved search AR_BaseQuery-
search environment=test
index=iis* NOT "GET / - 80" NOT "GET / - 443" NOT "/ping*" NOT "swagger" $filter$
|eval URI=lower(cs_uri_stem)
|rex field=URI "(?APIFamily[/\w.]+/v\d+)/"

cs uri stem is just different uri end points
api family is categorizing uri's by api family
domain is categorizing api family by domain mapping

0 Karma

to4kawa
Ultra Champion
index=iis* cs_uri_stem=* [|inputlookup TeamsLookupDef 
|fields  APIFamily|return 200 $APIFamily]
|eval URI=lower(cs_uri_stem)
|rex field=URI "(?<APIFamily>[/\w.]+/v\d+)/"
| eventstats count as tc
| stats distinct_count(URI) as #APIs, first(tc) as tc by APIFamily
| lookup TeamsLookupDef APIFamily as APIFamily output Domain
| stats sum(#APIs) as Apis, first(tc) as tc by Domain

maybe a little faster.

0 Karma
Get Updates on the Splunk Community!

Observe and Secure All Apps with Splunk

  Join Us for Our Next Tech Talk: Observe and Secure All Apps with SplunkAs organizations continue to innovate ...

Splunk Decoded: Business Transactions vs Business IQ

It’s the morning of Black Friday, and your e-commerce site is handling 10x normal traffic. Orders are flowing, ...

Fastest way to demo Observability

I’ve been having a lot of fun learning about Kubernetes and Observability. I set myself an interesting ...