topic Re: How do you include additional fields from an inputlookup in results? in Splunk Search

How do you include additional fields from an inputlookup in results?

jwalzerpitt — Tue, 16 Oct 2018 12:44:51 GMT

I have the following search in which I match up the user field from the lookup to the index, getting the top return of only the admin accounts:

index=foo [| inputlookup admin_accts |  fields user ] 
| stats count by user 
| sort -count

The lookup admin_accts also has three other fields - "Last Name", "First Name", and "Role".

How do I modify the search so that those three additional fields are listed in the results?

Thx

Re: How do you include additional fields from an inputlookup in results?

renjith_nair — Tue, 16 Oct 2018 13:43:28 GMT

@jwalzerpitt,

Try ,

index=foo|stats count by user 
| lookup admin_accts user OUTPUT "Last Name", "First Name", "Role"
|where Role!=""

This should avoid your sub-search with the inputlookup. Compare the results and also the performane

Re: How do you include additional fields from an inputlookup in results?

DalJeanis — Tue, 16 Oct 2018 13:59:27 GMT

Or...

   index=foo [| inputlookup admin_accts |  fields user ] 
 | stats count by user 
 | lookup admin_accts user OUTPUT "Last Name", "First Name", "Role"
 | sort -count

Re: How do you include additional fields from an inputlookup in results?

jwalzerpitt — Tue, 16 Oct 2018 14:03:45 GMT

Thx for the reply

If I try the search above, it doesn't match on the users in the lookup file. For example, using my original search, six users are returned. Using your suggested search, I get no results at all.

Thx

Re: How do you include additional fields from an inputlookup in results?

jwalzerpitt — Tue, 16 Oct 2018 14:07:16 GMT

Dal,

Using your suggested search, I see the six users, but the other fields returned are blank. For the heck of it, I removed the space between the two fields first name and last name (now they're Lastname and FirstName) to see if that made a difference, but still, the same result with the users returned, but the three other fields blank.

Thx

Re: How do you include additional fields from an inputlookup in results?

jwalzerpitt — Tue, 16 Oct 2018 14:11:21 GMT

Also, I did an| inputlookup admin_accts and the table is returned with the columns FirstName LastName, Role, and user to verify the lookup table

Re: How do you include additional fields from an inputlookup in results?

renjith_nair — Tue, 16 Oct 2018 14:16:05 GMT

are you getting result for index=foo|stats count by user ? If yes, then are the field name in search and lookup same - user ?

Re: How do you include additional fields from an inputlookup in results?

jwalzerpitt — Tue, 16 Oct 2018 14:18:58 GMT

I am - the field for the index is "user" and the field in the lookup is "user"

With my search, the results returned filters on only the users in the lookup

Re: How do you include additional fields from an inputlookup in results?

renjith_nair — Wed, 17 Oct 2018 03:13:02 GMT

Since the lookup is not working in both suggestions , we shall re-look at the lookup definitions. Just to test, can you try this

|makeresults|eval user="give here one of your admin username"|lookup admin_accts user OUTPUT "Last Name", "First Name", "Role"

If we are not getting the result for this, then we might have a problem in lookup definition

Re: How do you include additional fields from an inputlookup in results?

jwalzerpitt — Wed, 17 Oct 2018 12:38:58 GMT

A user is returned, but no values for the LastName, FirstName, and Role

Thx

Re: How do you include additional fields from an inputlookup in results?

renjith_nair — Wed, 17 Oct 2018 14:32:43 GMT

That tells us that there might be a problem in lookup because we just did a simple lookup. Could there be a space in user value in lookup or any other special character? Try creating a simple csv file with one or two records from the original lookup file and upload it as .csv and repeat the above sample search against that file.csv. Once you are able to get that lookup fixed, the original search provided should work.

Re: How do you include additional fields from an inputlookup in results?

DalJeanis — Fri, 19 Oct 2018 14:51:33 GMT

@jwalzerpitt - the output fields should be spelled and capitalized exactly as they are on the lookup file.

 | lookup admin_accts user OUTPUT LastName FirstName Role

Re: How do you include additional fields from an inputlookup in results?

jwalzerpitt — Fri, 19 Oct 2018 14:55:03 GMT

Figured out the issue in that the username was in call caps so whenever I would try to call another field like LastName, FirstName, or Role no results were returned, but calling just the user field would return the user.

Thx