Using sub search result to add to main search

[phamxuantung]

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.

[scelikok]

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

[richgalloway]

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

[phamxuantung]

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

[richgalloway]

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.

[phamxuantung]

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

MERCHANT_CODE|RATE |Total_trans_night|Total_trans|ACQ_BANK
CEB          |88.89|8                |9          |Bank A
CAB          |     |                 |           |Bank B

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

[richgalloway · Accepted solution]

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.