topic Re: Using sub search result to add to main search in Splunk Search

Using sub search result to add to main search

phamxuantung — Tue, 02 Mar 2021 16:10:05 GMT

Hi, I have a main search that look like this

index=main RESPONSE_CODE="0" earliest =-4mon@mon latest=mon@mon |stats count AS Total_success BY MERCHANT_CODE

This will produce a table that has each merchant and their sale for 4 months.

The sub-search that I want to incorporate is

index=backend earliest_time=@d | table CODE ACQ_BANK

This table has the merchant code (which is the same as above MERCHANT_CODE) and their corresponding bank. And because the data need to be update daily, I limit the search to the latest possible.

I want to produce a table that have that have 3 columns, which are MERCHANT_CODE, Total_success, ACQ_BANK.

Thank you in advance.

Re: Using sub search result to add to main search

richgalloway — Wed, 03 Mar 2021 12:51:03 GMT

Ideally, the backend index would be a lookup table, updated daily, that the first search would use to map MERCHANT_CODE to ACQ_BANK.

Failing that, I'd use append rather than a subsearch.

index=main RESPONSE_CODE="0" earliest =-4mon@mon latest=mon@mon |stats count AS Total_success BY MERCHANT_CODE | append [search index=backend earliest_time=@d | rename CODE as MERCHANT_CODE | table MERCHANT_CODE ACQ_BANK ] | stats values(*) as * by MERCHANT_CODE | table MERCHANT_CODE, Total_success, ACQ_BANK

Re: Using sub search result to add to main search

phamxuantung — Wed, 03 Mar 2021 08:15:32 GMT

Hello richgalloway,

Thank you for your reply, I applied your code but the ACQ_BANK column is blank, and it even make the result incorrect. To clarify, my complete query is (after adding yours)

index=index1 RESPONSE_CODE="0" | stats count AS Total_trans count(eval(date_hour>=20 OR date_hour<=5)) AS Total_trans_night BY MERCHANT_CODE |eval RATE=round((Total_trans_night/Total_trans)*100,2) |search RATE>70 |append [search index=backend earliest=-d@d latest=now |rename CODE AS MERCHANT_CODE |table MERCHANT_CODE, ACQ_BANK ] |stats values(*) AS * BY MERCHANT_CODE |table MERCHANT_CODE, ACQ_BANK, Total_trans_night, RATE

Re: Using sub search result to add to main search

richgalloway — Wed, 03 Mar 2021 12:56:47 GMT

How is the result incorrect?

Have you run each search separately to verify they return the expected results? If they do, then run the full query without the last stats command. This should shed light on where the query is failing.

Re: Using sub search result to add to main search

phamxuantung — Thu, 04 Mar 2021 03:48:52 GMT

The search ran fine individually. Without the last (stats) include , the result look normal but with the ACQ-BANK blank. With the (stats) however, the result look really weird, it end up show all the merchants and ACQ_BANK even if they don't pass the RATE>70 I set above, most of them are blank, with a few have result, it look like this

And I know the result is wrong because I compare it with the original search

Re: Using sub search result to add to main search

scelikok — Thu, 04 Mar 2021 08:18:50 GMT

Hi @phamxuantung,

Why don't you try using join like below?

index=index1 RESPONSE_CODE="0" | stats count AS Total_trans count(eval(date_hour>=20 OR date_hour<=5)) AS Total_trans_night BY MERCHANT_CODE | eval RATE=round((Total_trans_night/Total_trans)*100,2) | search RATE>70 | join MERCHANT_CODE [ search index=backend earliest=-d@d latest=now | dedup CODE | rename CODE AS MERCHANT_CODE | table MERCHANT_CODE, ACQ_BANK ] | stats values(*) AS * BY MERCHANT_CODE | table MERCHANT_CODE, ACQ_BANK, Total_trans_night, RATE

Re: Using sub search result to add to main search

richgalloway — Thu, 04 Mar 2021 13:27:16 GMT

The appended search does not filter by RATE, which it can't since it does not have that field. To fix that, move the RATE>70 test to after the last stats command.