Splunk Search

Intergrate data between two indexes

phamxuantung
Communicator

Hi, I have a search as such but it don't show the results I want

 

(index="index1")
OR (index="index2")
|search date_hour>20 OR date_hour<5
|eval MERCHANT_CODE1=mvdedup(mvappend(CODE, MERCHANT_CODE))
|eval SUCCESS=if(RESPONSE_CODE="0",1,0)
|stats count AS Total_night SUM(SUCCESS) AS SUCCESS_TOTAL BY MERCHANT_CODE1, ACQ_BANK
|eval SUCCESS_RATE=round(SUCCESS/Total_night*100,2)
|search SUCCESS_RATE>=70
|table MERCHANT_CODE1, SUCCESS_RATE, ACQ_BANK, Total_night, Total

 

The requirement is merchants that have more than 70% success transactions in the time range from 20h to 5h in a month.
The table results require as above. 
My problem is the transactions data is at index1, the ACQ_BANK for each merchant is at index2, I want to show them at the table without the manual checking for each one, I also want to show the Total success for a month but with the code above I don't have any result. If I don't include index2 and remove ACQ_BANK after the BY, I have the result I want.

If anyone have a suggestion I would really appreciate.

 

Labels (3)
0 Karma
1 Solution

manjunathmeti
SplunkTrust
SplunkTrust

Remove search time filter in the beginning. You can use stats with eval expression.

 

(index="index1")
OR (index="index2")
|eval MERCHANT_CODE1=mvdedup(mvappend(CODE, MERCHANT_CODE))
|stats count AS Total count(eval(date_hour>20 OR date_hour<5)) as Total_night count(eval(RESPONSE_CODE="0")) AS SUCCESS_TOTAL BY MERCHANT_CODE1, ACQ_BANK
|eval SUCCESS_RATE=(SUCCESS_TOTAL*100)/Total_night
|search SUCCESS_RATE>=70
|table MERCHANT_CODE1, SUCCESS_RATE, ACQ_BANK, Total_night, Total

 

 

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

View solution in original post

manjunathmeti
SplunkTrust
SplunkTrust

hi @phamxuantung,

You need to calculate SUCCESS_RATE.

(index="index1")
OR (index="index2")
|search date_hour>20 OR date_hour<5
|eval MERCHANT_CODE1=mvdedup(mvappend(CODE, MERCHANT_CODE))
|stats count AS Total_night count(eval(RESPONSE_CODE="0")) AS SUCCESS_TOTAL BY MERCHANT_CODE1, ACQ_BANK
|eval SUCCESS_RATE=(SUCCESS_TOTAL*100)/Total_night
|search SUCCESS_RATE>=70
|table MERCHANT_CODE1, SUCCESS_RATE, ACQ_BANK, Total_night, Total

 

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

phamxuantung
Communicator

Sorry, the code I post was lacking the calculation of the success rate. My main problem lie in the Total and the Total_night

0 Karma

manjunathmeti
SplunkTrust
SplunkTrust

How is Total calculated? Is it available in the index?

Tags (1)
0 Karma

phamxuantung
Communicator

Total is the total success transaction in that month, Total_night is the total success transactions between 20h and 5h, RESPONSE_CODE="0" is the success transaction indicator. Since I specify the search time at the beginning (the date_hour part), I can't count the Total.

0 Karma

manjunathmeti
SplunkTrust
SplunkTrust

Remove search time filter in the beginning. You can use stats with eval expression.

 

(index="index1")
OR (index="index2")
|eval MERCHANT_CODE1=mvdedup(mvappend(CODE, MERCHANT_CODE))
|stats count AS Total count(eval(date_hour>20 OR date_hour<5)) as Total_night count(eval(RESPONSE_CODE="0")) AS SUCCESS_TOTAL BY MERCHANT_CODE1, ACQ_BANK
|eval SUCCESS_RATE=(SUCCESS_TOTAL*100)/Total_night
|search SUCCESS_RATE>=70
|table MERCHANT_CODE1, SUCCESS_RATE, ACQ_BANK, Total_night, Total

 

 

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

Get Updates on the Splunk Community!

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 ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...