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
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.
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.
Good point, although it should be fields rather than table. I've corrected my reply.
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.
Welp, it's always something. I just noticed EnrollmentEmailAddresss has too many s's in it. *facepalm*
Thanks for the help!
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.