SELECT
	DATE_FORMAT(sl.dttm,'%Y-%m-%d') as the_date,
	#LPAD(FLOOR(CEIL(((HOUR(sl.dttm)*3600) + (MINUTE(sl.dttm)*60) + (SECOND(sl.dttm)))/1800)*(1800/60/60)),2,0) as the_hour,
	#LPAD(CEIL(time_to_sec(sl.dttm)/3600),2,0) as the_hour,
	DATE_FORMAT(sl.dttm,'%H') as the_hour,
  #CASE (CEIL(((MINUTE(sl.dttm)*60) + (SECOND(sl.dttm)))/3600)*60)
	CASE (FLOOR(((MINUTE(sl.dttm)*60) + (SECOND(sl.dttm)))/1800)*(1800/60)) WHEN 60 THEN '00' ELSE LPAD(round(FLOOR(((MINUTE(sl.dttm)*60) + (SECOND(sl.dttm)))/1800)*(1800/60)),2,0) END as the_minute,
  remote_host,
  region_description,
  ks.service_code,
  response_text,
  timeout,
  round(AVG((response_tm/100)),2) as ms
FROM
  accounts.key_uid ku
  LEFT JOIN accounts.membership_openid_url mou ON (ku.UID=mou.UID)
  LEFT JOIN accounts.membership_accounts ma ON (ku.UID=ma.UID)
  LEFT JOIN accounts.membership_services ms ON (ma.AID=ms.AID)
  LEFT JOIN accounts.key_services ks1 ON (ms.service_id=ks1.service_id)
  LEFT JOIN accounts.key_status_codes ksc ON (ms.status_id=ksc.status_id)
  LEFT JOIN accounts.uid_roles ur ON (ms.ms_id=ur.ms_id AND ma.ma_id=ur.ma_id)
  LEFT JOIN milliguard.config_services cs ON (ms.ms_id=cs.ms_id)
  LEFT JOIN milliguard.status_log sl ON (cs.remote_host_service_id=sl.remote_host_service_id)
  LEFT JOIN milliguard.membership_remote_hosts mrh ON (cs.remote_host_service_id=mrh.remote_host_service_id)
  LEFT JOIN milliguard.key_remote_hosts krm ON (mrh.remote_host_id=krm.remote_host_id)
  LEFT JOIN milliguard.key_regions kr1 ON (krm.region_id=kr1.region_id)
  LEFT JOIN milliguard.key_services ks ON (mrh.service_id=ks.service_id)
  LEFT JOIN milliguard.key_responses kr2 ON (sl.response_id=kr2.response_id)
WHERE
  ks1.service_code='MILLIGUARD'
  AND ksc.status_code='LIVE'
  AND (ku.username='intuit' OR mou.openid_url='intuit')
  AND cs.milliguard_joint_id=''
  AND sl.dttm>DATE_SUB(NOW(), INTERVAL 12 HOUR)
	#AND sl.dttm between '2008-04-13 1:00:00' AND '2008-04-14 23:00:00'
	AND response_tm/100 < timeout
	AND response_text='HTTP/1.1 200 OK'
GROUP BY
  1,2,3,4,5,6,7
ORDER BY
  1, 2, 3

SELECT
	DATE_FORMAT(sl.dttm,'%Y-%m-%d') as the_date,
	DATE_FORMAT(sl.dttm,'%H') as the_hour,
	CASE (FLOOR(((MINUTE(sl.dttm)*60) + (SECOND(sl.dttm)))/1800)*(1800/60)) WHEN 60 THEN '00' ELSE LPAD(round(FLOOR(((MINUTE(sl.dttm)*60) + (SECOND(sl.dttm)))/1800)*(1800/60)),2,0) END as the_minute,
  count(*) as the_count
