analyze IIS log errors by ip address

ipcheck.sql

----select * from a


------total requests by IP
----select f9, count(*) from a
----group by f9
----order by 2 desc


	--select 
	--	outerA.subnet as 'subnet',
	--	count(*) as 'count',
	--	cast(avg(F13) as int)
	--from 
	--	(
	--	select
	--	 REVERSE(SUBSTRING(reverse(lastoctet), CHARINDEX('.', reverse(lastoctet))+1, 100)) as 'subnet',
	--	 F13
	--	from
	--		(
	--		select
	--		 REVERSE(SUBSTRING(reverse(F9), CHARINDEX('.', reverse(F9))+1, 100)) as 'lastoctet',
	--		 F13
	--		from a
	--		where a.F11 like 500
	--		) innerA
	--	) outerA
	--group by outerA.subnet
	--order by 2 desc 



select 
 total_requests.subnet as 'subnet',
 total_requests.count as 'total',
 error_requests.count as 'error',
 total_requests.timewaited as 'total_time_waited',
 error_requests.timewaited as 'error_time_waited',
 error_requests.F10
from 
	(
	select 
	 outerA.subnet,
	 count(*) as 'count',
	 cast(sum(F13) as int) as 'timewaited',
	 F10
	from 
		(
		select
		 F9 as 'subnet',
		 F13,
		 F10
		from a
		--where F10 like '%iPhone%'
		) outerA
	group by outerA.subnet, F10
	) total_requests
full JOIN
	(
	select 
	 outerA.subnet, 
	 count(*) as 'count',
	 cast(sum(F13) as int) as 'timewaited',
	 F10
	from 
		(
		select
		 F9 as 'subnet',
		 F13,
		 F10
		from a
		where F11 like 500
		--and F10 like '%iPhone%'
		) outerA
	group by outerA.subnet, F10
	) error_requests
ON error_requests.subnet = total_requests.subnet
order by 5 desc



select 
	a.F5,
	a.count as 'total',
	b.count as 'error'
from 
	(
	select 
	F5, 
	count(*) as 'count'
	from a
	group by F5
	) a
JOIN
	(
	select 
	F5, 
	count(*) as 'count'
	from a
	where F11 like 500
	group by F5
	) b
on a.F5 = b.F5
order by (a.count/b.count)

ip errors by ip address. assumes that there is a table that's based on an IIS log file. need cleanup