Splunk Search

Using a lookup table to fill multiple subsearches to show hierarchy of user data

cblanton
Communicator

I have a lookup table that shows all the next-level managers of a particular manager as UserManager UserManagerx1 UserManagerx2 UserManagerx3... UserManagerx20. The top-level manager has about 20 nested managers, but all others have far fewer.

My indexed data has only data on the direct user and manager.

I have a dashboard select input to select the top-level manager that the user is interested in. I'm trying to create a search that will then display data of all direct report users as well as all levels below that manager via the lookup table.

This isn't working as expected. Thanks for any suggestions.

 index="disk_index" 
        [| inputlookup sdmanager.csv 
        | table UserManager] 
 | search blah blah blah 
    | append 
        [ search index="disk_index" UserManager="$top_manager$" 
    ] 
    | append 
        [search index="disk_index" UserManager="$top_manager$" 
        | lookup sdmanager.csv UserManager OUTPUT UserManagerx1 as UserManager 
     ] 
    | append 
        [ search index="disk_index" UserManager="$top_manager$" 
        | lookup sdmanager.csv UserManager OUTPUT UserManagerx2 as UserManager 
      ] 

and so on until

| append 
    [ search index="disk_index" UserManager="$top_manager$"  
    | lookup sdmanager.csv UserManager OUTPUT UserManagerx20 as UserManager 
 ] 
| stats sum(UserUsage) as TotalUserUsage by UserManager DiskUser

And then I've also tried like this...

index="disk_index" 
    [| inputlookup sdmanager.csv 
    | table UserManager] 
| search blah blah blah 
| join type=inner 
    [| search UserManager="LAST,FIRST" 
    | stats sum(UserUsage) as TotalUserUsage by UserManager configuration process DiskUser ] 
| join type=outer 
    [ | inputlookup sdmanager.csv | search UserManager="LAST,FIRST" | fields UserMangerx1 
    | stats sum(UserUsage) as TotalUserUsage by UserManager configuration process DiskUser ] 

| table UserManager configuration process DiskUser TotalUserUsage

My next idea is to try map, but I've never used it and it seems widely discouraged. Perhaps my lookup should be formatted differently? Or a foreach command? Thanks again

1 Solution

to4kawa
Ultra Champion
index="disk_index" 
    [| inputlookup sdmanager.csv 
     | foreach UserManager UserManagerx*  [ eval MatchUser=mvappend(MatchUser,<<FIELD>>)]
     | where match(MatchUser,"$UserManager$")
     | table UserManager] 
| stats sum(UserUsage) as TotalUserUsage by UserManager ipArchitecture process configuration drop DiskUser 
| table UserManager ipArchitecture process configuration drop DiskUser TotalUserUsage

Hi, @cblanton
How about this?
for the selected manager and then append the same search with each UserManagerx*n* from the lookup
Does this achieve your goal?

View solution in original post

to4kawa
Ultra Champion
index="disk_index" 
    [| inputlookup sdmanager.csv 
     | foreach UserManager UserManagerx*  [ eval MatchUser=mvappend(MatchUser,<<FIELD>>)]
     | where match(MatchUser,"$UserManager$")
     | table UserManager] 
| stats sum(UserUsage) as TotalUserUsage by UserManager ipArchitecture process configuration drop DiskUser 
| table UserManager ipArchitecture process configuration drop DiskUser TotalUserUsage

Hi, @cblanton
How about this?
for the selected manager and then append the same search with each UserManagerx*n* from the lookup
Does this achieve your goal?

to4kawa
Ultra Champion

I see,
but, where OR how does UserManagerxn display.
I'm not sure the relation DiskUser and UserManagerxn.

index="disk_index" 
     [| inputlookup sdmanager.csv 
      | search UserManager="$UserManager$"
      | table UserManager] 
| lookup sdmanager.csv UserManager OUTPUTNEW
| foreach UserManagerx* [ eval UserManager=mvappend(UserManager,<<FIELD>>)]
| mvexpand UserManager
| stats sum(UserUsage) as TotalUserUsage by UserManager ipArchitecture process configuration drop DiskUser
| table UserManager ipArchitecture process configuration drop DiskUser TotalUserUsage

Is this it? OR

 index="disk_index" 
      [| inputlookup sdmanager.csv 
       | search UserManager="$UserManager$"
       | foreach UserManagerx* [ eval UserManager=mvappend(UserManager,<<FIELD>>)]
      | stats count by UserManager
      | table UserManager] 
 | stats sum(UserUsage) as TotalUserUsage by UserManager ipArchitecture process configuration drop DiskUser
 | table UserManager ipArchitecture process configuration drop DiskUser TotalUserUsage
0 Karma

cblanton
Communicator

It was the last one that worked.

  index="disk_index" 
       [| inputlookup sdmanager.csv 
        | search UserManager="$UserManager$"
        | foreach UserManagerx* [ eval UserManager=mvappend(UserManager,<<FIELD>>)]
       | stats count by UserManager
       | table UserManager] 
  | stats sum(UserUsage) as TotalUserUsage by UserManager ipArchitecture process configuration drop DiskUser
  | table UserManager ipArchitecture process configuration drop DiskUser TotalUserUsage
0 Karma

to4kawa
Ultra Champion

