Umm.......decode the ip in log_visit for me please X(


#1

Hey Y’all,

I just trying to decode the value stored in log_visit.location_ip. I need to run a query from my database to compare the ip and id from the log_visit table. We pull the IP on our database and store that value and I need to find (through Piwiks awesome abilities) the more accurate referral_url and keywords used to get there. I was gonna run a query from the ip store at the time they viewed our site and the time the did so and run a comparison so I could find the referral_url/keywords. I tried inet_ntoa() and that bad boy gave me a whole bunch of 0.0.0.0. I have Piwik 1.5.1 and a little time on my hands…and a turkey sandwich to keep me company.

acollier21

P.S. Varbinary? Really? Who does that… :S


#2

Well…I finished my turkey sandwich…

For anyone else who is desperately trying to solve this problem, in PHP use inet_ntop()

http://php.net/manual/en/function.inet-ntop.php

acollier21


#3

I am doing similar but need to solve in a SQL statement.

I need to filter the piwik_log_visit table where the IP address (location_ip column) starts with something like ‘10.11’ so any IP 10.11.x.x address.

But so far I can’t even figure out how to get the IP address (since it is binary data of some sort) let alone compare against it.

Anyone know how to solve that?


(Matthieu Aubry) #4

ssmilie, you should use the API for Live to get all last visits: http://piwik.org/docs/analytics-api/reference/#Live

Then you can segment using the segmentation API based on the visitIp segment with ip>14.3.4.1;ip<=100.100.100.100 which works! Segmentation - Compare segments of visitors - Analytics Platform - Matomo


#5

But I don’t want a specific IP but rather a IP range.
How do you specify a wildcard in the IP address of the API?


(Matthieu Aubry) #6

Re-read my post, I show you how to use IP range:


Then you can segment using the segmentation API based on the visitIp segment with ip>14.3.4.1;ip<=100.100.100.100 which works!

will select all IPs between 14.3.4.1 and 100.100.100.100


(Matthieu Aubry) #7

See also: How to - Analytics Platform - Matomo