How to use join fields?

[majilan1]

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!

[majilan1]

 

[ITWhisperer]

What search did you use for that result?

[majilan1]

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

[ITWhisperer]

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

[majilan1]

It does not, it gives only field host .

[ITWhisperer]

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

[majilan1]

Thanks ITWhisperer,

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

Thanks

[ITWhisperer]

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

[majilan1]

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

[ITWhisperer]

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

[majilan1]

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

[ITWhisperer]

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.

[majilan1]

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!

[majilan1]

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

[ITWhisperer]

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?

[majilan1]

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. 

[ITWhisperer · Accepted solution]

| fields host_name count E_error L_error

[ITWhisperer]

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