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

[jwalzerpitt]

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

[renjith_nair]

@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

[jwalzerpitt]

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

[renjith_nair]

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

[jwalzerpitt]

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

[renjith_nair]

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

[jwalzerpitt]

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

Thx

[renjith_nair]

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.

[DalJeanis]

Or...

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

[jwalzerpitt]

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

[DalJeanis]

@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

[jwalzerpitt]

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

[jwalzerpitt]

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