Splunk alert on file not found in 1 index when compared between 2 indexes

[joshimeister]

Hello,

So i am trying to create an alert based on logs from 2 different indexes. Basically what im trying to alert on is if a zip file/zip files from 1 index makes it to a 2nd different index, if it does not, i want it to alert.

I have the following splunk query that combines both indexes but it's not completely accurate because when i run the indexes separately, im getting the zip files in question to appear in both indexes when in reality, i was expecting the zip files to appear in index 1 and not in index 2.

Splunk query combining both indexes

index=index_1 OR index=index_2 sourcetype="index_1_logs" OR sourcetype="index_2_logs" "ftp.com" OR "External command has been executed" "*.zip" 
| eval results = if(match(index_1_zipfile_field,index_2_zipfile_field), "file made it through", "file did not make it through") 
| table results index_1_zipfile_field index_2_zipfile_field 
| search index_1_zipfile_field=* 
| dedup index_1_zipfile_field

Results show as shown below showing no results under index_2_zipfile_field giving the illusion that the zip files never made it through to index 2:

results	index_1_zipfile_field	index_2_zipfile_field
file did not make it through	fgfbf-fgfgfg-wewsd-dfsf.zip
file did not make it through	ghghh-rtrtr-trtrt-weqe.zip

...but when i check index 2 and look up the results from the table above, i see the zip file made it through so i am unsure what im doing wrong here

index=index_2 sourcetype=index_2_logs "ftp.com" "*fgfbf-fgfgfg-wewsd-dfsf.zip*"
| table index_2_zipfield_field
| dedup index_2_zipfield_field 

results:

index_2_zipfield_field

fgfbf-fgfgfg-wewsd-dfsf.zip

ghghh-rtrtr-trtrt-weqe.zip

Hopefully i made sense. 

[ITWhisperer]

| stats count by zipfile_field
| where count = 1

[joshimeister]

Hi @ITWhisperer thanks for the response but where exactly would that fit in the bigger query that I posted? 

I have also tried the following but it's still not accurate

replaced the original long query:

<base query>
| eval results = if(match(index_1_zipfile_field,index_2_zipfile_field), "file made it through", "file did not make it through") 
| table results index_1_zipfile_field index_2_zipfile_field 
| search index_1_zipfile_field=* 
| dedup index_1_zipfile_field

...with this one:

<base query> 
| where isnull(index_2_zipfile_field)
| table index_1_zipfile_field index_2_zipfile_field
| dedup index_1_zipfile_field

...but still not there and not accurate

[ITWhisperer]

index=index_1 OR index=index_2 sourcetype="index_1_logs" OR sourcetype="index_2_logs" "ftp.com" OR "External command has been executed" "*.zip" 
| stats count by zipfile_field
| where count = 1

I am assuming (since you didn't share any sample events) that events from both indexes have a field called zipfile_field.

Splunk searches work on a pipeline of events, each command in the pipeline processes the events and passes the results onto the next command in the chain. An event from index_1 will not have fields from index_2 unless you are doing something to combine them, which you don't appear to be doing. This is why you aren't getting a match between index_1_zipfile_field and index_2_zipfile_field. In each event, one of these fields will have a value and the other will be null.

If a zipfile value only appears in one index, then counting by the zipfile_field across both indexes (which is what the stats command is doing) will find instances of values in the zipfile_field which only appear in one index (which is what the where command is doing).