topic Re: Splunk query optimization in Splunk Search

Splunk query optimization

uagraw01 — Mon, 14 Oct 2024 14:11:05 GMT

Hello Splunker!!

Could you please help me to optimize below query ? Customer saying dedup is taking so much resource consumption. So what should I change in the query so that the complete query gets optimized?

index=abc sourcetype=abc _tel type=TEL (trigger=MFC_SND OR trigger=FMC_SND) telegram_type=CO order_type=TO area=D10 aisle=A01 *1000383334* | rex field=_raw "(?P<Ordernumber>[0-9]+)\[ETX\]" | fields _time area aisle section source_tel position destination Ordernumber | join area aisle [ inputlookup isc where section="" | fields area aisle mark_code | rename area AS area aisle AS aisle] | lookup movement_type mark_code source AS source_tel position AS position destination AS destination OUTPUT movement_type | fillnull value="Unspecified" movement_type | eval movement_category = case( movement_type like "%IH - LH%", "Storage", movement_type like "%LH - R%", "Storage", movement_type like "%IH - IH%", "Storage", movement_type like "%R - LH%", "Retrieval", movement_type like "%LH - O%", "Retrieval", 1 == 1, "Unknown" ) | fields - source_tel position destination | dedup Ordernumber movement_category | stats count AS orders by area aisle section movement_category movement_type Ordernumber _raw

Re: Splunk query optimization

ITWhisperer — Mon, 14 Oct 2024 09:02:43 GMT

Optimisation will usually depend on the data set(s) you are dealing with, which you haven't provided. Having said that, the dedup by Ordernumber and movement_category will mean that there is only one event with each unique combination of the values in these fields, which means the count from the stats will always be 1, so what is the point of doing the stats?

Your join is to an inputlookup, can this be replaced by a simple lookup?

Re: Splunk query optimization

uagraw01 — Mon, 14 Oct 2024 11:18:35 GMT

@ITWhisperer Thanks for your response.

As per your suggestion I will take care of the join and replace that will lookup command.

I am adding screenshots of the results so that you can get a little more clarity.

Below are the results while executing the above query. Order number is same but one entry is for "Storage" & other one for "Retrieval" .

Job inspection while executing above query

Do you have any suggestion so that I can replace dedup with some more optimized command?

Re: Splunk query optimization

ITWhisperer — Mon, 14 Oct 2024 11:50:58 GMT

Given that the number of orders is always 1 (as previously explained and shown in your screenshot), the dedup is not actually doing anything useful and can be removed. This could affect the orders field in that it could be more than 1. This could be resolved by either evaluating it to 1 after the stats command, or by using distinct count

| stats dc(Ordernumber) AS orders by area aisle section movement_category movement_type Ordernumber _raw

Re: Splunk query optimization

PickleRick — Mon, 14 Oct 2024 14:16:03 GMT

Well, if your Splunk needs 13 seconds to scan just 14 thousand events... that looks weird.

But if you have big events (like 100K-big jsons), considering your wildcard at the beginning of the search term, the initial search might indeed be slow.

So that's the first and probably the most important optimization you can do - if you can drop the wildcard at the beginning of *1000383334*, it will save you a lot of time.

Notice that Splunk had to scan over 14k events just to match two of them. That's because it can't use indexed terms, it has to scan every single raw event.

Since you're extracting Odernumber (and rely on it being non-empty by including it in the BY clause for stats) using [EXT] as an anchor for your regex the [EXT] part must obviously be in your event. So if it's only in part of the events, you can use it as additional search term (square brackets are major breakers so you can just add EXT to your search terms).

The inputlookup and join @ITWhisperer already covered.

Dedup should _not_ be using much resources. As you can see from your job inspect table, it gets just 10 results on input and returns 2. It's not a huge amount. The main problem here is the initial search.

Also if your events are big, you can drop _raw early on so you don't drag it along with you along the pipeline (you only use a few fields in your stats anyway).

Re: Splunk query optimization

uagraw01 — Tue, 15 Oct 2024 05:25:09 GMT

@ITWhisperer After executing suggested command I am getting below results. The count should 2 only.

1 for the storage and 1 for the retrieval.

Re: Splunk query optimization

ITWhisperer — Tue, 15 Oct 2024 11:57:57 GMT

Remove _raw from the by clause

Re: Splunk query optimization

uagraw01 — Tue, 15 Oct 2024 12:17:10 GMT

@ITWhisperer Thanks for your response.

With dedup command it is giving expected 2 results. 1 for storage and 2 for retrieval.

Without dedup command it is giving me 5 results.

Note : There are no such impact seen after removing the join inputlookup with "lookup command".

Re: Splunk query optimization

ITWhisperer — Tue, 15 Oct 2024 12:22:46 GMT

| stats dc(Ordernumber) as count first(movement_type) as movement_type by area aisle section movement_category Ordernumber

Re: Splunk query optimization

uagraw01 — Tue, 15 Oct 2024 12:45:33 GMT

@ITWhisperer Although this method is correct, it takes one second longer than the dedup command.

Thanks in advance.

Re: Splunk query optimization

uagraw01 — Tue, 15 Oct 2024 12:55:18 GMT

@ITWhisperer After apply your suggested command I also replaced lookup command and now search is taking 10.5 seconds to complete the results.

Thanks for your help.

Re: Splunk query optimization

ITWhisperer — Tue, 15 Oct 2024 13:02:48 GMT

Have you tried this with the join being replaced by a lookup?

Re: Splunk query optimization

uagraw01 — Tue, 15 Oct 2024 13:07:50 GMT

@ITWhisperer Yes, I replace join with lookup command. Do you want me to try something more to optimize further?

Re: Splunk query optimization

ITWhisperer — Tue, 15 Oct 2024 13:09:55 GMT

Without knowledge of your data, I can't see any further optimisations you might try