How to exclude files from results using a lookup table with wildcard support?

[Splunked_Kid]

Hi Splunk Community,

I'm working on a search that analyzes an index containing records of file activity. Each event includes a field called FICHIER, which holds the name of the file.

I have two lookup tables:

1. ApplicationResponsibles.csv
   This contains mappings between application codes and their respective technical owners and email addresses.
   Columns: CodeApplication, ResponsableTI, Courriel

2. FilesToExclude.csv
   This contains a list of filenames or filename patterns (with wildcards like *J69*) that should be excluded from the results.
   Column: Motif

---

🧩 Goal

I want to produce a report that lists:

• Each application owner (ResponsableTI)
• Their email (Courriel)
• A list of application codes and the count of files associated with each code
  excluding any file that matches a pattern in the FilesToExclude.csv lookup.

---

🧪 What I’ve tried

I attempted to use a subsearch with format and like() to dynamically build a where NOT clause, but I ran into multiple issues:

• format doesn’t seem to work well with like() expressions.
• lookup requires a key field, but I’m trying to use the lookup as a filter list.
• mvmap() and mvfilter() seem promising, but I’m struggling to apply them correctly to compare each event’s FICHIER against all motifs in the exclusion list.

---

🧠 What I need help with

• What’s the best way to dynamically exclude files based on a list of patterns (wildcards) stored in a lookup table?
• Is there a clean way to apply like(FICHIER, motif) across all motifs in the lookup?
• Should I restructure the lookup or use a different approach entirely?

Any guidance or examples would be greatly appreciated!

Thanks in advance 🙏

[Splunked_Kid]

Hi @ITWhisperer 


Tanks for the answer, but there's other column in my lookup and it's trying to match the other colunm not needed.  For example my lookup contains : FICHIER, Approbateur, Raison, DateApprobation.

If I look at the job inspection in the remotesearch it goes something like this:
(index=my index (NOT Approbateur="John Doe" OR NOT DateApprobation="2025-09-11" OR NOT FICHIER="DEVDLP.FEXTORG.O04750.PC.REC.J04525" OR NOT Raison="Non- pas a détruire : Le Contenue du fichier est remplacé par ce qui arrive du commerce électronique.") (NOT Approbateur="Jane Doe" OR NOT DateApprobation="2025-09-11" OR NOT FICHIER="DVLPET.FEXTORG.O04746.PC.REC.J04525" OR NOT Raison="Non- pas a détruire : Le Contenue du fichier est remplacé par ce qui arrive du commerce électronique.") (NOT Approbateur="Jim Doe" OR NOT DateApprobation="2025-09-11" etc...

I would need to see something like this instead:

(index=my index (NOT FICHIER="DEVDLP.FEXTORG.O04750.PC.REC.J04525" OR NOT FICHIER="DVLPET.FEXTORG.O04746.PC.REC.J04525"   etc...

Is there a way to do that?

[ITWhisperer · Accepted solution]

index ... NOT [| inputlookup FilesToExclude.csv 
    | rename Motif as FICHIER
    | fields - Approbateur DateApprobation Raison
    | format]
| lookup ApplicationResponsibles.csv CodeApplication as code

[Splunked_Kid]

Super!  Thanks a lot!

[Splunked_Kid]

@ITWhisperer 

This is working fine but I would like to replace the bunch of AND NOT FICHIER="filename" by a lookup that will contain all of those file.

index=my_index
| eval APL=if(APL=="", "n/a", APL)

| where NOT FICHIER="DEVLP.FEXTORG.O04750.PC.REC.J04525"
AND NOT FICHIER="DEV.FEXTORG.O04746.PC.REC.J04525"
AND NOT FICHIER="DEV.FEXTORG.O01758.PC.REC.J04525"
AND NOT FICHIER="DEV.FEXTORG.O02104.PC.REC.J04525"
AND NOT FICHIER="DEV.FEXTORG.O02104.PC.REC.J04525.Y"
AND NOT FICHIER="DEV.PDOI.J04AVIS"
AND NOT FICHIER="CICSDEV.DFHJ04.A0007671.DATA"
AND NOT like(FICHIER, "%J69%")

| lookup SICListeRespCodeApplication_lookup CodeApplication as APL OUTPUT ResponsableTI Courriel
| eval ResponsableTI=trim(ResponsableTI), Courriel=trim(Courriel)
| stats count by ResponsableTI Courriel APL
| eval AppInfo = APL . " (" . count . ")"
| stats values(AppInfo) as Applications by ResponsableTI Courriel
| eval Applications = mvjoin(Applications, ", ")
| table ResponsableTI Courriel Applications


Result:

ResponsableTI Courriel Applications

John Doe	john.doe@gmail.com	J25 (544), J37 (510)
Jane Doe	jane.doe@gmail.com	E26 (33), E30 (2), E73 (8), J04 (401), J10 (42), J14 (1), J24 (9), J30 (15897), J32 (371), J34 (584), J36 (356), J76 (297), J96 (1)
Jim Doe	jim.doe@gmail.com	J45 (98), JLE (2)

[ITWhisperer]

Start with something like this

index ... NOT [| inputlookup FilesToExclude.csv 
    | rename Motif as FICHIER
    | format]
| lookup ApplicationResponsibles.csv CodeApplication as code