How to Combine Multiple Data Sources in Pie Chart Visualization?

[devanoverstock]

I have a data sources that shows if an order was resolved as fraudulent (data="resolutions") 

and in a different data source (data="headers") i have payment_method (Visa, Mastercard, etc) 

I want to see a pie chart of only orders that have chargebacks on them by payment method. 

data=headers
| top payment_method

This works for the pie chart of payment method. I tried:

data=headers OR data=resolutions resolution_name="ACM Chargeback Received - Fraud" 
| top payment_method

and a few other variations, but I can't seem to get it to work. 

Even if I can't do a pie chart and could figure out a table with 

Payment Method | Count of Resolution Name (chargeback) that would work

[devanoverstock]

I used tran_id but unfortunately it brought up no results for the pie chart, it did bring up results in the search but only resolution results. 

(data=headers OR data=resolutions) resolution_name="ACM Chargeback Received - Fraud"
| eventstats values(data) AS datasources by tran_id
| where isnotnull(mvfind(datasources, "resolutions"))
| top payment_method

[devanoverstock]

Thank you! This is getting closer, that still returned 0 results, however I altered it a bit and was able to get PayPal and Credit Card (which is to be expected) the only issue now is it's returning about 10% of the numbers it should be returning. Unsure why. This is how I altered it. 

data=headers OR (data=resolutions resolution_name="ACM Chargeback Received - Fraud")
| eventstats values(data) AS datasources by tran_id
| where isnotnull(mvfind(datasources, "resolutions"))
| top payment_method

[yuanliu · Accepted solution]

You want to test data separately, then, to compare to expected values.  Before doing that, let me confirm that the total number of payment_method is less than 10.  Correct?

How to test data depends on how you derive at the 10% estimation.  For example, if search

data=resolutions resolution_name="ACM Chargeback Received - Fraud"
| stats dc(tran_id)

returns 100 (unique transactions) but the combined search only total 10, maybe data=header is missing some events.  You can try

data=headers OR (data=resolutions resolution_name="ACM Chargeback Received - Fraud")
| eventstats values(data) AS datasources by tran_id
| where isnull(mvfind(datasources, "headers"))

to determine those missed tran_id's.  You can select one such tran_id and do a search like

data=header tran_id=<missed tran_id>

to see if it is truly missing, and investigate from there.

[devanoverstock]

Thanks for all the help. I figured it out. I needed to have different time frames for each query as resolutions column is an update column and comes up to 30 days after the headers column.

[yuanliu]

My mistake in grouping.  Try this

data=headers OR data=resolutions resolution_name="ACM Chargeback Received - Fraud"
| eventstats values(data) AS datasources by tran_id
| where isnotnull(mvfind(datasources, "resolutions"))
| top payment_method

This is equivalent to data=headers OR (data=resolutions AND resolution_name="ACM Chargeback Received - Fraud") .

[yuanliu]

Your examples are missing the link (common key) between the two data sets.  Assume that it is a field called orderId, something like this may get what you need:

(data=headers OR data=resolutions) resolution_name="ACM Chargeback Received - Fraud"
| eventstats values(data) AS datasources by orderId
| where isnotnull(mvfind(datasources, "resolutions"))
| top payment_method