Splunk Search

How to Combine Multiple Data Sources in Pie Chart Visualization?

devanoverstock
Explorer

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

Labels (1)
Tags (1)
0 Karma
1 Solution

yuanliu
SplunkTrust
SplunkTrust

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.

View solution in original post

devanoverstock
Explorer

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
0 Karma

devanoverstock
Explorer

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
0 Karma

yuanliu
SplunkTrust
SplunkTrust

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
Explorer

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.

0 Karma

yuanliu
SplunkTrust
SplunkTrust

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

0 Karma

yuanliu
SplunkTrust
SplunkTrust

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

 

Get Updates on the Splunk Community!

Testing out the OpenTelemetry Collector With raw Data

This blog post is part of an ongoing series on OpenTelemetry. The OpenTelemetry project is the second largest ...

New Cloud Intrusion Detection System Add-on for Splunk

In July 2022 Splunk released the Cloud IDS add-on which expanded Splunk capabilities in security and data ...

Happy CX Day to our Community Superheroes!

Happy 10th Birthday CX Day!What is CX Day? It’s a global celebration recognizing innovation and success in the ...