topic Re: Populate an event with data from another event in Knowledge Management

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

AbhishekJ — Thu, 20 Oct 2022 14:18:16 GMT

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

Re: Populate an event with data from another event

bowesmana — Thu, 20 Oct 2022 01:30:24 GMT

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

Re: Populate an event with data from another event

johnhuang — Thu, 20 Oct 2022 01:56:18 GMT

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

Re: Populate an event with data from another event

AbhishekJ — Thu, 20 Oct 2022 02:16:52 GMT

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.

Re: Populate an event with data from another event

AbhishekJ — Thu, 20 Oct 2022 02:19:03 GMT

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

Re: Populate an event with data from another event

bowesmana — Thu, 20 Oct 2022 02:33:51 GMT

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

Re: Populate an event with data from another event

johnhuang — Thu, 20 Oct 2022 03:18:57 GMT

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.

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

Re: Populate an event with data from another event

AbhishekJ — Thu, 20 Oct 2022 03:55:00 GMT

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

Re: Populate an event with data from another event

AbhishekJ — Thu, 20 Oct 2022 03:53:06 GMT

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.

Re: Populate an event with data from another event

johnhuang — Thu, 20 Oct 2022 04:34:42 GMT

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

Re: Populate an event with data from another event

wmazur-splunk — Thu, 20 Oct 2022 10:00:15 GMT

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

Re: Populate an event with data from another event

AbhishekJ — Thu, 20 Oct 2022 10:19:47 GMT

Thanks,

tried like below 😞 , but didn't work 😞

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

Re: Populate an event with data from another event

wmazur-splunk — Thu, 20 Oct 2022 10:38:37 GMT

Sub-search:

| table ID, MgrName

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

| fields ID, MgrName

Re: Populate an event with data from another event

AbhishekJ — Thu, 20 Oct 2022 22:28:10 GMT

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

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