search using Inputlookup with wildcard field - unable to retain wildcard key in result

[rahul0621]

I am using inputlookup in a search query and search key in table (test.csv) has wildcard as shown below.

FILENAME	EMAIL
abc*	test1@a.com
xyz*	test2@a.com

The query should match fname in log file with FILENAME from lookup table and if there's a match then result should be something like:

FILENAME	EMAIL	cnt
abc*	test1@a.com	2
xyz*	test2@a.com	0

Instead my query output is:

fname	EMAIL	count
abc*	test1@a.com	0
abc123.txt		1
abc.dat		1
xyz*	test2@a.com	0

This is my query:

index=* host=* source="/bustools/*"
| rex max_match=100 "\d+\d+\s(?<ts>.*)\s(?<directory>\/.*)\/(?<fname>.*)"
| dedup fname
| search [ | inputlookup test.csv | rename FILENAME AS fname
| fields fname]
| stats count as occur by fname | append
[ inputlookup test.csv | rename FILENAME AS fname | fields fname]
| fillnull occur
| stats sum(occur) as cnt BY fname
| join type=left fname
[ | inputlookup test.csv | rename FILENAME AS fname]
| table fname EMAIL cnt

Any help would be appreciated.

[kamlesh_vaghela · Accepted solution]

@rahul0621 

I have change the approach for your requirement. May me useful for you. Can you please try below search?

| inputlookup test.csv | eval fname = FILENAME | map search="index=* host=* source="/bustools/*" | rex max_match=100 "\d+\d+\s(?<ts>.*)\s(?<directory>\/.*)\/(?<fname>.*)" | dedup fname | search fname="$fname$" | stats count by fname | appendcols [ | makeresults |eval FILENAME="$FILENAME$",EMAIL="$EMAIL$"] | filldown FILENAME EMAIL " | fillnull count | stats sum(count) by FILENAME,EMAIL

Note:

• Here, I have used map command. Please visit https://docs.splunk.com/Documentation/Splunk/8.0.4/SearchReference/Map for more information

This is my Sample Search in case you want to trace logic:

| makeresults | eval FILENAME="splunk_*",EMAIL="test1@a.com" | append [ | makeresults | eval FILENAME="splunkd_*",EMAIL="test2@a.com"] | append [ | makeresults | eval FILENAME="splunks_*",EMAIL="test3@a.com"] | table FILENAME EMAIL | rename comments as "This is lookup data" | eval fname = FILENAME | map search="search index=_internal | rename sourcetype as fname | dedup fname | search fname="$fname$" | stats count by fname | appendcols [ | makeresults |eval FILENAME="$FILENAME$",EMAIL="$EMAIL$"] | filldown FILENAME EMAIL " | fillnull count | stats sum(count) by FILENAME,EMAIL

 Thanks
Kamlesh Vaghela

[rahul0621]

@kamlesh_vaghela  I had to tweak it a bit but this approach worked perfectly. Thanks a lot for your quick help.