How can I populate an event with data from another event?

[AbhishekJ]

Hi,
 
I have set of events from an index with user details as below and I am looking to populate the events with there manager Name .

ID	Name	MgrID
1	Tom	4
2	Rick	1
3	Harry	1
4	Boss	5
5	CEO	5

I want to add another column to the result with MgrName like below using the MgrID and re-referencing the same index again:

ID	Name	MgrID	MgrName
1	Tom	4	Boss
2	Rick	1	Tom
3	Harry	1	Tom
4	Boss	5	CEO
5	CEO	5	CEO

Tried to come up with something and so far no luck, appreciate if someone has any suggestions or have done this before.

Thanks

[johnhuang]

1. Run once (initially) to create the intial lookup file:

<base_search> earliest=1mon@mon
| dedup ID
| table ID Name MgrID
| outputlookup identity_lookup.csv

 
2. Run this on a schedule (hourly, daily, etc, depending on need) to populate the lookup file with manager name and keep it up to date with identity changes.

<base_search> earliest=-1d
| append [| inputlookup identity_lookup.csv]
| lookup identity_lookup.csv ID AS MgrID OUTPUT Name AS MgrName
| dedup ID
| outputlookup identity_lookup.csv

3. Once the lookup have been populate, you can use lookup against existing data:

-- Resolve MgrName based on ID
| lookup identity_lookup.csv ID OUTPUT Name MgrID MgrName

-- Resolve MgrName based on Name
| lookup identity_lookup.csv Name OUTPUT MgrName

[AbhishekJ]

Thanks, using lookups does make sense, may be I would use a KV Store as I am handling 300000 entries. I will give it a try in Dev env to see how it works out and confirm. 

Any suggestions, if this could this be done with using Subsearch/join/appendcols/macros somehow. 

[johnhuang]

300K entries should not be an issue for a CSV lookup -- worth testing before moving to kvstore.

[johnhuang]

Could you provide the actual fieldnames of ID, Name, and MgrID or confirm that it's already the case?

[AbhishekJ]

Thanks,

The Data is in use for Identity management , I was hoping to get a logic that I can further modify and implement with my actual data.

[bowesmana]

Where does that manager name data exist?

Simple way to do it is via a lookup, where you have two fields, name and manager name and then use lookup to lookup the name and return the manager name.

https://docs.splunk.com/Documentation/Splunk/9.0.1/SearchReference/Lookup

[AbhishekJ]

The data is the same , I need to populate the results from this index by requiring it. 

ID is the unique field and since the primary data contains the ID of the Manager, I need to populate the final results with Mgr Name by utilising the Mgr ID.

Thanks

[bowesmana]

So, do you have the manager name and ID anywhere in Splunk?

[AbhishekJ]

Thanks, its in the same index , was trying to see if there was a way I could use something like subsearches/macros/join or some other way to produce required result in a single SPL:

ID	Name	MgrID
1	Tom	4
2	Rick	1
3	Harry	1
4	Boss	5
5	CEO	5

[wmazur-splunk · Accepted solution]

join version (please check the performance):

<base search for user> 
|join type=outer left=L right=R where L.MgrID = R.ID [search <sub-search for managers>]

`type=outer` to get results even if no manager was found.

Note: If you are going to use custom time ranges in base query ('earliest', 'latest') - these filters are not applied to sub-search, so be careful there. Probably you want to add the same time range criteria to the sub-search

[AbhishekJ]

Thanks,

tried like below 😞 , but didn't work 😞

index=my_data
|eval ChkMgrID=MgrID 
|join type=outer left=L right=R where L.ChkMgrID = R.ID 
[search index=my_data | eval MgrName=Name | table MgrName]
| table ID Name MgrID MgrName

 Time range last 24 hours , reading about join as well .

[wmazur-splunk]

Sub-search:

| table ID, MgrName

Hint: Depending on event size:  drop this part of the search or replace with

 | fields ID, MgrName 

[AbhishekJ]

Thanks, this works like the way I was hoping it for: 

index=my_data
|eval ChkMgrID=MgrID 
|join type=outer left=L right=R where L.ChkMgrID = R.ID 
[search index=my_data | eval MgrName=Name | fields ID MgrName]
| table L.ID L.Name L.MgrID R.MgrName

 
I would further modify it to get 3 levels of leadership in the organisation, but this is something I was looking for.