How to filter events using lookup table?

[bvan]

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: [email protected]
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

[bowesmana]

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.

[richgalloway]

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

[richgalloway · Accepted solution]

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. 

[bvan]

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

Thanks for the help!

[bvan]

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 [email protected] 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.