topic Re: Compare CSV to Search [IN CSV NOT IN SEARCH] in Getting Data In

Compare CSV to Search [IN CSV NOT IN SEARCH]

Workmanaquariou — Wed, 19 Jun 2019 13:37:15 GMT

Good Day!
I am trying to figure out a way to compare a csv file to a search and return only what is in the CSV file that was not in the search. Im taking an email list and doing a search for all emails received my end goal is to figure out emails in the csv that haven't received an email in x days.

I have tried:

index=proofpoint 
    sourcetype=pps_messagelog 
| rex field=envelope.rcpts{} "(\@(?<PP_To>[a-zA-Z0-9]+.+))" 
| search envelope.rcpts{} = * 
| rename envelope.rcpts{} as "PP_Rcpts" 
| append 
    [ inputlookup DL_List.csv 
    | fields PP_Rcpts, "Display Name" ] 
| table PP_To,PP_Rcpts, "Display Name" 
| stats count by PP_Rcpts 
| search count=1 | lookup DL_List.csv PP_Rcpts  as PP_Rcpts output PP_Rcpts,"Display Name" | search PP_Rcpts!=""

I.E

CSV

Search Return

c
d
e
f
h
i
j

Wanted Results ( after CSV lookup)

a
b
g

Re: Compare CSV to Search [IN CSV NOT IN SEARCH]

Workmanaquariou — Wed, 30 Sep 2020 01:01:29 GMT

I also tried
| inputlookup DL_List.csv
| fields PP_Rcpts
| search NOT
[ search index=proofpoint
sourcetype=pps_messagelog
| rex field=envelope.rcpts{} "(\@(?[a-zA-Z0-9]+.+))"
| rename envelope.rcpts{} as "PP_Rcpts"
| stats count by PP_Rcpts
]

Re: Compare CSV to Search [IN CSV NOT IN SEARCH]

somesoni2 — Fri, 21 Jun 2019 14:55:06 GMT

Give this a try

index=proofpoint sourcetype=pps_messagelog 
 | search envelope.rcpts{} = * 
 | rex field=envelope.rcpts{} "(\@(?<PP_To>[a-zA-Z0-9]+.+))" 
 | rename envelope.rcpts{} as PP_Rcpts
 | fields PP_Rcpts PP_To
 | eval from="Data"
 | append 
     [ inputlookup DL_List.csv 
     | fields PP_Rcpts, "Display Name" | eval from="CSV"] 
 | stats values(from) as from values("Display name") as "DisplayName" by PP_Rcpts 
 | where mvcount(from)=1 AND from="CSV"