need better option than join

[Aishanazam]

Need better option to get user id from first search to populate results using the subsearch.  thought join would work but its not.....suggestions?

index=“something” 

| rex field=userName "\'(?P<userName>.+)\'"

| rex "\/ROOT\/[^\s']+\/(?P<Environment>[^\/]+)\/[^\s\/']+(\s|')"

| search NOT (Environment=eu-central-1 OR Environment=BOLCOM)

| rename commandTime as "Date/Time"

| join userName

    [ search index=okta sourcetype="OktaIM2:user" AND profile.department=*

    | rename profile.employeeNumber as userName, profile.division as Department, profile.title as Title, profile.countryCode AS Country

    | table userName, Department, Title, Country]

| dedup Department, Title, Environment

| table "Date/Time", Department, Title, Environment, Country

[isoutamo]

Here is one old answer which could help you to replace join. 

https://community.splunk.com/t5/Splunk-Search/What-is-the-relation-between-the-Splunk-inner-left-joi...

There are also several conf presentation which explain how to do join without join command.

r. Ismo

[johnhuang]

If you're using okta to enrich the primary search results, you should consider creating a lookup.

To create the lookup (schedule a job to update it periodically):

index=okta sourcetype="OktaIM2:user" AND profile.department=* earliest=-7d@d
| rename profile.employeeNumber as userName, profile.division as Department, profile.title as Title, profile.countryCode AS Country
| dedup userName
| table userName, Department, Title, Country
| outputlookup okta_user_lookup.csv

index=“something” 
| rex field=userName "\'(?P<userName>.+)\'"
| rex "\/ROOT\/[^\s']+\/(?P<Environment>[^\/]+)\/[^\s\/']+(\s|')"
| search NOT (Environment=eu-central-1 OR Environment=BOLCOM)
| rename commandTime as "Dste/Time"
| lookup okta_user_lookup.csv userName OUTPUT Department, Title, Country
| dedup Department, Title, Environment
| table "Date/Time", Department, Title, Environment, Country

[morethanyell]

Untested by try this

```

index=“something”
| rex field=userName "\'(?P<userName>.+)\'"
| rex "\/ROOT\/[^\s']+\/(?P<Environment>[^\/]+)\/[^\s\/']+(\s|')"
| search NOT (Environment=eu-central-1 OR Environment=BOLCOM)
| table userName commandTime
| join type=left userName
[ search index=okta sourcetype="OktaIM2:user" AND profile.department=*
| rename profile.employeeNumber as userName, profile.division as Department, profile.title as Title, profile.countryCode AS Country
| stats c by userName Department Title Country]
| eval _time = strptime(commandTime, "%the %format %here")
| dedup Department Title Environment
| table commandTime userName Department Title Country
| rename commandTime as "Date/Time"

```