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
Perhaps this will help.
index=foo | stats count as Count by User | lookup mylookup User OUTPUT "Account Type" "Employee RC" "Employee Department" "Student RC" | ...
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
Thx - I added the underline to the other fields, but still not seeing any values returned
Can you share sample from lookup file including the header and also from the log data couple or _raw events?
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)
FYI - some keys have blank values
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.
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
Perhaps this will help.
index=foo | stats count as Count by User | lookup mylookup User OUTPUT "Account Type" "Employee RC" "Employee Department" "Student RC" | ...
FYI - as a test, I did a |inputlookup ldap and I'm seeing values in the fields
So is your problem resolved?
No - still not seeing values in any of other four fields
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
.
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!
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