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!

Security Highlights | November 2022 Newsletter

 November 2022 2022 Gartner Magic Quadrant for SIEM: Splunk Named a Leader for the 9th Year in a RowSplunk is ...

Platform Highlights | November 2022 Newsletter

 November 2022 Skill Up on Splunk with our New Builder Tech Talk SeriesCan you build it? Yes you can! *play ...

Splunk Education - Fast Start Program!

Welcome to Splunk Education! Splunk training programs are designed to enable you to get started quickly and ...