SPARQL IP Address string to long integer

[Peter Neorr]

Hello All,

One of my project's current challenges is querying for IP Blocks that contain a given IP address.  

Here's an example use case:

31.187.93.96/28 is an IP block reserved for Antarctica.  I want to use Stardog to help me determine that an event with an :ipAddress of "31.187.93.100" is related to Antarctica.

We've done some processing (outside of Star Dog) so that each individual of type :IPBlock has a :minIPAddressAsLong and :maxIPAddressAsLong.

For my example use case, The min address is 31.187.93.96 and max address is 31.187.93.111 (As long integers, 532372832 and 532372847 respectively)

Is there a clever way to use SPARQL to convert "31.187.93.100" to it's long representation of 532372836?  This would make it easy for me to just use simple greater-than/less-than filters to find matching IPBlocks.

If the IP address is aaa.bbb.ccc.ddd, then it's long integer value should be  (aaa*256^3) + (bbb*256^2) + (ccc*256^1) + (ddd*256^0).

The algorithm is simple, but the string manipulation and arithmetic are beyond my current sparql abilities.

I've been considering writing my own set stardog functions in java to make this easier. E.g.

  isInIPRange("31.187.93.100","31.187.93.96/28")

 

But if I can do this with "pure" sparql, I'd like to try that first.  

Any bright ideas?

Regards,

Peter Neorr

[Michael Grove]

On Thu, Jul 28, 2016 at 11:33 AM, Peter Neorr <peter...@gmail.com> wrote:
> Hello All,
>
> One of my project's current challenges is querying for IP Blocks that
> contain a given IP address.
>
> Here's an example use case:
> 31.187.93.96/28 is an IP block reserved for Antarctica. I want to use
> Stardog to help me determine that an event with an :ipAddress of
> "31.187.93.100" is related to Antarctica.
>
> We've done some processing (outside of Star Dog) so that each individual of
> type :IPBlock has a :minIPAddressAsLong and :maxIPAddressAsLong.
> For my example use case, The min address is 31.187.93.96 and max address is
> 31.187.93.111 (As long integers, 532372832 and 532372847 respectively)
>
> Is there a clever way to use SPARQL to convert "31.187.93.100" to it's long
> representation of 532372836? This would make it easy for me to just use
> simple greater-than/less-than filters to find matching IPBlocks.
>
> If the IP address is aaa.bbb.ccc.ddd, then it's long integer value should be
> (aaa*256^3) + (bbb*256^2) + (ccc*256^1) + (ddd*256^0).
>
> The algorithm is simple, but the string manipulation and arithmetic are
> beyond my current sparql abilities
>

> I've been considering writing my own set stardog functions in java to make
> this easier. E.g.
> isInIPRange("31.187.93.100","31.187.93.96/28")
>
> But if I can do this with "pure" sparql, I'd like to try that first.

I'd recommend you just implement your own function [1] to do this.

Cheers,

Mike

[1] https://github.com/Complexible/stardog-examples/tree/develop/examples/function

>
> Any bright ideas?
>
> Regards,
> Peter Neorr
>

> --
> -- --
> You received this message because you are subscribed to the C&P "Stardog"
> group.
> To post to this group, send email to sta...@clarkparsia.com
> To unsubscribe from this group, send email to
> stardog+u...@clarkparsia.com
> For more options, visit this group at
> http://groups.google.com/a/clarkparsia.com/group/stardog?hl=en

[Zachary Whitley]

Oddly enough I happen to have already done that and I'll get you a copy. Are you by any chance working with the Maxmind GeoIP database?

---
You received this message because you are subscribed to the Google Groups "Stardog" group.
To unsubscribe from this group and stop receiving emails from it, send an email to stardog+u...@clarkparsia.com.

[Zachary Whitley]

https://github.com/semantalytics/stardog-udf-utils/blob/master/src/main/java/com/semantalytics/stardog/function/util/InetAddressToNumber.java
https://github.com/semantalytics/stardog-udf-utils/blob/master/src/main/java/com/semantalytics/stardog/function/util/InetNumberToAddress.java

Is that what you're looking for? You should be able to check out the project, build and throw the jar into $STARDOG_HOME/server/ext, restart and you're good to go. You'll need to create the ext directory. The url's for the functions are in the source files linked above. That project is just a grab bag of stupid stuff and experiments but I just happen to have experimented with just what you're looking for.

Hope that helps.

[Peter Neorr]

This is great!   I can't wait to try it out!

Thanks!

-Peter

[Peter Neorr]

As a matter of fact, we are using the free Maxmind's free geoip CSV downloads.  We wrote a some utility code to take their CIDR notation column and add columns for for min and max ip address. This allows allows to insert data as long integers, and easily find matching IPBlocks for a given IP address.  Our hope is to get some built-in stardog functions working so we no longer have to pre-process the data.

-Peter

[Zachary Whitley]

On Jul 28, 2016, at 10:56 PM, Peter Neorr <peter...@gmail.com> wrote:

As a matter of fact, we are using the free Maxmind's free geoip CSV downloads.  We wrote a some utility code to take their CIDR notation column and add columns for for min and max ip address. This allows allows to insert data as long integers, and easily find matching IPBlocks for a given IP address.  Our hope is to get some built-in stardog functions working so we no longer have to pre-process the data.

-Peter

That's exactly what I wrote it for. I was a little disappointed at the dearth of good datasets out there and thought that it would be a great way to demonstrate Stardog's virtual graphs, geo support, custom functions and data integration with geonames. 

The geonames dataset doesn't make for a very good demonstration of a great capability. Unfortunately I've been a little busy lately and haven't been able to finish it up but it's on my todo list. If you could use any other custom functions just let me know. They're quite easy to write.