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!

Sending Metrics to Splunk Enterprise With the OpenTelemetry Collector

This blog post is part of an ongoing series on OpenTelemetry. The OpenTelemetry project is the second largest ...

What's New in Splunk Cloud Platform 9.0.2208?!

Howdy!  We are happy to share the newest updates in Splunk Cloud Platform 9.0.2208! Analysts can benefit ...

Want a chance to win $500 to the Splunk shop? Take our IT Incident Management Survey!

  Top Trends & Best Practices in Incident ManagementSplunk is partnering up with Constellation Research to ...