Splunk Search

need better option than join

Aishanazam
Loves-to-Learn Lots

 

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

Labels (1)
0 Karma

isoutamo
SplunkTrust
SplunkTrust

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
Motivator

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

 

0 Karma

morethanyell
Builder

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"

```

0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...