I see.
After all, it is easy to do if logs and examples are presented.
Thank you for your reply.

0 Karma

cblanton
Communicator

that does it! thank you so much!

0 Karma

cblanton
Communicator

DiskUser is irrelevant.

I just want to look up all the corresponding UserManagerxn for the token selected UserManager and append the search with UserManager=UserManagerxn.

0 Karma

cblanton
Communicator

So if I try this, with my pretend csv. If the token passes NOBEL,ALFRED as the UserManager I expect to get all search results with NOBEL,ALFRED as UserManager plus PAGE,LARRY and MEAD,MARGARET, but instead I get only NOBEL,ALFRED and ROGERS,FRED. So it's finding the match in the UserManagerxn field and returning the UserManager, instead of matching the UserManager and returning all the UserManagerxn.

0 Karma

to4kawa
Ultra Champion

sdmanager.csv

UserManager,UserManagerx1,UserManagerx2,UserManagerx3,UserManagerx4,UserManagerx5,UserManagerx6,UserManagerx7
"ROGERS,FRED","SIMPSON,BART","ANDREWS,JULIE","CURIE,MARIE","NOBEL,AFLRED","PAGE,LARRY","MEAD,MARGRET","PAL,CHANDRANI"
"SIMPSON,BART","ROGERS,FRED","ANDREWS,JULIE","CURIE,MARIE","PAGE,LARRY","MEAD,MARGRET",,
"ANDREWS,JULIE","CURIE,MARIE",,,,,,
"CURIE,MARIE",,,,,,,,
"NOBEL,AFLRED","PAGE,LARRY","MEAD,MARGRET",,,,,
"PAGE,LARRY",,,,,,,
"MEAD,MARGRET",,,,,,,

I create the csv and lookup.

The following is my perception

  1. DiskUser specifies UserManager.
  2. UserManager has UserManagerx n , so also display the UserManagerx n DiskUser
  3. Select a specific UserManager from the dashboard

Query of this assumption:

| makeresults
| eval DiskUser=split("abc234,del567,cureim,smthb,arst876,sdkji8,asdre8,skjyu876",",")
| mvexpand DiskUser 
| eval UserManager=mvindex(split("ROGERS,FRED#SIMPSON,BART#ANDREWS,JULIE#CURIE,MARIE#NOBEL,AFLRED#PAGE,LARRY#MEAD,MARGRET","#"),random() % 7 )
| table DiskUser UserManager
| fillnull process configuration UserUsage
| foreach process configuration UserUsage [eval <<FIELD>> = random() % 100]
| rename COMMENT as "This is sample you provide. From here, the logic"
| lookup sdmanager.csv UserManager OUTPUTNEW
| foreach UserManager UserManagerx*  [ eval MatchUser=mvappend(MatchUser,<<FIELD>>)]
| where match(MatchUser,"CURIE,MARIE")
| fields - MatchUser UserManagerx*

"CURIE,MARIE" assumes a token.
As a result, the DiskUser whose "CUIRE,MARIE" is each managers is selected.

Is my understanding correct?

cblanton
Communicator

DiskUser does not specify UserManager. DiskUser is a field in the indexed data and no more relevant to the search than any other field that I'm tabling, like configuration process DiskUser... UserManager is a field in the indexed data and in the lookup table that has matching names.

I only need this simple search to run for the selected manager and then append the same search with each UserManagerx*n* from the lookup. And none of the fields I'm tabling are null.

 index="disk_index" 
        [| inputlookup sdmanager.csv 
        | table UserManager] 
    | search UserManager="$UserManager$"
    | stats sum(UserUsage) as TotalUserUsage by UserManager ipArchitecture process configuration drop DiskUser
    | table UserManager ipArchitecture process configuration drop DiskUser TotalUserUsage
0 Karma

cblanton
Communicator

alt text

alt text

0 Karma

to4kawa
Ultra Champion

Can you provide your csv sample?

0 Karma

cblanton
Communicator

UserManager UserManagerx1 UserManagerx2 UserManagerx3 ... UserManagerx20
LAST,FIRST LAST,FIRST LAST,FIRST LAST,FIRST LAST, FIRST
LAST,FIRST LAST,FIRST LAST,FIRST
LAST,FIRST LAST,FIRST

LAST,FIRST LAST,FIRST LAST,FIRST LAST,FIRST

The dashboard input selects from all names listed as UserManager. I need corresponding user data for UserManger/DiskUser pairs for all existing UserManagerx#.

Thanks

0 Karma

to4kawa
Ultra Champion
index="disk_index" 
         [| inputlookup sdmanager.csv 
         | table UserManager] 

this search is same index="disk_inde UserManager= LAST,FIRST OR UserManager= LAST,FIRST ...
I'm not sure
why many append output same UserManager field.
and , what's DiskUser?
LAST,FIRST of same row is same?

0 Karma

cblanton
Communicator

UserManager is a field in the lookup and in the index.

DiskUser is the employee name, a direct report to the manager, and a field in the indexed data

Each LAST,FIRST is a different name.
alt text

So if UserManager Andrews,Julie is chosen in the drop down, I want to return all events where she is the manager and also all the managers that report to her, listed in the lookup as UserManagerx1 and so on.

alt text

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...