Splunk Search

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

jwalzerpitt
Influencer

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

0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

Perhaps this will help.

index=foo | stats count as Count by User | lookup mylookup User OUTPUT "Account Type" "Employee RC" "Employee Department" "Student RC" | ...
---
If this reply helps you, Karma would be appreciated.

View solution in original post

niketn
Legend

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

jwalzerpitt
Influencer

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

0 Karma

niketn
Legend

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

jwalzerpitt
Influencer

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)

0 Karma

jwalzerpitt
Influencer

FYI - some keys have blank values

0 Karma

niketn
Legend

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.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

jwalzerpitt
Influencer

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

0 Karma

richgalloway
SplunkTrust
SplunkTrust

Perhaps this will help.

index=foo | stats count as Count by User | lookup mylookup User OUTPUT "Account Type" "Employee RC" "Employee Department" "Student RC" | ...
---
If this reply helps you, Karma would be appreciated.

jwalzerpitt
Influencer

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

0 Karma

richgalloway
SplunkTrust
SplunkTrust

So is your problem resolved?

---
If this reply helps you, Karma would be appreciated.
0 Karma

jwalzerpitt
Influencer

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

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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.

jwalzerpitt
Influencer

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!

0 Karma

jwalzerpitt
Influencer

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

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...