alternative to join that works with wildcards in lookups

[thx]

I  am trying to use a lookup of "known good" filenames that are within FTP transfer logs, to add extra data to files that are found in the logs, but also need to show  when files are not found in the logs, but expected.

The lookup has a lookup definition defined, so that FileName can contain wildcards, and this works for matching the wildcarded filename to  existing events, with other SPL.

lookup definition with wildcard on FileName for csv:
FTP-Out

FileName	Type	Direction	weekday
File1.txt	fixedfilename	Out	monday
File73*.txt	variablefilename	Out	thursday
File95*.txt	variablefilename	Out	friday

example events:

8/30/24 9:30:14.000AM FTPFileName=File1.txt Status=Success Size=14kb
8/30/24 9:35:26.000AM FTPFileName=File73AABBCC.txt Status=Success Size=15kb
8/30/24 9:40:11.000AM FTPFileName=File73XXYYZZ.txt Status=Success Size=23kb
8/30/24 9:45:24.000AM FTPFileName=garbage.txt Status=Success Size=1kb

current search (simplified):

| inputlookup FTP-Out
| join type=left FileName
[ search index=ftp_logs sourcetype=log:ftp
| rename FTPFileName as FileName]

results I get:

8/30/24 9:30:14.000AM File1.txt fixedfilename Out monday Success 14kb

File73*.txt variablefilename Out thursday

File95*.txt variablefilename Out friday

desired output:

8/30/24 9:30:14.000AM File1.txt fixedfilename Out monday Success 14kb

8/30/24 9:35:26.000AM File73AABBCC.txt variablefilename Out thursday Success 15kb

8/30/24 9:40:11.000AM File73XXYYZZ.txt variablefilename Out thursday Success 23kb

File95*.txt variablefilename Out friday

Essentially I want the full filename and results for anything the wildcard in the lookup matches, but also show any time the wildcard filename in the lookup doesn't match an event in the  search window.

I've tried various other queries with append/appendcols and transaction and the closest I've gotten  so far is still with the left join, however that doesn't appear to join with wildcarded  fields from a lookup. It also doesn't seem that the where  clause with a join off a lookup  supports like()

I'm hoping that someone else might have an idea on how I can get the  matched files as well as missing files in  an  output similar to my desired output above.

This is within a splunkcloud deployment not  enterprise.

[ITWhisperer · Accepted solution]

Try something like this (assuming your fields have been extracted already)

| lookup FTP-Out FileName as FTPFileName OUTPUTNEW FileName Type Direction weekday
| inputlookup FTP-Out append=t
| eventstats count(FTPFileName) as files by FileName
| where files=0 OR isnotnull(FTPFileName) AND isnotnull(FileName)
| fields - files

[thx]

Thanks, that looks like its doing exactly what I was looking to replicate with my join from my older SPL that had fixed values in  the lookup file.