magento-vistor-logs.sql

-- Make sure to uncomment one of the where conditions below or your query will take forever
select
    lu.visit_time,
    inet_ntoa(lvi.server_addr) server_addr,
    inet_ntoa(lvi.remote_addr) remote_addr,
    lui.url,
    lc.customer_id,
    ce.email,
    cs.name store,
    lvi.http_user_agent,
    lui.referer,
    lvi.http_accept_language
from log_url lu
inner join log_url_info lui
    on lui.url_id = lu.url_id
inner join log_visitor lv
    on lv.visitor_id = lu.visitor_id
inner join log_visitor_info lvi
    on lvi.visitor_id = lu.visitor_id
inner join core_store cs
    on lv.store_id = cs.store_id
left join log_customer lc
    on lc.visitor_id = lu.visitor_id
left join customer_entity ce
    on ce.entity_id = lc.customer_id
where 1 = 1
    -- Search by email
--    and ce.email = 'email@example.com'
    -- By IP address
--    and lvi.remote_addr = inet_aton('8.8.8.8')
    -- Urls to ignore
    and lui.url not like '%ajax%'
order by lu.visit_time;