topic Re: Nested inputlookup with where clause in Splunk Search

Nested inputlookup with where clause

MattKr — Thu, 30 Nov 2023 15:22:46 GMT

I am trying to build my own kvstore geo data, so far i can run

| inputlookup geobeta | where endIPNum >= 1317914622 and startIPNum <= 1317914622 | table latitude,longitude

That returns:

latitude,longitude
"51.5128","-0.0638"

But how do i combine this with a search?

I was trying this but it doesn't work:

| makeresults eval ip_address_integer=1317914622 [ | inputlookup geobeta | where endIPNum >= ip_address_integer and startIPNum <= ip_address_integer ]

Many thanks for hints

Re: Nested inputlookup with where clause

gcusello — Thu, 30 Nov 2023 15:40:55 GMT

Hi @MattKr ,

what have you to do? use the lookup's geo coordinates to filter results or what else?

if you want to use the values in the lookup for a subsearch, you have to use the rules of a subsearch, so the fields in the subsearch must have the same field names.

Then you can use thewhere clause inside the inputlookup command.

Put attention that the AND logical operator must be in uppercase to be recognized:

| inputlookup geobeta WHERE endIPNum>=1317914622 AND startIPNum<=1317914622 | table latitude longitude

Then, how are your IP address written?

At least, using lookups, you can also have CIDR match type as described at https://docs.splunk.com/Documentation/SplunkCloud/latest/Knowledge/Usefieldlookupstoaddinformationtoyourevents

Ciao.

Giuseppe

Re: Nested inputlookup with where clause

ITWhisperer — Thu, 30 Nov 2023 16:24:20 GMT

Subsearches are executed before the main search so your ip_address_integer has no value when the inputlookup is executed.

You could try using the map command (although this has its limitations and perhaps should be avoided where possible).

Re: Nested inputlookup with where clause

MattKr — Thu, 30 Nov 2023 19:59:08 GMT

Hi Giuseppe,
thanks for your response.
I would like to expand the IPv4 addresses and are looking for lat/lon, country and AS informations.
As i do already have the maxmind database in my own mySQL i thought to convert the data into a kvstore
and get my data from there.

None of the Apps on the market had ready suited for me unless i missed one, so i thought to do this by my own .. its just a hobby ...

Cheers

Matthias

Re: Nested inputlookup with where clause

MattKr — Thu, 30 Nov 2023 20:00:27 GMT

Thank you very much, that explains it!
I was able to complete my little proof of concept, this is my complete search:

sourcetype=nftemp | top 100 SRC | eval ip_address = SRC | eval ip_dot_decimal_split=split(ip_address,".") | 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 | map search=" | inputlookup geobeta | where endIPNum >= $ip_address_integer$ AND startIPNum <= $ip_address_integer$ | eval ip=$ip_address$ | eval mapcount=$count$ | sort mapcount | table mapcount,ip,country_iso_code,latitude,longitude,ASName,ASNumber" maxsearches=20000

The sourcetype is a random generated nftables log with a few IPs in it, then convert the ip's to decimal
and do the search against the geobeta lookup. The source of the geobeta lookup contains also only a few records, not sure how it will perform when the geobeta lookup will have millions of records in it, lets see ... geobeta comes from maxmind by the way.