Splunk Search

How to have the lookup multivalue field return the 1st match?

brdr
Contributor

hello,

I have a lookup table that has host to user correlation... ex;

Host, User
hosta, user1
hostb, user2
hostb, user3

My understanding is that when I match on hostb via lookup below I will get field Authentication.user back as a multivalue field. But when I try to use 'mv' commands to get first user for hostb (user2) I get blank field for top_user. Why? thx.

| makeresults | eval host="hostb"
    | table host
    | lookup host-to-users.csv Authentication.src as host OUTPUT Authentication.user
    | eval top_user=mvindex(Authentication.user,0)
    | table host Authentication.user top_user
0 Karma
1 Solution

woodcock
Esteemed Legend

You have a mismatch in field names and wrong ordering to boot, with a dash of possible misinterpretation of . as concatenation operator. Try this:

| makeresults | eval host="hostb"
| table host
| lookup host-to-users.csv host AS Authentication.src OUTPUT user AS Authentication.user
| eval top_user=mvindex('Authentication.user', 0)
| table host Authentication.user top_user

View solution in original post

woodcock
Esteemed Legend

You have a mismatch in field names and wrong ordering to boot, with a dash of possible misinterpretation of . as concatenation operator. Try this:

| makeresults | eval host="hostb"
| table host
| lookup host-to-users.csv host AS Authentication.src OUTPUT user AS Authentication.user
| eval top_user=mvindex('Authentication.user', 0)
| table host Authentication.user top_user

brdr
Contributor

thx for response as always... I did have it backwards. the headers in the lookup file should have been Authentication.src, Authentication.user..

The thing that was catching me up is the single quotes around the first parameter in the mvindex() function. As soon as I added the quotes the commands I entered worked.

Thx again.

0 Karma

woodcock
Esteemed Legend

I hate non-alphanumeric characters in field names, especially spaces and periods.

0 Karma

somesoni2
Revered Legend

If your lookup fields are host and user (that exact name), you're using wrong order in your lookup invocation. Do you get any value in Authentication.user right now?

0 Karma

brdr
Contributor

Hey somesoni2, yes I get the appropriate number of user(s) that match on host. Just not sure why I can't operate on the users mv field to get the first (value) user.

0 Karma
Get Updates on the Splunk Community!

Security Highlights: September 2022 Newsletter

 September 2022 The Splunk App for Fraud Analytics (SFA) is now Splunk SupportedUse your existing Splunk ...

Platform Highlights | September 2022 Newsletter

 September 2022 What’s New in 9.0 and How to UpgradeGet a walk through of what is new Splunk Enterprise 9.0 ...

Observability Highlights | September 2022 Newsletter

 September 2022 Splunk Observability SuiteAccess to "Classic" SignalFx Interface Will be Removed on Sept 30, ...