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, Karma would be appreciated.

View solution in original post

0 Karma

bowesmana
SplunkTrust
SplunkTrust

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, Karma 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, Karma would be appreciated.
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
Get Updates on the Splunk Community!

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...