Intergrate data between two indexes

[phamxuantung]

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.

[manjunathmeti]

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]

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

[manjunathmeti]

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

[phamxuantung]

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.

[manjunathmeti · Accepted solution]

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.