topic Re: Mismatch with values in Join in Splunk Search

Mismatch with values in Join

tlunruh — Thu, 14 Jun 2018 19:34:42 GMT

When I run this query:
index=edi-2 | join type=inner TRACKINGNUMBER [search index=edi | rename TRCK AS TRACKINGNUMBER] | stats count(TRACKINGNUMBER) BY STYPE

I get these results:

STYPE count(TRACKINGNUMBER) 01 2140
08 38
10 284
13 1122
22 539
25 349
29 4
32 236

However, if I add this where clause:
index=edi-2 | join type=inner TRACKINGNUMBER [search index=edi | rename TRCK AS TRACKINGNUMBER | where STYPE=08 OR STYPE=29] | stats count(TRACKINGNUMBER) BY STYPE

I get this:

STYPE count(TRACKINGNUMBER)
08 166
29 4572

WTH?? How is that possible? Shouldn't 08 and 29 match up with the first set of results?? What am I missing with this?

Thanks!

Re: Mismatch with values in Join

somesoni2 — Thu, 14 Jun 2018 20:27:51 GMT

With type=inner in join command, you'd see matching records from both the sources (main search and subsearch), hence the result is limited to only the STYPE=08 OR STYPE=29. The joins are very expensive, and you should look for alternative implementation for it.

If you have to use join, use type=left so that all rows from left (main search) will be returned, along with matched ones.

Alternatively, you can use following:

index=edi-2 OR index=edi | eval TRACKINGNUMBER=coalesce(TRACKINGNUMBER, TRCK)
| stats count(TRACKINGNUMBER)  values(index) as indexes BY STYPE

Re: Mismatch with values in Join

tlunruh — Thu, 14 Jun 2018 21:29:13 GMT

Thanks @somesoni2. I ran your new search and it gave me different data, but I am still not sure it is right. I am sure I am trying to use Splunk JOINs like SQL (which I am very familiar with) and not understanding the overhead/results. What I am really trying to get (for the first pass), is the STYPE from index=edi for each matching TRACKINGNUMBER in index=edi-2.

index=edi has TRCK and STYPE fields; index=edi-2 has TRACKINGNUMBER and DATE fields. I want to JOIN (or use an alternative) the two indexex on TRACKINGNUMBER/TRCK and return the count of STYPE. Does that make sense?

Re: Mismatch with values in Join

youngc_splunk — Thu, 21 Nov 2024 08:17:49 GMT

@tlunruh , it looks like your processing EDI data. We now have solution accelerator for EDI transactions, I would love to share what we have. Let me know if you're interested.