Splunk Search

Intergrate data between two indexes

phamxuantung
Path Finder

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
Champion

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
Champion

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
Path Finder

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
Champion

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

Tags (1)
0 Karma

phamxuantung
Path Finder

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
Champion

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

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.