topic Re: Using Join to fields with another values in Splunk Search

Using Join to fields with another values

Astorn — Mon, 15 Feb 2021 11:47:27 GMT

I have lookup with possible sources and i'm comparing them with the real log events to check if any of them don't sending as expected. The hosts in lookup are without domain but the hosts in logs have added domain to the hostname. I want to join both lookup and lists of sending hosts but i need that the command that will join superSide and superSide.computer.level.com as one hostname. I have found answers with the wild card but it seems not working, is there any other nice answer for this problem?

Re: Using Join to fields with another values

manjunathmeti — Mon, 15 Feb 2021 12:06:16 GMT

hi @Astorn,

You can strip domain name from host then do the lookup.

base_search | rex field=host "^(?<host_short>[^\.]+)" | lookup lookup_name host AS host_short OUTPUT output_field

If this reply helps you, an upvote/like would be appreciated.

Re: Using Join to fields with another values

ITWhisperer — Mon, 15 Feb 2021 12:06:19 GMT

You could try stripping the domain from your host name before joining, but if I understand correctly, you just want the hosts which haven't got log entries, so, strip the domain name from your log host names, dedup by hostname, append your lookup data and count by hostname. Anything with a count of 1 will have come from your lookup, count of 2 appears in both your logs and your lookup. Is that what you are after?

Re: Using Join to fields with another values

Astorn — Tue, 16 Feb 2021 06:45:44 GMT

Thanks it is one possible solution, but it seems not very elegant, i'm looking for more modular way to do this. For example in some way it is not working, i have many domains and may be in some case i want to have in register the hostname with some subdomains (part of all domain url). I will prefer modular solution when i can defined my own way to compare values to join.

Re: Using Join to fields with another values

ITWhisperer — Tue, 16 Feb 2021 06:57:33 GMT

What do you mean by modular?

Also, please confirm that you want to find the hosts from your lookup that don't have recent log entries, or are you looking for something else?

Re: Using Join to fields with another values

Astorn — Tue, 16 Feb 2021 07:17:02 GMT

May be not modular but universal solution. My example:

in my logs i have:

super-website.computer.pl

but in the register(excel ->lookup) i have

super-website 1.2.4.2.

But maybe it will not be enought and i will have two

super-website.computer.pl

super-website.magic.pl

so will have to change register to have super-website.computer and another record super-website.magic.

I want to be able to join the records base on the rule record_from_log.domain="record_from_register*"to match based on wild card, i'm quite new in splunk search language so maybe my questions are not very precisly.

Re: Using Join to fields with another values

Astorn — Tue, 16 Feb 2021 10:16:40 GMT

OK it works but not exactly output as expected. I have sth like this in logs.

server.net1.com

server.net2.com

The hostname are the same the subdomain are other.

So i the register i have

server.net1

server.net2

Then the rex not working.

Is there a way to user regex to change server.net1 to be server.net1* and will join with server.net1.com

Re: Using Join to fields with another values

manjunathmeti — Tue, 16 Feb 2021 10:30:14 GMT

Check this site: https://splunkonbigdata.com/2020/08/04/handling-wildcard-characters-in-lookup-file/ . This should solve your problem. You should create csv file with field with wildcard values.

hostname, field1, field2 server.net1*, abc, xyz server.net2*,abc,xyz

Re: Using Join to fields with another values

Astorn — Tue, 16 Feb 2021 12:09:39 GMT

Is there a way to add the * character to each host dynamicaly in search?