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.
P.S. Varbinary? Really? Who does that… :S
Well…I finished my turkey sandwich…
For anyone else who is desperately trying to solve this problem, in PHP use inet_ntop()
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?
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>18.104.22.168;ip<=100.100.100.100 which works! Segmentation - Compare segments of visitors - Analytics Platform - Matomo
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?
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>22.214.171.124;ip<=100.100.100.100 which works!
will select all IPs between 126.96.36.199 and 100.100.100.100