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
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
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
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.
300K entries should not be an issue for a CSV lookup -- worth testing before moving to kvstore.
Could you provide the actual fieldnames of ID, Name, and MgrID or confirm that it's already the case?
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.
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
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
So, do you have the manager name and ID anywhere in Splunk?
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 |
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
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 .
Sub-search:
| table ID, MgrName
Hint: Depending on event size: drop this part of the search or replace with
| fields ID, MgrName
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.