Lookups in Splunk App for Fraud Analytics

| from datamodel:fraud_ui
| search data_source IN (UI_SYS_ACCESS, CERTS)
| sort 0 + _time
| streamstats time_window=1h dc(IP_Country) as countries, dc(data_source) as sources by SSN
| where countries>1 AND sources>1
| dedup SSN
| lookup email_norm_info email_norm
| eval risk="Location Deception"
| table SSN risk email_norm emails_num
| outputlookup risky_ssns.csv

| inputlookup fraud_ui_claimant
| lookup fraud_ui_email SSN
| eval email_norm = coalesce(lower(replace(mvindex(split(EMAIL_ADDRESS, "@"),0), "(\.|\+[^@]*)", "") . "@" . mvindex(split(EMAIL_ADDRESS, "@"),1)), "---")
| where email_norm!="---"
| eval EMAIL_ADDRESS_lower=lower(EMAIL_ADDRESS)
| eventstats dc(EMAIL_ADDRESS_lower) as emails_num BY email_norm
| where emails_num>1
| eval risk="Impersonation"
| table SSN risk email_norm emails_num
| inputlookup append=true risky_ssns.csv
| outputlookup risky_ssns.csv

index=main source IN (DEPOS*)
| fillnull value="---"
| dedup SSN DD_ACCT_NUM DD_ACCT_ABA_NO
| eventstats dc(SSN) as acct_shared_with BY DD_ACCT_NUM
| where acct_shared_with>3
| lookup risky_ssns2.csv SSN
| stats max(acct_shared_with) as acct_shared_with, dc(DD_ACCT_NUM) as accounts_num, first(email*) as email* by SSN
| eval email_norm = coalesce(email_norm, lower(replace(mvindex(split(EMAIL_ADDRESS, "@"),0), "(\.|\+[^@]*)", "") . "@" . mvindex(split(EMAIL_ADDRESS, "@"),1)))
| eval risk="Shared Bank Account"
| inputlookup append=true risky_ssns.csv
| eventstats first(email*) as email*, first(acc*) as acc* by SSN
| table SSN risk acc* EM* em*
| outputlookup risky_ssns.csv