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!
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"
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.
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
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'
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?
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'
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"