Splunk Search

Return value from separate fields

kengilmour
Path Finder

Hello,

I'm looking for a solution to get data from two CSV files that will be used for a one-off search.

I have the following data:

CSV 1

  1. displayName=Full User's Name (e.g. "John Doe")
  2. ManagerRACF= The Manager's User ID (e.g. Jdoe002)
  3. sAMAccountName=User's User ID (e.g. Jdoe001)

CSV 2

  1. First_Name
  2. Last_Name
  3. Token_Nr

What I want to do is get combine CSV2 with CSV1 (easy) and then find the person's manager's full name with nothing more than the ManagerRACF field (unknown).

For the first part:

source=CSV1.csv
|join displayName[search index="main" source="CSV2.csv" | eval displayName=First_Name." ".Last_Name |dedup displayName| fields + displayName]
| table displayName, ManagerRACF, ManagerName, Token_Nr

This will join the two files and show the user's full name, the Manager's UserID and the user's Token number.

Now how do i get ManagerName to translate into the full name of the person's manager from CSV 1 based on the data in the ManagerRACF column? Basically what this means is, I need to find data in ManagerRACF (Jdoe002) search the sAMAccountName column for that value, and then return the results from displayName on that row into the ManagerName column.

Thanks!

Ken

lguinn2
Legend

I see that you indexed this data into Splunk, but honestly this doesn't seem like the best way to approach the problem. I think that CSV 1 (can we call it "AccountInfo") should be a lookup table, not indexed into Splunk.

If you load the CSV file into Splunk and create a lookup called AccountLookup (lookup tutorial here and manual here), then you can do this

source=csv2
| eval displayName = First_Name + " " + Last_Name
| lookup AccountLookup displayName OUTPUT ManagerRACF as inRACF
| lookup AccountLookup sAMAccountName as inRACF OUTPUT displayName as ManagerName

That should do it!

lguinn2
Legend

I guess all data is "static" - it's just a matter of the time frame! And illustrates the importance of actually understanding the data before making these decisions...

0 Karma

kristian_kolb
Ultra Champion

Aah, you might be right, I actually read "token" as "userID"

0 Karma

lguinn2
Legend

I agree, but I thought maybe the token assigned to the name changed regularly, and I certainily didn't want to get into time-based lookups as a starting point!

0 Karma

kristian_kolb
Ultra Champion

Yep, along my line of thinking as well. Actually both of the CSV's are pretty good candidates for lookup tables, and in my opinion CSV2 is preferable, as you tend to change managers more often than you change your name 🙂

/K

0 Karma

kengilmour
Path Finder

Imagine there is a table with two rows:

Headers

displayName, sAMAccountName, ManagerRACF, Token_Nr

Row 1
John Doe, JDoe001, JDoe002, 000000001

Row 2
Jane Doe, JDoe002, JDoe003, 000000002

Splunk should return the results as follows:

displayName, ManagerRACF, ManagerName, Token_Nr
John Doe, JDoe002, Jane Doe, 000000001

The displayName value from row 2 populates "ManagerName" value field in splunk, based on the data in the "ManagerRACF" value field from row 1.

Thanks!

Ken

0 Karma

kml_uvce
Builder

can you explain more ...

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 ...