Can you help me to create a join in a lookup file?

[kingwaras]

Hi all,

I need your help.
I created a lookup file (hierarchy_lookup.csv) with this layout

I would like to create a dashboard that, in the multiselect list view, the EnterpriseID presents in the lookup file that has a common field (Scope, Module) of the current user logged into Splunk.

In my case for example (line 4 & 5), I have two module (DWH and BW). I need to view all EnterpriseID that have my same Module and Scope.
I have tried to generate the query, but it is not working very well.

| inputlookup hierarchy_lookup.csv
|  where [| rest /services/authentication/current-context 
                      | table username
                      | rename username as EnterpriseID]
| fields Scope, Module, EnterpriseID
| join Scope, Module type=inner [
      | inputlookup hierarchy_lookup.csv
      | stats count by Scope,Module
  ]

In this case, I would like that my query return

[SCOPE] ; [MODULE] ; [ENTERPRISEID] ; [COUNT]
Logistica ; DWH ; myEnterpriseID (line 4 of lookup) ; 1
Logistica ; BW ; myEnterpriseID (line 5 of lookup) ;1
Logistica ; BW ;EnterpriseID (line 3 of lookup) ; 1
Logistica ; BW ;EnterpriseID (line 6 of lookup) ; 1
Logistica ; DWH ;EnterpriseID (line 7 of lookup) ; 1

I hope I have explained my problem well.
Thanks in advance.

[dmarling · Accepted solution]

Without having an equivalent lookup to play with, I found a couple syntax issues with the first part of search you wrote. Can you give this a try to see if this produces the same table as your first screen shot?

| inputlookup hierarchy_lookup.csv 
| where 
    [| rest /services/authentication/current-context 
    | where username!="splunk-system-user" 
    | rename username as EnterpriseID 
    | table EnterpriseID 
    | format] 
| fields Scope, Module, EnterpriseID 
| join Scope, Module type=inner 
    [| inputlookup hierarchy_lookup.csv 
    | stats count by Scope,Module]

As for the second part, the request is to have a table automatically display the same scope and model for all users that share the Scope and Module as that user. I believe the below search would accomplish that to produce the table you listed out:

| inputlookup hierarchy_lookup.csv 
| where 
    [| inputlookup hierarchy_lookup.csv 
    | where 
        [| rest /services/authentication/current-context 
        | where username!="splunk-system-user" 
        | rename username as EnterpriseID 
        | table EnterpriseID 
        | format] 
    | table Scope Module 
    | format] 
| fields Scope, Module, EnterpriseID 
| join Scope, Module, EnterpriseID type=inner 
    [| inputlookup hierarchy_lookup.csv 
    | stats count by Scope Module EnterpriseID]

[dmarling]

I have moved this from a comment to an answer. Please accept the answer when you get a moment. Thank you!

[kingwaras]

Hi @dmarling,
you can convert this to an answer... It's working very well!!

Thank you so much for your help.