FROM
  accounts.key_uid ku
  LEFT JOIN accounts.membership_openid_url mou ON (ku.UID=mou.UID)
  LEFT JOIN accounts.membership_accounts ma ON (ku.UID=ma.UID)
  LEFT JOIN accounts.membership_services ms ON (ma.AID=ms.AID)
  LEFT JOIN accounts.key_services ks1 ON (ms.service_id=ks1.service_id)
  LEFT JOIN accounts.key_status_codes ksc ON (ms.status_id=ksc.status_id)
  LEFT JOIN accounts.uid_roles ur ON (ms.ms_id=ur.ms_id AND ma.ma_id=ur.ma_id)
  LEFT JOIN milliguard.config_services cs ON (ms.ms_id=cs.ms_id)
  LEFT JOIN milliguard.status_log sl ON (cs.remote_host_service_id=sl.remote_host_service_id)
  LEFT JOIN milliguard.membership_remote_hosts mrh ON (cs.remote_host_service_id=mrh.remote_host_service_id)
  LEFT JOIN milliguard.key_remote_hosts krm ON (mrh.remote_host_id=krm.remote_host_id)
  LEFT JOIN milliguard.key_regions kr1 ON (krm.region_id=kr1.region_id)
  LEFT JOIN milliguard.key_services ks ON (mrh.service_id=ks.service_id)
  LEFT JOIN milliguard.key_responses kr2 ON (sl.response_id=kr2.response_id)
WHERE
  ks1.service_code='MILLIGUARD'
  AND ksc.status_code='LIVE'
  AND (ku.username='intuit' OR mou.openid_url='intuit')
  AND cs.milliguard_joint_id='' #8 =
	AND ks.service_code='HTTP'
  AND sl.dttm>DATE_SUB(NOW(), INTERVAL 24 HOUR)
  AND response_tm/100>=timeout
	AND response_text='HTTP/1.1 200 OK'
GROUP BY
  1,2,3
ORDER BY
  1, 2, 3

Chart.

response_time_query
SELECT
	DATE_FORMAT(sl.dttm,'%Y-%m-%d') as the_date,
	#LPAD(FLOOR(CEIL(((HOUR(sl.dttm)*3600) + (MINUTE(sl.dttm)*60) + (SECOND(sl.dttm)))/1800)*(1800/60/60)),2,0) as the_hour,
	#LPAD(CEIL(time_to_sec(sl.dttm)/3600),2,0) as the_hour,
	DATE_FORMAT(sl.dttm,'%H') as the_hour,
  #CASE (CEIL(((MINUTE(sl.dttm)*60) + (SECOND(sl.dttm)))/3600)*60)
	CASE (FLOOR(((MINUTE(sl.dttm)*60) + (SECOND(sl.dttm)))/1800)*(1800/60)) WHEN 60 THEN '00' ELSE LPAD(round(FLOOR(((MINUTE(sl.dttm)*60) + (SECOND(sl.dttm)))/1800)*(1800/60)),2,0) END as the_minute,
  remote_host,
  region_description,
  ks.service_code,
  response_text,
  timeout,
  round(AVG((response_tm/100)),2) as ms
FROM
  accounts.key_uid ku
  LEFT JOIN accounts.membership_openid_url mou ON (ku.UID=mou.UID)
  LEFT JOIN accounts.membership_accounts ma ON (ku.UID=ma.UID)
  LEFT JOIN accounts.membership_services ms ON (ma.AID=ms.AID)
  LEFT JOIN accounts.key_services ks1 ON (ms.service_id=ks1.service_id)
  LEFT JOIN accounts.key_status_codes ksc ON (ms.status_id=ksc.status_id)
  LEFT JOIN accounts.uid_roles ur ON (ms.ms_id=ur.ms_id AND ma.ma_id=ur.ma_id)
  LEFT JOIN milliguard.config_services cs ON (ms.ms_id=cs.ms_id)
  LEFT JOIN milliguard.status_log sl ON (cs.remote_host_service_id=sl.remote_host_service_id)
  LEFT JOIN milliguard.membership_remote_hosts mrh ON (cs.remote_host_service_id=mrh.remote_host_service_id)
  LEFT JOIN milliguard.key_remote_hosts krm ON (mrh.remote_host_id=krm.remote_host_id)
  LEFT JOIN milliguard.key_regions kr1 ON (krm.region_id=kr1.region_id)
  LEFT JOIN milliguard.key_services ks ON (mrh.service_id=ks.service_id)
  LEFT JOIN milliguard.key_responses kr2 ON (sl.response_id=kr2.response_id)
