Splunk Search

How to filter events using lookup table?

bvan
Explorer

I've tried to follow others posts as well as the documentation here and I've come up empty. I have a bunch of device enrollment events in my index and I want to filter out only those events that are happening by users in our Pilot group listed in a lookup table.

index data looks like this:

 

DeviceFriendlyName:  DeviceMobile-Serial1234
DeviceId:  132483 
EnrollmentEmailAddress: user@company.com
EnrollmentStatus:  Enrolled 
EnrollmentUserId:  123 
EnrollmentUserName:  mobileUsername
EventId:  148 
EventTime:  2020-07-13T22:54:04.4612316Z 
EventType:  MDM Enrollment Complete 

 

My lookup table is simply a list of:

Full Name E-mail Address

 

I want to just see events where the EnrollmentEmailAddress matches an email listed in the "E-Mail Address" of the lookup table.

 

index=myindex source=mysource sourcetype=mysource_type EventId="148" | search [| inputlookup pilot_users.csv | rename "E-Mail Address" as EnrollmentEmailAddresss ] | table EnrollmentEmailAddress, EventId

 

Labels (2)
0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

You didn't say so, but I'll assume that query didn't work for you.  What did you get compared to what you wanted?  Have you tried something like this?

 

index=myindex source=mysource sourcetype=mysource_type EventId="148" [ | inputlookup pilot_users.csv | rename "E-Mail Address" as EnrollmentEmailAddresss | fields EnrollmentEmailAddresss | format ] 
| table EnrollmentEmailAddress, EventId

 

It's a pretty subtle change.  The inputlookup subsearch moves to before the first pipe so the list of email addresses can be filtered right away. 

---
If this reply helps you, an upvote would be appreciated.

View solution in original post

0 Karma

bowesmana
Super Champion

Unless you want both Full Name and E-mail address fields from the lookup to be used in the parent search, you need to add

| table EnrollmentEmailAddress

after the rename statement, before the end of the subsearch, as the outer search will also be using Full Name as a field search.

 

0 Karma

richgalloway
SplunkTrust
SplunkTrust

Good point, although it should be fields rather than table.  I've corrected my reply.

---
If this reply helps you, an upvote would be appreciated.

richgalloway
SplunkTrust
SplunkTrust

You didn't say so, but I'll assume that query didn't work for you.  What did you get compared to what you wanted?  Have you tried something like this?

 

index=myindex source=mysource sourcetype=mysource_type EventId="148" [ | inputlookup pilot_users.csv | rename "E-Mail Address" as EnrollmentEmailAddresss | fields EnrollmentEmailAddresss | format ] 
| table EnrollmentEmailAddress, EventId

 

It's a pretty subtle change.  The inputlookup subsearch moves to before the first pipe so the list of email addresses can be filtered right away. 

---
If this reply helps you, an upvote would be appreciated.

View solution in original post

0 Karma

bvan
Explorer

Welp, it's always something. I just noticed EnrollmentEmailAddresss has too many s's in it. *facepalm*

Thanks for the help!

0 Karma

bvan
Explorer

No luck. The job inspector says that it was able to find the lookup table successfully. I've verified the "E-Mail Address" column on that lookup table and I've run JUST a search filtering for EnrollmentEmailAdress=someemailinthattable@company.com and I got results. So the data is there, the query just isn't working.

I'm not sure what the next steps are. I might try to edit the lookup table and just rename the e-mail column so I don't have to do any re-naming inline in the search and hope that helps. Right now I'm getting no returned results using the helpful suggestions here.

0 Karma
.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!