Below is a query I'm trying to create and the sql for the table I'm pulling the
information out of. The query is definitely not the best query out there
especially since I'm still pretty new with sql. I know there has to be a
better way of getting the information I want. A little background. I'm
parsing an Apache access_log file and throwing it all into a DB so I can run
some reports on it. What I'm wanting to get are certain file types that were
downloaded (in this case all .html, .php, .pdf, .doc, and .flv files) in a
certain date range but grouped by certain ip addresses. Some groups of ips
might have 2 or 3 subnets it needs to get (ie xxx.xxx.% and yyy.yyy.%). What
needs to be returned is the count of all the file types that have been
downloaded but grouped by certain ips that I'm looking for.
I hope I didn't confuse anything because I think I confused myself! ;)
Thanks for any help!
CREATE TABLE `apache_statslog` (
`STATS_ID` int(11) NOT NULL auto_increment,
`ip` varchar(25) default NULL,
`accesstime` datetime default NULL,
`thepage` varchar(250) default NULL,
`thetype` varchar(25) default NULL,
`thecode` char(3) default NULL,
`thebytes` int(11) default NULL,
`theref` varchar(250) default NULL,
`browser` varchar(250) default NULL,
PRIMARY KEY (`STATS_ID`),
KEY `ip` (`ip`),
KEY `accesstime` (`accesstime`),
KEY `thepage` (`thepage`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=59145 ;
SELECT count(swrm.ip) as swrm_page_hits, count(mw.ip) as mw_page_hits,
count(fl.ip) as fl_page_hits, count(so.ip) as so_page_hits
FROM apache_statslog as swrm, apache_statslog as mw, apache_statslog as fl,
apache_statslog as so
WHERE (swrm.accesstime >= '2006-01-01 00:00:00' AND swrm.accesstime <=
'2006-01-04 23:59:59')
AND (swrm.ip LIKE 'xxx.xxx.%' OR swrm.ip LIKE 'xxx.xxx.%' OR swrm.ip
LIKE 'xxx.xxx.%') #this is a group that needs to return a count
AND (mw.ip LIKE 'xxx.xxx.%' OR mw.ip LIKE 'xxx.xxx.%') #this is a
group that needs to return a count
AND (fl.ip LIKE 'xxx.xxx.%' OR fl.ip LIKE 'xxx.xxx.%') #this is a
group that needs to return a count
AND (so.ip LIKE 'xxx.xxx.%' OR so.ip LIKE 'xxx.xxx.%') #this is a
group that needs to return a count
AND (swrm.thepage LIKE '%.html%' OR swrm.thepage LIKE '%.php%' OR
swrm.thepage LIKE '%.doc%' OR swrm.thepage LIKE '%.pdf%' OR swrm.thepage LIKE
'%.flv%')
ORDER BY swrm.accesstime ASC