WHERE
  ks1.service_code='MILLIGUARD'
  AND ksc.status_code='LIVE'
  AND (ku.username='intuit' OR mou.openid_url='intuit')
  AND cs.milliguard_joint_id=''
  AND sl.dttm>DATE_SUB(NOW(), INTERVAL 12 HOUR)
	#AND sl.dttm between '2008-04-13 1:00:00' AND '2008-04-14 23:00:00'
	AND response_tm/100 < timeout
	AND response_text='HTTP/1.1 200 OK'
GROUP BY
  1,2,3,4,5,6,7
ORDER BY
  1, 2, 3


response_time_exceeded_query
SELECT
	DATE_FORMAT(sl.dttm,'%Y-%m-%d') as the_date,
	DATE_FORMAT(sl.dttm,'%H') as the_hour,
	CASE (FLOOR(((MINUTE(sl.dttm)*60) + (SECOND(sl.dttm)))/1800)*(1800/60)) WHEN 60 THEN '00' ELSE LPAD(round(FLOOR(((MINUTE(sl.dttm)*60) + (SECOND(sl.dttm)))/1800)*(1800/60)),2,0) END as the_minute,
  count(*) as the_count
FROM
  accounts.key_uid ku
  LEFT JOIN accounts.membership_openid_url mou ON (ku.UID=mou.UID)
  LEFT JOIN accounts.membership_accounts ma ON (ku.UID=ma.UID)
  LEFT JOIN accounts.membership_services ms ON (ma.AID=ms.AID)
  LEFT JOIN accounts.key_services ks1 ON (ms.service_id=ks1.service_id)
  LEFT JOIN accounts.key_status_codes ksc ON (ms.status_id=ksc.status_id)
  LEFT JOIN accounts.uid_roles ur ON (ms.ms_id=ur.ms_id AND ma.ma_id=ur.ma_id)
  LEFT JOIN milliguard.config_services cs ON (ms.ms_id=cs.ms_id)
  LEFT JOIN milliguard.status_log sl ON (cs.remote_host_service_id=sl.remote_host_service_id)
  LEFT JOIN milliguard.membership_remote_hosts mrh ON (cs.remote_host_service_id=mrh.remote_host_service_id)
  LEFT JOIN milliguard.key_remote_hosts krm ON (mrh.remote_host_id=krm.remote_host_id)
  LEFT JOIN milliguard.key_regions kr1 ON (krm.region_id=kr1.region_id)
  LEFT JOIN milliguard.key_services ks ON (mrh.service_id=ks.service_id)
  LEFT JOIN milliguard.key_responses kr2 ON (sl.response_id=kr2.response_id)
WHERE
  ks1.service_code='MILLIGUARD'
  AND ksc.status_code='LIVE'
  AND (ku.username='intuit' OR mou.openid_url='intuit')
  AND cs.milliguard_joint_id='' #8 =
	AND ks.service_code='HTTP'
  AND sl.dttm>DATE_SUB(NOW(), INTERVAL 24 HOUR)
  AND response_tm/100>=timeout
	AND response_text='HTTP/1.1 200 OK'
GROUP BY
  1,2,3
ORDER BY
  1, 2, 3


could_not_connect_to_host_query
SELECT
	DATE_FORMAT(sl.dttm,'%Y-%m-%d') as the_date,
	DATE_FORMAT(sl.dttm,'%H') as the_hour,
	CASE (FLOOR(((MINUTE(sl.dttm)*60) + (SECOND(sl.dttm)))/1800)*(1800/60)) WHEN 60 THEN '00' ELSE LPAD(round(FLOOR(((MINUTE(sl.dttm)*60) + (SECOND(sl.dttm)))/1800)*(1800/60)),2,0) END as the_minute,
  count(*) as the_count
