Splunk Search

Search in two table

AnnexQ
Explorer

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. 

splunk Q.JPG

Thanks!

Labels (1)
0 Karma
1 Solution

AnnexQ
Explorer

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"

 

View solution in original post

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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.

0 Karma

AnnexQ
Explorer

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:
splunk ipai.JPG

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

spunk geol.JPG

Like this(edited picture):
splunk2.jpg
(the values are not corret)

Thanks!
AnnexQ

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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'

 

0 Karma

AnnexQ
Explorer

Hello,

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

I gott this table(with your search):

splunk.JPG

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?

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
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'
0 Karma

AnnexQ
Explorer

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"

 

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...