Splunk Search

Using sub search result to add to main search

phamxuantung
Path Finder

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.

Labels (3)
0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

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.

---
If this reply helps you, an upvote would be appreciated.

View solution in original post

scelikok
Champion

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

 

If this reply helps you an upvote is appreciated.
0 Karma

richgalloway
SplunkTrust
SplunkTrust

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

 

---
If this reply helps you, an upvote would be appreciated.

phamxuantung
Path Finder

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

 

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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.

---
If this reply helps you, an upvote would be appreciated.
0 Karma

phamxuantung
Path Finder

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

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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.

---
If this reply helps you, an upvote would be appreciated.

View solution in original post

Register for .conf21 Now! Go Vegas or Go Virtual!

How will you .conf21? You decide! Go in-person in Las Vegas, 10/18-10/21, or go online with .conf21 Virtual, 10/19-10/20.