Use a lookup file to find FromIP addresses of log entries

[Deb]

Hi there, I have a csv lookup file consisting of sender email addresses.  I'd like to search the splunk logs for all the entries with these SenderAddresses over the last 90 days to determine what FromIP they have.  What search syntax do I use?

file has been uploaded to Splunk and is called AllSenders.csv.  it has heading email, flag...all the flag are set to 1 since I want to search them all.  

In general, to search the logs for email i use:  

index=app_messagetrace sourcetype=ms:o365:reporting:messagetrace

Thanks in advance....let me know what other info you need to help  🙂

[Deb]

sorry, i'm not understanding.  when I search using index=app_messagetrace sourcetype=ms:o365:reporting:messagetrace [ | inputlookup AllSenders.csv | return 1000 email ] earliest = -90d.....I get 0 results.  I think it's because it doesn't realize what "email" is in the actual log....so I want to connect it to SenderAddress.....I see you have written "The subsearch runs first, reads in the lookup file then formats the first 1000 results (you can change that number) into (email=foo OR email=bar OR ..." but I'm not sure how to change the query for my purposes....sorry I'm new at this....been reading and watching tutorials but finding it a bit confusing.

[richgalloway · Accepted solution]

If the field name in the lookup doesn't match that in the index then the fix is easy - just add a rename command to the subsearch (there's also a way to do it with the return command).

index=app_messagetrace sourcetype=ms:o365:reporting:messagetrace [ | inputlookup AllSenders.csv | rename email as SenderAddress | return 1000 SenderAddress ] earliest = -90d

[Deb]

Thanks Rich, appreciate your help!!

[Deb]

thanks for the quick reply Rich.  I realize now the heading in the csv is "email" but in the log data, i want it to search by SenderAddress...how do I indicate this in the query?

[richgalloway]

Incorporate the lookup into your search by using a subsearch.

index=app_messagetrace sourcetype=ms:o365:reporting:messagetrace [ | inputlookup AllSenders.csv | return 1000 email ] earliest = -90d

The subsearch runs first, reads in the lookup file then formats the first 1000 results (you can change that number) into (email=foo OR email=bar OR ...).

[Deb]

I think I found it at this posting

https://community.splunk.com/t5/Splunk-Search/inputlookup-compare-the-field-values-in-my-logs-with-l...

index=app_messagetrace sourcetype=ms:o365:reporting:messagetrace [ | inputlookup AllSenders.csv | table email | rename email as SenderAddress ] earliest = -90d

this seems to give me the events i'm looking for....now I just need to organize the events by IP Address