FROM
  accounts.key_uid ku
  LEFT JOIN accounts.membership_openid_url mou ON (ku.UID=mou.UID)
  LEFT JOIN accounts.membership_accounts ma ON (ku.UID=ma.UID)
  LEFT JOIN accounts.membership_services ms ON (ma.AID=ms.AID)
  LEFT JOIN accounts.key_services ks1 ON (ms.service_id=ks1.service_id)
  LEFT JOIN accounts.key_status_codes ksc ON (ms.status_id=ksc.status_id)
  LEFT JOIN accounts.uid_roles ur ON (ms.ms_id=ur.ms_id AND ma.ma_id=ur.ma_id)
  LEFT JOIN milliguard.config_services cs ON (ms.ms_id=cs.ms_id)
  LEFT JOIN milliguard.status_log sl ON (cs.remote_host_service_id=sl.remote_host_service_id)
  LEFT JOIN milliguard.membership_remote_hosts mrh ON (cs.remote_host_service_id=mrh.remote_host_service_id)
  LEFT JOIN milliguard.key_remote_hosts krm ON (mrh.remote_host_id=krm.remote_host_id)
  LEFT JOIN milliguard.key_regions kr1 ON (krm.region_id=kr1.region_id)
  LEFT JOIN milliguard.key_services ks ON (mrh.service_id=ks.service_id)
  LEFT JOIN milliguard.key_responses kr2 ON (sl.response_id=kr2.response_id)
WHERE
  ks1.service_code='MILLIGUARD'
  AND ksc.status_code='LIVE'
  AND (ku.username='https://clickpass.com/public/gmillikan' OR mou.openid_url='https://clickpass.com/public/gmillikan')
  AND cs.milliguard_joint_id='' #8 =
	AND ks.service_code='HTTP'
  AND sl.dttm>DATE_SUB(NOW(), INTERVAL 24 HOUR)
  AND	response_text IN ('', 'ERROR')
GROUP BY
  1,2,3
ORDER BY
  1, 2, 3


unfavorable_host_response_query
SELECT
	DATE_FORMAT(sl.dttm,'%Y-%m-%d') as the_date,
	DATE_FORMAT(sl.dttm,'%H') as the_hour,
	CASE (FLOOR(((MINUTE(sl.dttm)*60) + (SECOND(sl.dttm)))/1800)*(1800/60)) WHEN 60 THEN '00' ELSE LPAD(round(FLOOR(((MINUTE(sl.dttm)*60) + (SECOND(sl.dttm)))/1800)*(1800/60)),2,0) END as the_minute,
  count(*) as the_count
FROM
  accounts.key_uid ku
  LEFT JOIN accounts.membership_openid_url mou ON (ku.UID=mou.UID)
  LEFT JOIN accounts.membership_accounts ma ON (ku.UID=ma.UID)
  LEFT JOIN accounts.membership_services ms ON (ma.AID=ms.AID)
  LEFT JOIN accounts.key_services ks1 ON (ms.service_id=ks1.service_id)
  LEFT JOIN accounts.key_status_codes ksc ON (ms.status_id=ksc.status_id)
  LEFT JOIN accounts.uid_roles ur ON (ms.ms_id=ur.ms_id AND ma.ma_id=ur.ma_id)
  LEFT JOIN milliguard.config_services cs ON (ms.ms_id=cs.ms_id)
  LEFT JOIN milliguard.status_log sl ON (cs.remote_host_service_id=sl.remote_host_service_id)
  LEFT JOIN milliguard.membership_remote_hosts mrh ON (cs.remote_host_service_id=mrh.remote_host_service_id)
  LEFT JOIN milliguard.key_remote_hosts krm ON (mrh.remote_host_id=krm.remote_host_id)
  LEFT JOIN milliguard.key_regions kr1 ON (krm.region_id=kr1.region_id)
  LEFT JOIN milliguard.key_services ks ON (mrh.service_id=ks.service_id)
  LEFT JOIN milliguard.key_responses kr2 ON (sl.response_id=kr2.response_id)
WHERE
  ks1.service_code='MILLIGUARD'
  AND ksc.status_code='LIVE'
  AND (ku.username='https://clickpass.com/public/gmillikan' OR mou.openid_url='https://clickpass.com/public/gmillikan')
  AND cs.milliguard_joint_id='' #8 =
	AND ks.service_code='HTTP'
  AND sl.dttm>DATE_SUB(NOW(), INTERVAL 24 HOUR)
  AND	response_text NOT IN ('', 'ERROR', 'HTTP/1.1 200 OK')
GROUP BY
  1,2,3
ORDER BY
  1, 2, 3