Search in two table

[AnnexQ]

Hi,

I have two table.
The first have few ip what i switched dotdecimal

splunk_server="xyserver" index=main
source="/var/log/ids.log" earliest=-24h | stats count by name, dest_ip, src_ip | sort –count
| eval ip_dot_decimal_split=split(src_ip,".")
| eval first=mvindex(ip_dot_decimal_split,0),second=mvindex(ip_dot_decimal_split,1),third=mvindex(ip_dot_decimal_split,2),fourth=mvindex(ip_dot_decimal_split,3)| fields - ip_dot_decimal_split
| eval first=first*pow(256,3),second=second*pow(256,2),third=third*256
| eval ip_address_integer=first+second+third+fourth
| fields - first,second,third,fourth

The second table is a geoIP database (this is a static db)

index=main host="xy2server" sourcetype="geo_ip_locations" earliest="08/24/2021:00:00:00" latest="08/24/2021:00:01:00" | table start_off finish_off "Alpha_2 code" Country

The start_off and finish_off is dotdecimal too.

I want to search the ip_address_integer(created dotdecimal) between predefined ip ranges (start_off / finish_off) and give me back the country and alpha2 values in new columns of the first table. 

Thanks!

[ITWhisperer]

You could append the second search, convert start_off to integer in the same field (ip_integer_address), then sort by ip_integer_address, and use filldown on the country and alpha2 fields.

[AnnexQ]

Hi,

Thank you for your answer, but i think this is not the solution I need or I get it wrong.

There is my two table:

First:

I want add it to the first table the appropriate value from the second table(Alpha2 & Country).
I need to find which country is where my ip falls in his range and add it to my first table as a new columns (A2 Code and Country).

Second

Like this(edited picture):

(the values are not corret)

Thanks!
AnnexQ

[ITWhisperer]

It looks like you used appendcols rather than append - try something like this

splunk_server="xyserver" index=main
source="/var/log/ids.log" earliest=-24h | stats count by name, dest_ip, src_ip | sort –count
| eval ip_dot_decimal_split=split(src_ip,".")
| eval first=mvindex(ip_dot_decimal_split,0),second=mvindex(ip_dot_decimal_split,1),third=mvindex(ip_dot_decimal_split,2),fourth=mvindex(ip_dot_decimal_split,3)| fields - ip_dot_decimal_split
| eval first=first*pow(256,3),second=second*pow(256,2),third=third*256
| eval ip_address_integer=first+second+third+fourth
| fields - first,second,third,fourth
| append [| search index=main host="xy2server" sourcetype="geo_ip_locations" earliest="08/24/2021:00:00:00" latest="08/24/2021:00:01:00" | table start_off finish_off "Alpha_2 code" Country | eval ip_dot_decimal_split=split(start_off ,".")
| eval first=mvindex(ip_dot_decimal_split,0),second=mvindex(ip_dot_decimal_split,1),third=mvindex(ip_dot_decimal_split,2),fourth=mvindex(ip_dot_decimal_split,3)| fields - ip_dot_decimal_split 
| eval first=first*pow(256,3),second=second*pow(256,2),third=third*256
| eval ip_address_integer=first+second+third+fourth
| fields - first,second,third,fourth]
| sort 0 ip_address_integer
| filldown Country 'Alpha_2 code'

[AnnexQ]

Hello,

Thanks for the help again @ITWhisperer . 
Unfortunately i need another result.

I gott this table(with your search):

Off:
I gott same resoult whit this search:

splunk_server="xyserver" index=main
source="/var/log/ids.log" earliest=-24h | stats count by name, dest_ip, src_ip | sort –count
| eval ip_dot_decimal_split=split(src_ip,".")
| eval first=mvindex(ip_dot_decimal_split,0),second=mvindex(ip_dot_decimal_split,1),third=mvindex(ip_dot_decimal_split,2),fourth=mvindex(ip_dot_decimal_split,3)| fields - ip_dot_decimal_split
| eval first=first*pow(256,3),second=second*pow(256,2),third=third*256
| eval ip_address_integer=first+second+third+fourth
| union [ search index=main host="xy2server" sourcetype="geo_ip_locations" earliest="08/24/2021:00:00:00" latest="08/24/2021:00:03:00" | table start_off finish_off "Alpha_2 code" Country]
| fields - first,second,third,fourth

And i don't have idea, how to search the ip_address_integer values in the range  of start_off and Finish _off  and gett back the Country and A2code valoues in the first 4 line.

From Two Tables, I would like to identify which country src-ip is from?

[ITWhisperer]

splunk_server="xyserver" index=main
source="/var/log/ids.log" earliest=-24h | stats count by name, dest_ip, src_ip | sort –count
| eval ip_dot_decimal_split=split(src_ip,".")
| eval first=mvindex(ip_dot_decimal_split,0),second=mvindex(ip_dot_decimal_split,1),third=mvindex(ip_dot_decimal_split,2),fourth=mvindex(ip_dot_decimal_split,3)| fields - ip_dot_decimal_split
| eval first=first*pow(256,3),second=second*pow(256,2),third=third*256
| eval ip_address_integer=first+second+third+fourth
| fields - first,second,third,fourth
| append [| search index=main host="xy2server" sourcetype="geo_ip_locations" earliest="08/24/2021:00:00:00" latest="08/24/2021:00:01:00" | table start_off finish_off "Alpha_2 code" Country
| eval ip_address_integer=start_off]
| sort 0 ip_address_integer
| filldown Country 'Alpha_2 code'

[AnnexQ · Accepted solution]

Hi,

Finally find the right search:

index=main host="xy2server" sourcetype="geo_ip_locations" earliest="08/24/2021:00:00:00" latest="08/24/2021:00:03:00" | table start_off finish_off Country "Alpha_2 code"
| join max=0 [search splunk_server="xyserver" index=main source="/var/log/ids.log" earliest=-24h@h | stats count by name, dest_ip, src_ip | sort –count
| eval ip_dot_decimal_split=split(src_ip,".")
| eval first=mvindex(ip_dot_decimal_split,0),second=mvindex(ip_dot_decimal_split,1),third=mvindex(ip_dot_decimal_split,2),fourth=mvindex(ip_dot_decimal_split,3)| fields - ip_dot_decimal_split
| eval first=first*pow(256,3),second=second*pow(256,2),third=third*256
| eval ip_address_integer=first+second+third+fourth
| table name src_ip dest_ip ip_address_integer] | where ip_address_integer>=start_off and ip_address_integer<=finish_off
| fields - start_off, finish_off, ip_address_integer
| table name dest_ip src_ip Country "Alpha_2 code"