Getting Data In

How to use join fields?

majilan1
Path Finder

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!

 

Labels (1)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

| fields host_name count E_error L_error

View solution in original post

0 Karma

majilan1
Path Finder

screen.PNG

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

What search did you use for that result?

0 Karma

majilan1
Path Finder

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

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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
0 Karma

majilan1
Path Finder

It does not, it gives only field host .

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

0 Karma

majilan1
Path Finder

Thanks ITWhisperer,

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

Thanks

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

0 Karma

majilan1
Path Finder

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

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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
0 Karma

majilan1
Path Finder

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

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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.

0 Karma

majilan1
Path Finder

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!

0 Karma

majilan1
Path Finder

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

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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?

0 Karma

majilan1
Path Finder

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. 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

| fields host_name count E_error L_error

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

0 Karma
Get Updates on the Splunk Community!

The Splunk Success Framework: Your Guide to Successful Splunk Implementations

Splunk Lantern is a customer success center that provides advice from Splunk experts on valuable data ...

Splunk Training for All: Meet Aspiring Cybersecurity Analyst, Marc Alicea

Splunk Education believes in the value of training and certification in today’s rapidly-changing data-driven ...

Investigate Security and Threat Detection with VirusTotal and Splunk Integration

As security threats and their complexities surge, security analysts deal with increased challenges and ...