topic Re: How to develop a lookup search to run a stats count by user and return other fields in the output? in Splunk Search

How to develop a lookup search to run a stats count by user and return other fields in the output?

jwalzerpitt — Mon, 14 Nov 2016 17:55:07 GMT

I have a lookup table that has five fields:

User    
Account Type    
Employee RC 
Employee Department 
Student RC

I have an index I'd like to run the lookup command against that has a field with the same name - User

How do I set up my lookup command to do a stats count by User and return the other four fields in the output?

Thx

Re: How to develop a lookup search to run a stats count by user and return other fields in the output?

richgalloway — Mon, 14 Nov 2016 18:10:06 GMT

Perhaps this will help.

index=foo | stats count as Count by User | lookup mylookup User OUTPUT "Account Type" "Employee RC" "Employee Department" "Student RC" | ...

Re: How to develop a lookup search to run a stats count by user and return other fields in the output?

niketn — Mon, 14 Nov 2016 18:34:33 GMT

Assuming your lookup table has lookup defined as userdetails

Your base search | stats count by User | lookup userdetails User | table User, "Account Type", "Employee RC", "Employee Department", "Student RC"

It would be better if your lookup table had fields without spaces like Employee_RC etc

Re: How to develop a lookup search to run a stats count by user and return other fields in the output?

jwalzerpitt — Mon, 14 Nov 2016 18:35:01 GMT

Thx for the repkly Rich.

I ran that query and I'm seeing the count by User, but the other fields ("Account Type" "Employee RC" "Employee Department" "Student RC") are blank

Re: How to develop a lookup search to run a stats count by user and return other fields in the output?

jwalzerpitt — Mon, 14 Nov 2016 18:36:36 GMT

FYI - as a test, I did a |inputlookup ldap and I'm seeing values in the fields

Re: How to develop a lookup search to run a stats count by user and return other fields in the output?

jwalzerpitt — Mon, 14 Nov 2016 18:41:33 GMT

Thx - I added the underline to the other fields, but still not seeing any values returned

Re: How to develop a lookup search to run a stats count by user and return other fields in the output?

richgalloway — Mon, 14 Nov 2016 19:02:40 GMT

So is your problem resolved?

Re: How to develop a lookup search to run a stats count by user and return other fields in the output?

niketn — Mon, 14 Nov 2016 19:05:36 GMT

Can you share sample from lookup file including the header and also from the log data couple or _raw events?

Re: How to develop a lookup search to run a stats count by user and return other fields in the output?

niketn — Mon, 14 Nov 2016 19:06:47 GMT

Make sure the casing of field User is the same in raw data and the lookup table. If the fields are not exact match they will not join.

Re: How to develop a lookup search to run a stats count by user and return other fields in the output?

jwalzerpitt — Mon, 14 Nov 2016 19:18:12 GMT

The field in the index is 'User' and the field in the .csv is 'User' - I did double check that to make sure they matched up

Re: How to develop a lookup search to run a stats count by user and return other fields in the output?

jwalzerpitt — Mon, 14 Nov 2016 19:19:10 GMT

No - still not seeing values in any of other four fields

Re: How to develop a lookup search to run a stats count by user and return other fields in the output?

jwalzerpitt — Tue, 29 Sep 2020 11:45:58 GMT

Some sample data from the csv:

User Account_Type Employee_RC Employee_Department Student_RC
user1 Primary University Library System (60) Administrative Services (60080)
user2 Primary Sch Arts and Sciences (06) Chemistry (13203)

user3 Primary Swansea School of Engineering (23)
user4 Primary General Counsel (54) General Counsel (01020)

user5 Primary Univ of ABC at City Name (42) Soc Sci-Admin of Just (42249)
user6 Primary Financial Aid (84) General University Budget Only (07118) Howler Sch Arts and Sciences (06)
user7 Primary Howler Sch Arts and Sciences (06)
user8 Primary Howler Sch Arts and Sciences (06)

Re: How to develop a lookup search to run a stats count by user and return other fields in the output?

jwalzerpitt — Tue, 15 Nov 2016 14:16:47 GMT

FYI - some keys have blank values

Re: How to develop a lookup search to run a stats count by user and return other fields in the output?

somesoni2 — Tue, 15 Nov 2016 15:35:12 GMT

Is the case of values of field User different in index/lookup? Lookup is case-sensitive (by default) and will not work if you try to match user1 with User1 OR USER1.

Re: How to develop a lookup search to run a stats count by user and return other fields in the output?

jwalzerpitt — Tue, 15 Nov 2016 16:28:37 GMT

Thx for the info - in my .csv user names are all in caps, whereas the user field in the index the user names are lower case. I modified the .csv and changed upper case to lower case, reloaded the .csv and the lookup works - thx!