topic Re: need better option than join in Splunk Search

need better option than join

Aishanazam — Tue, 25 Jan 2022 19:43:08 GMT

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

Re: need better option than join

morethanyell — Tue, 25 Jan 2022 20:18:17 GMT

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"

```

Re: need better option than join

johnhuang — Wed, 26 Jan 2022 02:51:27 GMT

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

Re: need better option than join

isoutamo — Wed, 26 Jan 2022 15:01:00 GMT

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-join-and-the/m-p/391288/thread-id/113948

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

r. Ismo