Splunk Search

Splunk Join Command not working as expected

punyanit
Path Finder

Hello everyone,

I am trying to join using "Table" as common field, here is my query.

index=prod source=A
| stats count by PROD TABLENAME_PROD Partition_Column_PROD INI_TRANS_PROD Table Column Trans
| sort TABLENAME_PROD
| join type=left Table
[ search index=preprod source=B
| stats count by CAP TABLENAME_CAP Partition_Column_CAP INI_TRANS_CAP Table Column Trans
|sort TABLENAME_CAP
]
| table Partition_Column_PROD Partition_Column_CAP

The values that i am getting here is not matching to those if i run both commands separately and join there output manually(keeping Table as common field)

I.e values of Partition_Column_CAP,Partition_Column_PROD , of this query should match with values of Partition_Column_CAP , Partition_Column_PROD which i will get if i would run these queries separately .

original Output of above query
Partition_Column_PROD Partition_Column_CAP
(ACS_ID, ACS_ID) (ACS_ID)
(ADDR_ID, ADDR_ID) (ADDR_ID)
(CITY, CITY, ADDR_ID, ADDR_ID) (ADDR_ID)
(ALFRESCO_MSTR_REC_ID) (ALFRESCO_MSTR_REC_ID)
(APPL_ID, APPL_ID) (APPL_ID)
(ACS_METHD_ID, ACS_METHD_ID) (ACS_METHD_ID)
(APPL_CMPNT_ID, APPL_CMPNT_ID) (APPL_CMPNT_ID)
(CMPNT_TYP_ID, CMPNT_TYP_ID) (APPL_CMPNT_ID)

Expected output

Partition_Column_PROD Partition_Column_CAP
(ACS_ID, ACS_ID) (ACS_ID)
(ADDR_ID, ADDR_ID) (ADDR_ID)
(CITY, CITY, ADDR_ID, ADDR_ID) (CITY, ADDR_ID)
(ALFRESCO_MSTR_REC_ID) (ALFRESCO_MSTR_REC_ID)
(APPL_ID, APPL_ID) (APPL_ID)
(ACS_METHD_ID, ACS_METHD_ID) (ACS_METHD_ID)
(APPL_CMPNT_ID, APPL_CMPNT_ID) (APPL_CMPNT_ID)
(CMPNT_TYP_ID, CMPNT_TYP_ID) (CMPNT_TYP_ID)

In above results Partition_Column_PROD and Partition_Column_CAP are from both searches(search and sub search) which is joined manually.

There are no repetitive values for the second part of search query ,for ex the field Partition_Column_CAP has these 3 different values
(ACS_ID) , (ADDR_ID) ,(CITY, ADDR_ID) and each event has unique value

now when i add this second part of search query to the join command i start seeing repetitive values for the same mentioned
field mentioned above, which should not be the case

Tags (2)
0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @punyanit,
only one question: if you run the second search (the subsearch) separately, how many results do you have?
because there's the limit of 50,000 results in subsearches, so, maybe this is your problem.
For this reson you should try to use a different approach (stats command) instead join that's in addition a very slow command.
Something like this:

(index=prod source=A) OR (index=preprod source=B)
| eval PROD=coalesce(PROD,CAP), TABLENAME_PROD=coalesce(TABLENAME_PROD,TABLENAME_CAP),  Partition_Column_PROD=coalesce(Partition_Column_PROD,Partition_Column_CAP), INI_TRANS_PROD=coalesce(INI_TRANS_PROD,INI_TRANS_CAP)
| stats count by PROD TABLENAME_PROD Partition_Column_PROD INI_TRANS_PROD Table Column Trans
| sort TABLENAME_PROD

Ciao.
Giuseppe

0 Karma

punyanit
Path Finder

@gcusello thank you for effort, my subsearch is returning only 315 results in last 24hrs

0 Karma

to4kawa
Ultra Champion

what's the difference.

0 Karma

punyanit
Path Finder

@to4kawa i have that in my question now

0 Karma
Get Updates on the Splunk Community!

Detecting Remote Code Executions With the Splunk Threat Research Team

WATCH NOWRemote code execution (RCE) vulnerabilities pose a significant risk to organizations. If exploited, ...

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...