Pulling stats for the most recent time for values in a lookup table, why doesn't a join work when entries have a wildcard "*" character?

[octavian_i]

Hi,

I am trying to pull some statistics on what is the most recent time a value in a lookuptable appeared in my Splunk logs. I have managed to get it working using a join, however this doesn't work for *.website.com entries in my lookuptable.

This is what I am trying to search for:

base_search
| search [|inputlookup table | fields dest]
| stats first(time) by dest

table values include:

website1.com
*.website2.com
website3.com
a.b.website4.com

Trying to get the last hit time for e.g. value *.website2.com

If I run the search as it is now, I will get a time for each subdomain, but I would like to get time for the values in the lookup table instead.

Instead of:

b.c.website2.com | 01/01/2015 01:00

I want to see:

*.website2.com | 01/01/2015 01:00

The following search seems to work with values that don't have a *, but the join won't work with * entries (I have other subdomains in the logs but they would not show in the results).

base search
| join dest  [|inputlookup table | fields dest_fqdn | eval dest_ref="dest_ref_" + $dest$] 
| table dest dest_ref

Results are:

website1.com | 01/01/2015 01:00
website3.com | 01/01/2015 01:00
a.b.website4.com | 01/01/2015 01:00
etc.

Would welcome any ideas, thanks!

[lguinn2]

The join command simply looks at the field values as a character string. It doesn't support wildcards and there is no way to make it do so. For a join, the * is simply an asterisk.

I think the answer is to actually use a lookup here! In the transforms.conf stanza for this lookup, make sure that the following is set:

match_type = WILDCARD

This will have the lookup do the wildcard matching for you.

A lookup will probably be more efficient anyway:

base search
| lookup table dest OUTPUT dest_fqdn
| eval dest_ref="dest_ref_" + $dest$] 
| table dest dest_ref dest_fqdn

I am not sure what the whole "dest_ref" thing is about...