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!

Splunk Answers Content Calendar, July Edition I

Hello Community! Welcome to another month of Community Content Calendar series! For the month of July, we will ...

Secure Your Future: Mastering Upgrade Readiness for Splunk 10

Spotlight: The Splunk Health Assistant Add-On  The Splunk Health Assistant Add-On is your ultimate companion ...

Observability Unlocked: Kubernetes & Cloud Monitoring with Splunk IM

Ready to master Kubernetes and cloud monitoring like the pros? Join Splunk’s Growth Engineering team on ...