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!
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...