Splunk Search

Using sub search result to add to main search

phamxuantung
Communicator

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, Karma would be appreciated.

View solution in original post

scelikok
SplunkTrust
SplunkTrust

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 and "Accept as Solution" 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, Karma would be appreciated.

phamxuantung
Communicator

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, Karma would be appreciated.
0 Karma

phamxuantung
Communicator

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, Karma would be appreciated.
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...