DELETE FROM `idtemp_dbo`.`hits`
WHERE csUriStem='/umbraco/ping.aspx'
OR csUriStem LIKE '%.gif'
OR csUriStem LIKE '%.png'
OR csUriStem LIKE '%.ico'
OR csUriStem LIKE '%.jpg'
OR csUriStem LIKE '%.js'
OR csUriStem LIKE '%.css'
OR csUriStem LIKE '%.axd'
OR csUriStem LIKE '%.swf'
OR csUriStem LIKE '/otsintranet%'
OR csUriStem='/robots.txt'
OR csUriStem LIKE '/umbraco/%'
OR csUriStem='/Captchaimage.aspx'
OR csUserAgent LIKE '%msnbot%'
OR csUserAgent LIKE '%Googlebot%'
OR csUserAgent LIKE '%Slurp%'
OR LOWER(csUserAgent) LIKE '%bot%'
OR LOWER(csUserAgent) LIKE '%crawler%'
OR scStatus != '200'

UPDATE hits SET `dateTime` = cast(concat(DATE(`date`), ' ', TIME(`time`)) AS DATETIME)

SELECT *, TIMEDIFF(`dateTime`, NXT) FROM
(
SELECT h1.`dateTime`, h1.cIp, h1.csUriStem, h1.csUriQuery,
(SELECT h2.`dateTime` FROM hits AS h2 WHERE h2.`dateTime` < h1.`dateTime` AND h2.cIp = h1.cIp ORDER BY h2.`dateTime` DESC LIMIT 1) AS NXT
FROM hits AS h1
ORDER BY h1.cIp
LIMIT 50
) AS A


--SELECT DISTINCT csUserAgent FROM `idtemp_dbo`.`hits`
--WHERE csUserAgent NOT LIKE 'Mozilla%' ORDER BY `date`

SELECT DISTINCT csUserAgent FROM `idtemp_dbo`.`hits`
WHERE csUserAgent LIKE '%bot%'