topic Re: Intergrate data between two indexes in Splunk Search

Intergrate data between two indexes

phamxuantung — Mon, 22 Feb 2021 04:41:30 GMT

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.

Re: Intergrate data between two indexes

manjunathmeti — Mon, 22 Feb 2021 04:37:29 GMT

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.

Re: Intergrate data between two indexes

phamxuantung — Mon, 22 Feb 2021 04:43:25 GMT

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

Re: Intergrate data between two indexes

manjunathmeti — Mon, 22 Feb 2021 04:48:59 GMT

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

Re: Intergrate data between two indexes

phamxuantung — Mon, 22 Feb 2021 04:58:15 GMT

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.

Re: Intergrate data between two indexes

manjunathmeti — Mon, 22 Feb 2021 05:22:19 GMT

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.