topic Re: How to use join fields? in Getting Data In

How to use join fields?

majilan1 — Wed, 13 Jul 2022 15:54:01 GMT

Hi Splunkers,

I struggled badly trying to get this solved, but no luck?

I need to join to a different search using the ip_address to get the host name :

Base search for the join: index= X sourcetype=server dv_ir=4311.00.
The dv_name field is the host name and the dv_ip_address is the ip_address.

Any help will be appreciated.

Thank you all!

Re: Using join fields

ITWhisperer — Wed, 13 Jul 2022 14:54:38 GMT

It is not clear which events you are trying to "join" with which - where do these events come from - can you use a lookup to find the host name?

Re: Using join fields

majilan1 — Wed, 13 Jul 2022 15:41:11 GMT

Thanks for your fast response!

The only thing that I'm struggling to get is the host name, So This is what I have so far:

index=app sourcetype=pw:ws "Failed handshake due to exhausted 15 seconds timeout on channel" earliest=06/16/2022:00:00:00

| rex field=_raw "(R:\/)(?<ip>[\d.]*)(:8609)"

| stats earliest(_time) as E_error latest(_time) as L_error count by ip

| join type=left ip [| search earliest=-24h index= X sourcetype=server dv_ir=4311.00

| stats latest(dv_name) as host_name by dv_ip_address

| rename dv_ip_address as ip]

| eval E_error=strftime(E_error,"%m/%d/%Y %H:%M:%S"), L_error=strftime(L_error,"%m/%d/%Y %H:%M:%S")

| fields host count E_error L_error

Just to clarify, what I'm trying to do in here is to create a new alert in the custom email template to notify if we receive handshake errors in the web logs. I had some of these errors on 5/20, so I need to adjust the time range to build/test the search and alert, for all of these I need to parse the ip_address out of the raw data.

The alert should display : the host, number of handshake, time of first instance error on the host, and time of the recent error on the host.

Re: Using join fields

ITWhisperer — Wed, 13 Jul 2022 15:46:34 GMT

What is this line supposed to be doing

| stats values(hostname) by ip_addresses

because after that dv_ip_address no longer exists so there is nothing to rename

Re: Using join fields

majilan1 — Wed, 13 Jul 2022 15:49:25 GMT

I edited that already if you refresh you won't see it, that was a mistake.

Re: Using join fields

ITWhisperer — Wed, 13 Jul 2022 15:53:40 GMT

| fields host_name count E_error L_error

Re: How to use join fields?

majilan1 — Wed, 13 Jul 2022 15:59:59 GMT

Re: How to use join fields?

ITWhisperer — Wed, 13 Jul 2022 16:02:06 GMT

What search did you use for that result?

Re: How to use join fields?

majilan1 — Wed, 13 Jul 2022 16:12:27 GMT

This is the search:

| rex field=_raw "(R:\/)(?<ip>[\d.]*)(:8609)"

| stats earliest(_time) as earliest_error latest(_time) as latest_error count by ip

| join type=left ip [| search earliest=-24h index=inventory_snow index= X sourcetype=server dv_ir=4311.00
| stats latest(dv_name) as host by dv_ip_address
| rename dv_ip_address as ip]

| eval earliest_error=strftime(earliest_error,"%m/%d/%Y %H:%M:%S"), latest_error=strftime(latest_error,"%m/%d/%Y %H:%M:%S")

| fields host count earliest_error latest_error

Re: How to use join fields?

ITWhisperer — Wed, 13 Jul 2022 16:16:10 GMT

Does this give you any host names for the ip addresses you are interested in?

earliest=-24h index=inventory_snow index= X sourcetype=server dv_ir=4311.00 | stats latest(dv_name) as host by dv_ip_address | rename dv_ip_address as ip

Re: How to use join fields?

majilan1 — Wed, 13 Jul 2022 16:23:48 GMT

It does not, it gives only field host .

Re: How to use join fields?

ITWhisperer — Wed, 13 Jul 2022 16:26:49 GMT

host is a field used by Splunk usually for the host the event originated on - try a different name

earliest=-24h index=inventory_snow index= X sourcetype=server dv_ir=4311.00 | stats latest(dv_name) as host_name by dv_ip_address | rename dv_ip_address as ip

If you still don't get any host names in host_name, you need to look at your data in index X

Re: How to use join fields?

majilan1 — Wed, 13 Jul 2022 16:49:51 GMT

The host name will not be available in the web logs, that's why I join to a different search using the ip_address to get the host name, even doing all of that still no luck!

Re: How to use join fields?

majilan1 — Thu, 14 Jul 2022 13:42:46 GMT

Thanks ITWhisperer,

Just as an update, the field that I need to join on need to have the same exact name.

Thanks

Re: How to use join fields?

ITWhisperer — Thu, 14 Jul 2022 14:23:33 GMT

No, they don't have to be the same, but, if they are not the same, you need to show which field from the left is joined to which field on the right join - Splunk Documentation

Re: How to use join fields?

majilan1 — Thu, 14 Jul 2022 14:48:33 GMT

Thanks again for your quick response, please take a look at this:

index=X sourcetype=Y "Failed handshake due to exhausted 10 seconds timeout on channel" earliest=06/16/2022:00:00:00

| rex field=_raw "(R:\/)(?<ip>[\d.]*)(:8609)"

| stats earliest(_time) as earliest_error latest(_time) as latest_error count by ip

| join type=left ip [| search earliest=-24h index=A sourcetype=B dv_u_eir=4311.00

| stats latest(dv_name) as host by dv_ip_address

| rename dv_ip_address as ip]

| eval earliest_error=strftime(earliest_error,"%m/%d/%Y %H:%M:%S"), latest_error=strftime(latest_error,"%m/%d/%Y %H:%M:%S")

| fields host count earliest_error latest_error

Re: How to use join fields?

ITWhisperer — Thu, 14 Jul 2022 14:54:30 GMT

Don't use host as your field name as this is already used by Splunk, chose a different name

index=X sourcetype=Y "Failed handshake due to exhausted 10 seconds timeout on channel" earliest=06/16/2022:00:00:00 | rex field=_raw "(R:\/)(?<ip>[\d.]*)(:8609)" | stats earliest(_time) as earliest_error latest(_time) as latest_error count by ip | join type=left ip [| search earliest=-24h index=A sourcetype=B dv_u_eir=4311.00 | stats latest(dv_name) as host_name by dv_ip_address | rename dv_ip_address as ip] | eval earliest_error=strftime(earliest_error,"%m/%d/%Y %H:%M:%S"), latest_error=strftime(latest_error,"%m/%d/%Y %H:%M:%S") | fields host_name count earliest_error latest_error

Check that your subsearch actually finds the ip addresses you are expecting to be there

earliest=-24h index=A sourcetype=B dv_u_eir=4311.00 | stats latest(dv_name) as host_name by dv_ip_address | rename dv_ip_address as ip

Re: How to use join fields?

majilan1 — Thu, 14 Jul 2022 15:26:08 GMT

The subsearch actually finds the IP addresses that I'm looking for and everything looks great until the last line where I have :

| fields host_name count earliest_error latest_error at this point the IP address changed to host_name with no output on the host_name field.

Thanks

Re: How to use join fields?

ITWhisperer — Thu, 14 Jul 2022 15:33:33 GMT

Try this

| fields ip host_name count earliest_error latest_error

If the subsearch finds the ip addresses (and they are an exact match for the ip address in the outer search), then the host_name field should have the host name returned by the subsearch.