Subsearch join not Working (string size)

[Michell_ctba]

Hi guys
I ask for help for that.
I tried to search according to the query below:

index = ott sourcetype = drm_license
| junction type = internal userId [index = ott  sourcetype = drm_user_return]

The userId field has 128 characters (numbers and letters).
I know that the sub-survey is heavy and will hurt performance, but this survey will be run once a month, so the performance will not be impacting for me.

The search does not match all (drm_license and drm_user-return) even if the fields have equal values.

Example: 1000 events match 600, even though the fields of the other 400 have equal values.

[richgalloway]

The join command is matching on the userId field so neither drm_license nor drm_user_return are considered.

With an inner join, events in the subsearch that do not match events in the outer search are discarded.

Try this alternative search that does not use join.

(index = ott sourcetype = drm_license) OR (index = ott index = ott sourcetype = drm_user_return)
| stats values(*) as * by userId

[Michell_ctba]

Thanks a lot for the help 🙂

Unfortunately, this research did not bring me the expected result.

The scenario looks like this:

I have a "sourcetype" with approximately 1 million users, each user has a field identified as "userhashid".(index = drm sourcetype = drm_user_return)

I have another "sourcetype" with all the licenses generated on the daily platform and each event exists in a field with name "userid".

(index = ott sourcetype = drm_license)

This field is the same as the "userhashid" field of the sourcetype "drm_user_return"

Need something like a join (compare the userid with the hashuserid) so that you can use other fields from the sourcetype "drm_user_return"

To remember.

With the join function, many events with equal fields are not matching. I wonder if there is any alternative to join for this case.

[richgalloway]

Yes, there is an alternative to join, which I provided in my reply.  Here it is again, updated based on your comments.

(index = ott sourcetype = drm_license) OR (index = drm sourcetype = drm_user_return)
| eval userID = coalesce(userid, userhashid)
| stats values(*) as * by userId

[Michell_ctba]

Thanks @richgalloway 

When doing this search, it will be necessary that in the generated table it appears two fields of the sourcetype "drm_user_return".
The fields are:

"retailerUserId" and "ProfileUserId"

[richgalloway]

The above query should return all available fields. Use the table command to specify the fields you want displayed and the order in which to display them.