topic How to filter events using lookup table? in Splunk Search

How to filter events using lookup table?

bvan — Tue, 14 Jul 2020 19:55:35 GMT

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

Re: Using lookup table to filter events

richgalloway — Tue, 14 Jul 2020 12:41:30 GMT

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?

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.

Re: Using lookup table to filter events

bowesmana — Tue, 14 Jul 2020 00:42:06 GMT

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.

Re: Using lookup table to filter events

richgalloway — Tue, 14 Jul 2020 12:40:56 GMT

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

Re: Using lookup table to filter events

bvan — Tue, 14 Jul 2020 19:38:07 GMT

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.

Re: Using lookup table to filter events

bvan — Tue, 14 Jul 2020 19:57:57 GMT

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

Thanks for the help!