-- -- phpWebLogAnalyzer - powerful weblog and analyzer -- -- sql/queries/mysql.sql - MySQL statistics queries -- ____________________________________________________________ -- -- Developed by Ondrej Jombik -- Copyright (c) 2001-2005 Platon SDG, http://platon.sk/ -- All rights reserved. -- -- See README file for more information about this software. -- See COPYING file for license information. -- -- Download the latest version from -- http://platon.sk/projects/phpWebLogAnalyzer/ -- -- $Platon: phpWebLogAnalyzer/sql/queries/mysql.sql,v 1.7 2003/01/27 01:07:57 nepto Exp $ -- NOTE: always use 'join with blanks' functionality in `screen', -- where copy/pasting query into your mysql console client -- -- Detecting double records -- (records, which can be created by PHP timeout exceed) -- select id, datetime, value_id, session_id, count(*) from weblog_accesses group by datetime, value_id, request_method_id, query_string_id, path_info_id, path_translated_id, content_type_id, content_length, script_name_id, server_name_id, server_port, server_software_id, server_protocol_id, gateway_interface_id, proxy_addr_id, proxy_host_id, remote_addr_id, remote_host_id, remote_port, auth_type_id, remote_user_id, remote_ident_id, document_root_id, http_accept_id, http_accept_charset_id, http_accept_encoding_id, http_accept_language_id, http_connection_id, http_host_id, http_referer_id, http_user_agent_id, script_filename_id, server_admin_id, server_signature_id, request_uri_id, ident_username_id, ident_ostype_id, session_id having count(*) > 1; -- -- weblog_accesses_raw queries -- -- actual state for HTTP hosts select count(distinct session_id), http_host_id from weblog_accesses_raw group by http_host_id ; -- actual state for servers select count(distinct session_id), server_name_id from weblog_accesses_raw group by server_name_id; -- actial state of scripts select count(distinct session_id) as c, script_name_id from weblog_accesses_raw group by script_name_id order by c desc; -- -- weblog_accesses queries -- -- accesses statistic for particular months select month(datetime), count(distinct session_id) from weblog_accesses group by month(datetime); select concat(monthname(datetime), ' ', date_format(datetime, '%Y')) as month, count(distinct session_id) as sessions, count(id) as accesses from weblog_accesses group by month order by datetime; select date_format(datetime, '%Y-%m') as month, count(distinct session_id) as sessions, count(id) as accesses from weblog_accesses group by month order by month; -- accesses statistic for paticular months for defined server select date_format(datetime, '%Y-%m-%d') as day, count(distinct session_id) as sessions, count(weblog_accesses.id) as accesses from weblog_accesses, weblog_server_names where weblog_accesses.server_name_id = weblog_server_names.id and weblog_server_names.value = 'www.prave-spektrum.sk' group by day order by sessions DESC limit 10; -- the same statistic as th one above, but looks better -- replace with your server name select concat(monthname(datetime), ' ', date_format(datetime, '%Y')) as month, count(distinct weblog_accesses.session_id) as sessions, count(weblog_accesses.id) as accesses, count(distinct weblog_remote_addrs.value) as IPs from weblog_accesses, weblog_remote_addrs, weblog_server_names where weblog_accesses.server_name_id = weblog_server_names.id and weblog_accesses.remote_addr_id = weblog_remote_addrs.id and weblog_server_names.value like '' group by month order by datetime; -- accesses statistic for particular years select date_format(datetime, '%Y') as year, count(distinct session_id) as sessions, count(id) as accesses from weblog_accesses group by year order by year; -- accesses statistic for particular days select dayofweek(datetime), count(distinct session_id) from weblog_accesses group by dayofweek(datetime); select dayofweek(datetime) as n, dayname(datetime) as day, count(distinct session_id) as sessions, count(id) as accesses from weblog_accesses group by dayname(datetime) order by n; -- statistic for particular days in month -- Frlo note: around salary time are most of accesses ;-) select dayofmonth(datetime) as day, count(distinct session_id) as sessions, count(id) as accesses from weblog_accesses group by dayofmonth(datetime) order by sessions; -- -- Day statistics -- -- 10 most bad days from sessions count view select date_format(datetime, '%Y-%m-%d') as day, count(distinct session_id) as sessions, count(id) as accesses from weblog_accesses group by day order by sessions limit 10; -- 10 most good days from sessions count view select date_format(datetime, '%Y-%m-%d') as day, count(distinct session_id) as sessions, count(id) as accesses from weblog_accesses group by day order by sessions DESC limit 10; -- -- FIXME: notes -- -- total accesses count for particular servers -- IMPORTANT: bad query; it uses wrong `group by' clausule -- although that it works, because it is grouped by primary index select weblog_server_names.value as server, count(distinct weblog_accesses.session_id) as sessions, count(weblog_accesses.id) as accesses from weblog_accesses, weblog_server_names where weblog_accesses.server_name_id = weblog_server_names.id group by weblog_server_names.id; -- total accesses count for particular HTTP hosts select weblog_http_hosts.value as server, count(distinct weblog_accesses.session_id) as sessions, count(weblog_accesses.id) as accesses from weblog_accesses, weblog_http_hosts where weblog_accesses.http_host_id = weblog_http_hosts.id group by weblog_http_hosts.id; -- SYSTEM: session list which have more than one ident -- replace ident_username_id with some else column to get another infos select session_id, count(distinct ifnull(ident_username_id,0)) as cnt from weblog_accesses group by session_id having cnt > 1; -- session list print select min(datetime) as begin_time, session_id as sID, weblog_remote_hosts.value as remote_host, weblog_remote_addrs.value as remote_addr, weblog_http_hosts.value as http_host from weblog_accesses, weblog_remote_hosts, weblog_remote_addrs, weblog_http_hosts where weblog_accesses.remote_host_id=weblog_remote_hosts.id and weblog_accesses.remote_addr_id=weblog_remote_addrs.id and weblog_accesses.http_host_id=weblog_http_hosts.id and weblog_http_hosts.value like '%' group by session_id, http_host order by begin_time desc limit 30; -- SYSTEM: quick session print select weblog_accesses.id, session_id, datetime, http_host_id hh, remote_addr_id as ra, http_user_agent_id ua_id, weblog_ident_usernames.value as idnt from weblog_accesses left join weblog_ident_usernames on weblog_accesses.ident_username_id = weblog_ident_usernames.id where session_id = ; select datetime, weblog_request_uris.value from weblog_accesses left join weblog_request_uris on weblog_accesses.request_uri_id = weblog_request_uris.id where weblog_accesses.session_id = order by datetime asc; # does not works now # select distinct a.session_id, weblog_server_names.value, # weblog_http_hosts.value, weblog_http_user_agents.value, # weblog_ident_ostypes.value, weblog_ident_usernames.value, # weblog_remote_addrs.value, weblog_remote_hosts.value # from weblog_accesses a, # weblog_accesses left join weblog_server_names # on server_name_id = weblog_server_names.id, # weblog_accesses left join weblog_http_hosts # on http_host_id = weblog_http_hosts.id, # weblog_accesses left join weblog_http_user_agents # on http_user_agent_id = weblog_http_user_agent.id, # weblog_accesses left join weblog_ident_ostypes # on ident_ostype = weblog_ident_ostypes.id, # weblog_accesses left join weblog_ident_usernames # on ident_username = weblog_ident_usernames.id, # weblog_accesses left join weblog_remote_addrs # on remote_addr_id = weblog_remote_addrs.id, # weblog_accesses left join weblog_remote_hosts # on remote_host_id = weblog_remote_hosts.id # WHERE session_id = ; -- MiX -- For Radka... Najnavstevovanejsi den za mesiac. Najnavstevovanejsia hodina za mesiac. Najnavstevovanejsia rubrika. #Najnavstevovanejsi clanok za cely cas z Komentarov. + opposite