Merging and counting 3 data sets

chaalz — Tue, 12 Jan 2021 03:08:36 GMT

I have 3 data sets that I'm trying to merge and count.

Data set 1

Data Set 2

Data Set 3

some-id | error-code | error-descr | my-type
100 | 900 | descr for 900 | ERR
200 | 922 | descr for 922 | ERR
200 | 923 | descr for 923 | ERR

========

Results I’m trying to get:

COMPANY_ID | COMPANY_NAME | GROUP  | ERR_CODE   | ERR_DESCR | COUNT | PERCENT
8634535 | Target | ABC | 900 | descr for 900 | 5 | 10
0583509 | Disney | EFG | 922 | descr for 922 | 10 | 20
0583509 | Disney | EFG | 923 | descr for 923 | 10 | 20
2095497 | Starbucks | |   |   | 23 | 46
6433241 | Microsoft | XYZ |   |   | 2 | 4

I've tried joining the data but I only seem to get rows where data is available in all data sets. My counts are off. It looks like the results of the last join are just being repeated. In the joins I specify JOIN_ID since the values are stored in different fields (field my-id in data set 1 and field some-id in data sets 2 and 3). Maybe this is the issue?

My search:

index="index1" my-type="COMP"
| rename my_id as JOIN_ID, company_id as COMPANY_ID, company_name as COMPANY_NAME

| join type=left max=10 JOIN_ID [search index="index2" my-type="GROUP"
| table my-group-name
| rename my-group-name as GROUP ]

| join type=left max=10 JOIN_ID [search index="index2" my-type="ERR"
| table error-code, error-descr
| rename error-code as ERR_CODE, error-descr as ERR_DESCR]

top COMPANY_ID , COMPANY_NAME , GROUP , ERR_CODE , ERR_DESCR
| rename count as COUNT, percent as PERCENT | eval PERCENT=round(PERCENT,2) | addcoltotals COUNT

I tried top and stats as well but same results. Any pointers? Thank you.

Re: Merging and counting 3 data sets

to4kawa — Sat, 16 Jan 2021 01:46:59 GMT

| makeresults | eval _raw="my_id,company_id,company_name,my_type 100,8634335,Target,COMP 200,0583509,Disney,COMP 300,2095497,Starbucks,COMP 400,6433241,Microsoft,COMP" | multikv forceheader=1 | table my_id,company_id,company_name,my_type | append [ | makeresults | eval _raw="some_id,my_group_name,my_type 100,ABC,GROUP 200,EFG,GROUP 400,XYZ,GROUP" | multikv forceheader=1 | table some_id,my_group_name,my_type] | append [ | makeresults | eval _raw="some_id,error_code,error_descr,my_type 100,900,descr for 900,ERR 200,922,descr for 922,ERR 200,923,descr for 923,ERR" | multikv forceheader=1 | table some_id,error_code,error_descr,my_type] | eval some_id=coalesce(my_id,some_id) | stats values(*) as * by some_id | fields - my_type error_code | mvexpand error_descr | eval error_code=mvindex(split(error_descr," "),-1) | table company_id company_name my_group_name error_code error_descr

try stats

topic Re: Merging and counting 3 data sets in Splunk Search

Merging and counting 3 data sets

Re: Merging and counting 3 data sets