topic Re: Search in two table in Splunk Search

Search in two table

AnnexQ — Wed, 25 Aug 2021 13:39:14 GMT

Hi,

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

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!

Re: Search in two table

ITWhisperer — Wed, 25 Aug 2021 14:15:11 GMT

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.

Re: Search in two table

AnnexQ — Wed, 25 Aug 2021 15:43:37 GMT

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

Re: Search in two table

ITWhisperer — Wed, 25 Aug 2021 16:34:05 GMT

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'

Re: Search in two table

AnnexQ — Mon, 30 Aug 2021 13:30:53 GMT

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:

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?

Re: Search in two table

ITWhisperer — Tue, 31 Aug 2021 09:16:36 GMT

Re: Search in two table

AnnexQ — Mon, 20 Sep 2021 11:56:33 GMT

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"