Splunk Search

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
New Member

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!

0 Karma

lguinn2
Legend

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...