Splunk Join Command not working as expected

[punyanit]

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

[gcusello]

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

[punyanit]

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

[to4kawa]

what's the difference.

[punyanit]

@to4kawa i have that in my question now