Splunk Search

How to inputlookup match against search field?

7ryota
Explorer

Hi all,

I have a inputlookup file named as leavers.csv which ill be automatically update

this file contain the userID

 

I will need to use the userID and retrieve the user email from index=zscaler

from there i will need to search in the index=exomsgtrace to determine if there is any outbound email from the users listed in the leavers.csv

 

Can i get your help to construct all the requirement into a single query.

Labels (1)
0 Karma

yuanliu
SplunkTrust
SplunkTrust

For others to construct a search, they will need to know which field in index zscaler can be matched with userID - I'll assume the same field name; which field in index zscaler represents user's E-mail - I'll assume the name userEmail, they will also need to know which field in index exomsgtrace represents outbound E-mail - I'll assume something like outgoingEmail, as well as further assume that's a multivalue; as well as how you want the result presented; in addition, what other characteristics are in zscaler and exomsgtrace because different characteristics can require very different search approaches. (These details should be described in the question and not leaving it to volunteers to speculate.)  For simplicity, I'll assume that there's no overlapping fields between the two indices, and userID, outgoingEmail are already extracted, and that you wanted a list of timestamps (_time), senders (sender), and subject (subject) in exomsgtrace when outgointEmail contains E-mail address of a matching userID, all of these fields already available in the index.

Using these assumptions, what I assumed you wanted can be done with something like

index=exomsgtrace OR (index=zscaler 
    [| inputlookup leavers.csv
    | fields userID] ``` assume userID also exists in zscaler ```)
| mvexpand outgoingEmail ``` outgoing Email could be multivalued ```
| eval userEmail = coalesce(userEmail, outgoingEmail) ``` use the same field name ```
| eval _time = if(index == zscaler, null(), _time)
| stats values(_time) as _time values(sender) as sender values(subject) as subject by userEmail

 

Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...