Knowledge Management

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

AbhishekJ
Explorer

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

Labels (1)
Tags (1)
0 Karma
1 Solution

wmazur-splunk
Splunk Employee
Splunk Employee

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

View solution in original post

johnhuang
Motivator



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
Explorer

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. 

0 Karma

johnhuang
Motivator

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

0 Karma

johnhuang
Motivator

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

0 Karma

AbhishekJ
Explorer

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.

0 Karma

bowesmana
SplunkTrust
SplunkTrust

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

 

0 Karma

AbhishekJ
Explorer

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

 

0 Karma

bowesmana
SplunkTrust
SplunkTrust

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

0 Karma

AbhishekJ
Explorer

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:

IDNameMgrID
1Tom4
2Rick1
3Harry1
4Boss5
5CEO5
0 Karma

wmazur-splunk
Splunk Employee
Splunk Employee

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
Explorer

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 .

0 Karma

wmazur-splunk
Splunk Employee
Splunk Employee

Sub-search:

| table ID, MgrName

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

 | fields ID, MgrName 
0 Karma

AbhishekJ
Explorer

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. 

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...