select
tstamp::text,
hmap(hostname),
sum(l1count) as l1total,
100-pct(sum(l1success),sum(l1count)) as l1failspct,
avg0(sum(l1resp),sum(l1success)) as l1avg,
avg0(sum(l2resp),sum(l2success)) as l2avg,
avg0(sum(l3resp),sum(l3success)) as l3avg,
(avg0(sum(l1resp),sum(l1success)) - avg0(sum(l2resp),(sum(l2success)))) as l12avgdiff,
(avg0(sum(l2resp),sum(l2success)) - avg0(sum(l3resp),(sum(l3success)))) as l23avgdiff
from (
select
date_trunc('minute',brsa.lastflushtime) as tstamp,
brsa.hostname,
brsa.businessrequestsummaryid as l1id,
brsb.businessrequestsummaryid as l2id,
brsc.businessrequestsummaryid as l3id,
brsa.category||','||brsa.perspective||','||brsa.request as l1req,
brsb.category||','||brsb.perspective||','||brsb.request as l2req,
brsc.category||','||brsc.perspective||','||brsc.request as l3req,
brsa.totalresponsetime as l1resp,
brsa.totalcount as l1count,
brsa.successcount as l1success,
brsb.successcount as l2success,
brsa.highestresponsetime as l1max,
brsb.totalresponsetime as l2resp,
brsb.totalcount as l2count,
brsb.highestresponsetime as l2max,
brsc.totalresponsetime as l3resp,
brsc.totalcount as l3count,
brsc.highestresponsetime as l3max,
brsc.successcount as l3success
from businessrequestsummary brsa
left join businessrequestsummary brsb
on (brsa.businessrequestsummaryid=brsb.primaryrequestid)
left join businessrequestsummary brsc
on (brsb.businessrequestsummaryid=brsc.primaryrequestid)
where brsa.category='Webservice' and
brsa.lastflushtime between '2007-11-27 13:15:03' and '2007-11-27 13:45:03'
and brsa.request='lowFareSearch' and
brsa.perspective = 'LowFareSearchReq' -- and
) as a
group by a.tstamp , hostname
union all
select
'Rollup',
hmap(hostname),
sum(l1count) as l1total,
100-pct(sum(l1success),sum(l1count)) as l1failspct,
avg0(sum(l1resp),sum(l1success)) as l1avg,
avg0(sum(l2resp),sum(l2success)) as l2avg,
avg0(sum(l3resp),sum(l3success)) as l3avg,
(avg0(sum(l1resp),sum(l1success)) - avg0(sum(l2resp),(sum(l2success)))) as l12avgdiff,
(avg0(sum(l2resp),sum(l2success)) - avg0(sum(l3resp),(sum(l3success)))) as l23avgdiff
from (
select
date_trunc('min',brsa.lastflushtime) as tstamp,
brsa.hostname,
brsa.businessrequestsummaryid as l1id,
brsb.businessrequestsummaryid as l2id,
brsc.businessrequestsummaryid as l3id,
brsa.category||','||brsa.perspective||','||brsa.request as l1req,
brsb.category||','||brsb.perspective||','||brsb.request as l2req,
brsc.category||','||brsc.perspective||','||brsc.request as l3req,
brsa.totalresponsetime as l1resp,
brsa.totalcount as l1count,
brsa.successcount as l1success,
brsb.successcount as l2success,
brsa.highestresponsetime as l1max,
brsb.totalresponsetime as l2resp,
brsb.totalcount as l2count,
brsb.highestresponsetime as l2max,
brsc.totalresponsetime as l3resp,
brsc.totalcount as l3count,
brsc.highestresponsetime as l3max,
brsc.successcount as l3success
from businessrequestsummary brsa
left join businessrequestsummary brsb
on (brsa.businessrequestsummaryid=brsb.primaryrequestid)
left join businessrequestsummary brsc
on (brsb.businessrequestsummaryid=brsc.primaryrequestid)
where brsa.category='Webservice' and
brsa.lastflushtime between '2007-11-27 13:15:03' and '2007-11-27 13:45:03'
and brsa.request='lowFareSearch' and
brsa.perspective = 'LowFareSearchReq' -- and
) as b
group by b.hostname
union all
select
'Std Dev:',
hmap(hostname),
stddev(l1total)::numeric(12,2) as l1totstddev,
stddev(l1failspct)::numeric(12,2) as l1failspctstddev,
stddev(l1avg)::numeric(12,2) as l1stddev,
stddev(l2avg)::numeric(12,2) as l2stddev,
stddev(l3avg)::numeric(12,2) as l3stddev,
stddev(l12avgdiff)::numeric(12,2) as l12avgstddev,
stddev(l23avgdiff)::numeric(12,2) as l23avgstddev
from (
select
-- tstamp::text,
hmap(c.hostname) as hostname,
sum(l1count) as l1total,
100-pct(sum(l1success),sum(l1count)) as l1failspct,
avg0(sum(l1resp),sum(l1success)) as l1avg,
avg0(sum(l2resp),sum(l2success)) as l2avg,
avg0(sum(l3resp),sum(l3success)) as l3avg,
(avg0(sum(l1resp),sum(l1success)) - avg0(sum(l2resp),(sum(l2success)))) as l12avgdiff,
(avg0(sum(l2resp),sum(l2success)) - avg0(sum(l3resp),(sum(l3success)))) as l23avgdiff
from (
select
date_trunc('minute',brsa.lastflushtime) as tstamp,
brsa.hostname,
brsa.businessrequestsummaryid as l1id,
brsb.businessrequestsummaryid as l2id,
brsc.businessrequestsummaryid as l3id,
brsa.category||','||brsa.perspective||','||brsa.request as l1req,
brsb.category||','||brsb.perspective||','||brsb.request as l2req,
brsc.category||','||brsc.perspective||','||brsc.request as l3req,
brsa.totalresponsetime as l1resp,
brsa.totalcount as l1count,
brsa.successcount as l1success,
brsb.successcount as l2success,
brsa.highestresponsetime as l1max,
brsb.totalresponsetime as l2resp,
brsb.totalcount as l2count,
brsb.highestresponsetime as l2max,
brsc.totalresponsetime as l3resp,
brsc.totalcount as l3count,
brsc.highestresponsetime as l3max,
brsc.successcount as l3success
from businessrequestsummary brsa
left join businessrequestsummary brsb
on (brsa.businessrequestsummaryid=brsb.primaryrequestid)
left join businessrequestsummary brsc
on (brsb.businessrequestsummaryid=brsc.primaryrequestid)
where brsa.category='Webservice' and
brsa.lastflushtime between '2007-11-27 09:14' and '2007-11-27 09:25:40'
and brsa.request='lowFareSearch' and
brsa.perspective = 'LowFareSearchReq' -- and
) as c
group by c.tstamp, hostname
) as d
group by hostname
order by 2, tstamp asc, l1total