func (a appMetric) loadAppMetrics(applicationID, resolution string, typeID internal.ID, timeRange []time.Time, p *ts.Plot) *weft.Result {
var err error
var rows *sql.Rows
rows, err = dbR.Query(`SELECT COUNT(*)
FROM app.type, app.application
WHERE applicationID = $1
AND typePK = $2`, applicationID, int(typeID))
defer rows.Close()
var nRows int
for rows.Next() {
if err = rows.Scan(&nRows); err != nil {
return weft.InternalServerError(err)
}
}
// missing applicationID or typePK should return a 404 while no data can be 200. See GeoNet/mtr#214
if nRows == 0 {
return &weft.NotFound
}
rows.Close()
switch resolution {
case "minute":
rows, err = dbR.Query(`SELECT instancePK, typePK, date_trunc('`+resolution+`',time) as t, avg(value)
FROM app.metric
WHERE applicationPK = (SELECT applicationPK from app.application WHERE applicationID = $1)
AND typePK = $2
AND time >= $3 AND time <= $4
GROUP BY date_trunc('`+resolution+`',time), typePK, instancePK
ORDER BY t ASC`, applicationID, int(typeID), timeRange[0], timeRange[1])
case "five_minutes":
rows, err = dbR.Query(`SELECT instancePK, typePK,
date_trunc('hour', time) + extract(minute from time)::int / 5 * interval '5 min' as t, avg(value)
FROM app.metric
WHERE applicationPK = (SELECT applicationPK from app.application WHERE applicationID = $1)
AND typePK = $2
AND time >= $3 AND time <= $4
GROUP BY date_trunc('hour', time) + extract(minute from time)::int / 5 * interval '5 min', typePK, instancePK
ORDER BY t ASC`, applicationID, int(typeID), timeRange[0], timeRange[1])
case "hour":
rows, err = dbR.Query(`SELECT instancePK, typePK, date_trunc('`+resolution+`',time) as t, avg(value)
FROM app.metric
WHERE applicationPK = (SELECT applicationPK from app.application WHERE applicationID = $1)
AND typePK = $2
AND time >= $3 AND time <= $4
GROUP BY date_trunc('`+resolution+`',time), typePK, instancePK
ORDER BY t ASC`, applicationID, int(typeID), timeRange[0], timeRange[1])
case "full":
rows, err = dbR.Query(`SELECT instancePK, typePK, time as t, value
FROM app.metric
WHERE applicationPK = (SELECT applicationPK from app.application WHERE applicationID = $1)
AND typePK = $2
AND time >= $3 AND time <= $4
ORDER BY time ASC`, applicationID, int(typeID), timeRange[0], timeRange[1])
default:
return weft.InternalServerError(fmt.Errorf("invalid resolution: %s", resolution))
}
if err != nil {
return weft.InternalServerError(err)
}
defer rows.Close()
var t time.Time
var typePK, instancePK int
var avg float64
var instanceID string
pts := make(map[InstanceMetric][]ts.Point)
for rows.Next() {
if err = rows.Scan(&instancePK, &typePK, &t, &avg); err != nil {
return weft.InternalServerError(err)
}
key := InstanceMetric{instancePK: instancePK, typePK: typePK}
pts[key] = append(pts[key], ts.Point{DateTime: t, Value: avg})
}
rows.Close()
instanceIDs := make(map[int]string)
if rows, err = dbR.Query(`SELECT instancePK, instanceID FROM app.instance`); err != nil {
return weft.InternalServerError(err)
}
defer rows.Close()
for rows.Next() {
if err = rows.Scan(&instancePK, &instanceID); err != nil {
return weft.InternalServerError(err)
}
instanceIDs[instancePK] = instanceID
}
rows.Close()
var keys InstanceMetrics
for k := range pts {
keys = append(keys, k)
}
sort.Sort(keys)
var labels ts.Labels
for i, k := range keys {
if i > len(colours) {
i = 0
}
c := colours[i]
p.AddSeries(ts.Series{Colour: c, Points: pts[k]})
labels = append(labels, ts.Label{Colour: c, Label: fmt.Sprintf("%s.%s", instanceIDs[k.instancePK], internal.Label(k.typePK))})
}
p.SetLabels(labels)
return &weft.StatusOK
}
func (a appMetric) loadMemory(applicationID, resolution string, timeRange []time.Time, p *ts.Plot) *weft.Result {
var err error
var rows *sql.Rows
switch resolution {
case "minute":
rows, err = dbR.Query(`SELECT instancePK, typePK, date_trunc('`+resolution+`',time) as t, avg(value)
FROM app.metric
WHERE applicationPK = (SELECT applicationPK from app.application WHERE applicationID = $1)
AND typePK IN (1000, 1001, 1002)
AND time >= $2 AND time <= $3
GROUP BY date_trunc('`+resolution+`',time), typePK, instancePK
ORDER BY t ASC`, applicationID, timeRange[0], timeRange[1])
case "five_minutes":
rows, err = dbR.Query(`SELECT instancePK, typePK,
date_trunc('hour', time) + extract(minute from time)::int / 5 * interval '5 min' as t, avg(value)
FROM app.metric
WHERE applicationPK = (SELECT applicationPK from app.application WHERE applicationID = $1)
AND typePK IN (1000, 1001, 1002)
AND time >= $2 AND time <= $3
GROUP BY date_trunc('hour', time) + extract(minute from time)::int / 5 * interval '5 min', typePK, instancePK
ORDER BY t ASC`, applicationID, timeRange[0], timeRange[1])
case "hour":
rows, err = dbR.Query(`SELECT instancePK, typePK, date_trunc('`+resolution+`',time) as t, avg(value)
FROM app.metric
WHERE applicationPK = (SELECT applicationPK from app.application WHERE applicationID = $1)
AND typePK IN (1000, 1001, 1002)
AND time >= $2 AND time <= $3
GROUP BY date_trunc('`+resolution+`',time), typePK, instancePK
ORDER BY t ASC`, applicationID, timeRange[0], timeRange[1])
case "full":
rows, err = dbR.Query(`SELECT instancePK, typePK, time, value
FROM app.metric
WHERE applicationPK = (SELECT applicationPK from app.application WHERE applicationID = $1)
AND typePK IN (1000, 1001, 1002)
AND time >= $2 AND time <= $3
ORDER BY time ASC`, applicationID, timeRange[0], timeRange[1])
default:
return weft.InternalServerError(fmt.Errorf("invalid resolution: %s", resolution))
}
if err != nil {
return weft.InternalServerError(err)
}
defer rows.Close()
var t time.Time
var typePK, instancePK int
var avg float64
var instanceID string
pts := make(map[InstanceMetric][]ts.Point)
for rows.Next() {
if err = rows.Scan(&instancePK, &typePK, &t, &avg); err != nil {
fmt.Println(err)
return weft.InternalServerError(err)
}
key := InstanceMetric{instancePK: instancePK, typePK: typePK}
pts[key] = append(pts[key], ts.Point{DateTime: t, Value: avg})
}
rows.Close()
instanceIDs := make(map[int]string)
if rows, err = dbR.Query(`SELECT instancePK, instanceID FROM app.instance`); err != nil {
return weft.InternalServerError(err)
}
defer rows.Close()
for rows.Next() {
if err = rows.Scan(&instancePK, &instanceID); err != nil {
return weft.InternalServerError(err)
}
instanceIDs[instancePK] = instanceID
}
rows.Close()
var labels ts.Labels
for k := range pts {
p.AddSeries(ts.Series{Colour: internal.Colour(k.typePK), Points: pts[k]})
labels = append(labels, ts.Label{Colour: internal.Colour(k.typePK), Label: fmt.Sprintf("%s.%s", instanceIDs[k.instancePK], strings.TrimPrefix(internal.Label(k.typePK), `Mem `))})
}
p.SetLabels(labels)
return &weft.StatusOK
}
func (a appMetric) loadTimersWithSourceID(applicationID, sourceID, resolution string, timeRange []time.Time, p *ts.Plot) *weft.Result {
var err error
var rows *sql.Rows
switch resolution {
case "minute":
rows, err = dbR.Query(`SELECT date_trunc('minute',time) as t, avg(average), max(fifty), max(ninety), sum(count)
FROM app.timer
WHERE applicationPK = (SELECT applicationPK from app.application WHERE applicationID = $1)
AND sourcePK = (SELECT sourcePK from app.source WHERE sourceID = $2)
AND time >= $3 AND time <= $4
GROUP BY date_trunc('minute',time)
ORDER BY t ASC`, applicationID, sourceID, timeRange[0], timeRange[1])
case "five_minutes":
rows, err = dbR.Query(`SELECT
date_trunc('hour', time) + extract(minute from time)::int / 5 * interval '5 min' as t,
avg(average), max(fifty), max(ninety), sum(count)
FROM app.timer
WHERE applicationPK = (SELECT applicationPK from app.application WHERE applicationID = $1)
AND sourcePK = (SELECT sourcePK from app.source WHERE sourceID = $2)
AND time >= $3 AND time <= $4
GROUP BY date_trunc('hour', time) + extract(minute from time)::int / 5 * interval '5 min'
ORDER BY t ASC`, applicationID, sourceID, timeRange[0], timeRange[1])
case "hour":
rows, err = dbR.Query(`SELECT date_trunc('hour',time) as t, avg(average), max(fifty), max(ninety), sum(count)
FROM app.timer
WHERE applicationPK = (SELECT applicationPK from app.application WHERE applicationID = $1)
AND sourcePK = (SELECT sourcePK from app.source WHERE sourceID = $2)
AND time >= $3 AND time <= $4
GROUP BY date_trunc('hour', time)
ORDER BY t ASC`, applicationID, sourceID, timeRange[0], timeRange[1])
case "full":
rows, err = dbR.Query(`SELECT time, average, fifty, ninety, count
FROM app.timer
WHERE applicationPK = (SELECT applicationPK from app.application WHERE applicationID = $1)
AND sourcePK = (SELECT sourcePK from app.source WHERE sourceID = $2)
AND time >= $3 AND time <= $4
ORDER BY time ASC`, applicationID, sourceID, timeRange[0], timeRange[1])
default:
return weft.InternalServerError(fmt.Errorf("invalid resolution: %s", resolution))
}
if err != nil {
return weft.InternalServerError(err)
}
defer rows.Close()
var t time.Time
var avg_mean float64
var max_fifty, max_ninety, n int
pts := make(map[internal.ID][]ts.Point)
for rows.Next() {
if err = rows.Scan(&t, &avg_mean, &max_fifty, &max_ninety, &n); err != nil {
return weft.InternalServerError(err)
}
pts[internal.AvgMean] = append(pts[internal.AvgMean], ts.Point{DateTime: t, Value: avg_mean})
pts[internal.MaxFifty] = append(pts[internal.MaxFifty], ts.Point{DateTime: t, Value: float64(max_fifty)})
pts[internal.MaxNinety] = append(pts[internal.MaxNinety], ts.Point{DateTime: t, Value: float64(max_ninety)})
}
rows.Close()
var labels ts.Labels
for k, v := range pts {
i := int(k)
p.AddSeries(ts.Series{Points: v, Colour: internal.Colour(i)})
labels = append(labels, ts.Label{Label: fmt.Sprintf("%s (n=%d)", strings.TrimPrefix(internal.Label(i), `main.`), len(v)), Colour: internal.Colour(i)})
}
p.SetLabels(labels)
return &weft.StatusOK
}
func (a appMetric) loadTimers(applicationID, resolution string, timeRange []time.Time, p *ts.Plot) *weft.Result {
var err error
var rows *sql.Rows
switch resolution {
case "minute":
rows, err = dbR.Query(`SELECT sourcePK, date_trunc('`+resolution+`',time) as t, max(ninety), sum(count)
FROM app.timer
WHERE applicationPK = (SELECT applicationPK from app.application WHERE applicationID = $1)
AND time >= $2 AND time <= $3
GROUP BY date_trunc('`+resolution+`',time), sourcePK
ORDER BY t ASC`, applicationID, timeRange[0], timeRange[1])
case "five_minutes":
rows, err = dbR.Query(`SELECT sourcePK,
date_trunc('hour', time) + extract(minute from time)::int / 5 * interval '5 min' as t,
max(ninety), sum(count)
FROM app.timer
WHERE applicationPK = (SELECT applicationPK from app.application WHERE applicationID = $1)
AND time >= $2 AND time <= $3
GROUP BY date_trunc('hour', time) + extract(minute from time)::int / 5 * interval '5 min', sourcePK
ORDER BY t ASC`, applicationID, timeRange[0], timeRange[1])
case "hour":
rows, err = dbR.Query(`SELECT sourcePK, date_trunc('`+resolution+`',time) as t, max(ninety), sum(count)
FROM app.timer
WHERE applicationPK = (SELECT applicationPK from app.application WHERE applicationID = $1)
AND time >= $2 AND time <= $3
GROUP BY date_trunc('`+resolution+`',time), sourcePK
ORDER BY t ASC`, applicationID, timeRange[0], timeRange[1])
case "full":
rows, err = dbR.Query(`SELECT sourcePK, time, ninety, count
FROM app.timer
WHERE applicationPK = (SELECT applicationPK from app.application WHERE applicationID = $1)
AND time >= $2 AND time <= $3
ORDER BY time ASC`, applicationID, timeRange[0], timeRange[1])
default:
return weft.InternalServerError(fmt.Errorf("invalid resolution: %s", resolution))
}
if err != nil {
return weft.InternalServerError(err)
}
defer rows.Close()
var t time.Time
var sourcePK, max, n int
var sourceID string
pts := make(map[int][]ts.Point)
total := make(map[int]int) // track the total counts (call) for each timer.
for rows.Next() {
if err = rows.Scan(&sourcePK, &t, &max, &n); err != nil {
return weft.InternalServerError(err)
}
pts[sourcePK] = append(pts[sourcePK], ts.Point{DateTime: t, Value: float64(max)})
total[sourcePK] += n
}
rows.Close()
sourceIDs := make(map[int]string)
if rows, err = dbR.Query(`SELECT sourcePK, sourceID FROM app.source`); err != nil {
return weft.InternalServerError(err)
}
defer rows.Close()
for rows.Next() {
if err = rows.Scan(&sourcePK, &sourceID); err != nil {
return weft.InternalServerError(err)
}
sourceIDs[sourcePK] = sourceID
}
rows.Close()
// sort the sourcePKs based on number of calls.
keys := rank(total)
var labels ts.Labels
for _, k := range keys {
p.AddSeries(ts.Series{Points: pts[k.Key], Colour: "#e34a33"})
labels = append(labels, ts.Label{Label: fmt.Sprintf("%s (n=%d)", strings.TrimPrefix(sourceIDs[k.Key], `main.`), total[k.Key]), Colour: "lightgrey"})
}
p.SetLabels(labels)
return &weft.StatusOK
}
func (a appMetric) loadCounters(applicationID, resolution string, timeRange []time.Time, p *ts.Plot) *weft.Result {
var err error
var rows *sql.Rows
switch resolution {
case "minute":
rows, err = dbR.Query(`SELECT typePK, date_trunc('`+resolution+`',time) as t, sum(count)
FROM app.counter
WHERE applicationPK = (SELECT applicationPK from app.application WHERE applicationID = $1)
AND time >= $2 AND time <= $3
GROUP BY date_trunc('`+resolution+`',time), typePK
ORDER BY t ASC`, applicationID, timeRange[0], timeRange[1])
case "five_minutes":
rows, err = dbR.Query(`SELECT typePK,
date_trunc('hour', time) + extract(minute from time)::int / 5 * interval '5 min' as t, sum(count)
FROM app.counter
WHERE applicationPK = (SELECT applicationPK from app.application WHERE applicationID = $1)
AND time >= $2 AND time <= $3
GROUP BY date_trunc('hour', time) + extract(minute from time)::int / 5 * interval '5 min', typePK
ORDER BY t ASC`, applicationID, timeRange[0], timeRange[1])
case "hour":
rows, err = dbR.Query(`SELECT typePK, date_trunc('`+resolution+`',time) as t, sum(count)
FROM app.counter
WHERE applicationPK = (SELECT applicationPK from app.application WHERE applicationID = $1)
AND time >= $2 AND time <= $3
GROUP BY date_trunc('`+resolution+`',time), typePK
ORDER BY t ASC`, applicationID, timeRange[0], timeRange[1])
case "full":
rows, err = dbR.Query(`SELECT typePK, time, count
FROM app.counter
JOIN app.type USING (typepk)
WHERE applicationPK = (SELECT applicationPK from app.application WHERE applicationID = $1)
AND time >= $2 AND time <= $3
ORDER BY time ASC`, applicationID, timeRange[0], timeRange[1])
default:
return weft.InternalServerError(fmt.Errorf("invalid resolution: %s", resolution))
}
if err != nil {
return weft.InternalServerError(err)
}
defer rows.Close()
var t time.Time
var typePK, count int
pts := make(map[int][]ts.Point)
total := make(map[int]int)
for rows.Next() {
if err = rows.Scan(&typePK, &t, &count); err != nil {
return weft.InternalServerError(err)
}
pts[typePK] = append(pts[typePK], ts.Point{DateTime: t, Value: float64(count)})
total[typePK] += count
}
rows.Close()
var keys []int
for k := range pts {
keys = append(keys, k)
}
sort.Ints(keys)
var labels ts.Labels
for _, k := range keys {
p.AddSeries(ts.Series{Colour: internal.Colour(k), Points: pts[k]})
labels = append(labels, ts.Label{Colour: internal.Colour(k), Label: fmt.Sprintf("%s (n=%d)", internal.Label(k), total[k])})
}
p.SetLabels(labels)
return &weft.StatusOK
}
func dataLatencyPlot(siteID, typeID, resolution string, plotter ts.SVGPlot, b *bytes.Buffer) *weft.Result {
var err error
// we need the sitePK often so read it once.
var sitePK int
if err = dbR.QueryRow(`SELECT sitePK FROM data.site WHERE siteID = $1`,
siteID).Scan(&sitePK); err != nil {
if err == sql.ErrNoRows {
return &weft.NotFound
}
return weft.InternalServerError(err)
}
var typePK int
var scale float64
var display string
if err = dbR.QueryRow(`SELECT typePK, scale, display FROM data.type WHERE typeID = $1`,
typeID).Scan(&typePK, &scale, &display); err != nil {
if err == sql.ErrNoRows {
return &weft.NotFound
}
return weft.InternalServerError(err)
}
var p ts.Plot
p.SetUnit(display)
var lower, upper int
if err := dbR.QueryRow(`SELECT lower,upper FROM data.latency_threshold
WHERE sitePK = $1 AND typePK = $2`,
sitePK, typePK).Scan(&lower, &upper); err != nil && err != sql.ErrNoRows {
return weft.InternalServerError(err)
}
if !(lower == 0 && upper == 0) {
p.SetThreshold(float64(lower)*scale, float64(upper)*scale)
}
var tags []string
var rows *sql.Rows
if rows, err = dbR.Query(`SELECT tag FROM data.latency_tag JOIN mtr.tag USING (tagpk) WHERE
sitePK = $1 AND typePK = $2
ORDER BY tag asc`,
sitePK, typePK); err != nil {
return weft.InternalServerError(err)
}
defer rows.Close()
for rows.Next() {
var s string
if err = rows.Scan(&s); err != nil {
return weft.InternalServerError(err)
}
tags = append(tags, s)
}
rows.Close()
p.SetSubTitle("Tags: " + strings.Join(tags, ","))
p.SetTitle(fmt.Sprintf("Site: %s - %s", siteID, strings.Title(typeID)))
// TODO - loading avg(mean) at each resolution. Need to add max(fifty) and max(ninety) when there are some values.
switch resolution {
case "minute":
p.SetXAxis(time.Now().UTC().Add(time.Hour*-12), time.Now().UTC())
p.SetXLabel("12 hours")
case "five_minutes":
p.SetXAxis(time.Now().UTC().Add(time.Hour*-24*2), time.Now().UTC())
p.SetXLabel("48 hours")
case "hour":
p.SetXAxis(time.Now().UTC().Add(time.Hour*-24*28), time.Now().UTC())
p.SetXLabel("4 weeks")
default:
return weft.BadRequest("invalid resolution")
}
if err != nil {
return weft.InternalServerError(err)
}
var timeRange []time.Time
if timeRange, err = defaultTimeRange(resolution); err != nil {
weft.InternalServerError(err)
}
rows, err = queryLatencyRows(sitePK, typePK, resolution, timeRange)
defer rows.Close()
pts := make(map[internal.ID]([]ts.Point))
var mean float64
var fifty int
var ninety int
var pt ts.Point
for rows.Next() {
if err = rows.Scan(&pt.DateTime, &mean, &fifty, &ninety); err != nil {
return weft.InternalServerError(err)
}
pt.Value = mean * scale
pts[internal.Mean] = append(pts[internal.Mean], pt)
pt.Value = float64(fifty) * scale
pts[internal.Fifty] = append(pts[internal.Fifty], pt)
pt.Value = float64(ninety) * scale
pts[internal.Ninety] = append(pts[internal.Ninety], pt)
}
rows.Close()
// Add the latest value to the plot - this may be different to the average at minute or hour resolution.
if err = dbR.QueryRow(`SELECT time, mean, fifty, ninety FROM data.latency WHERE
sitePK = $1 AND typePK = $2
ORDER BY time DESC
LIMIT 1`,
sitePK, typePK).Scan(&pt.DateTime, &mean, &fifty, &ninety); err != nil {
return weft.InternalServerError(err)
}
pt.Value = mean * scale
pts[internal.Mean] = append(pts[internal.Mean], pt)
p.SetLatest(pt, internal.Colour(int(internal.Mean)))
// No latest label for fifty and ninety
pt.Value = float64(fifty) * scale
pts[internal.Fifty] = append(pts[internal.Fifty], pt)
pt.Value = float64(ninety) * scale
pts[internal.Ninety] = append(pts[internal.Ninety], pt)
for k, v := range pts {
i := int(k)
p.AddSeries(ts.Series{Colour: internal.Colour(i), Points: v})
}
// We need the labels in the order of "Mean, Fifty, Ninety" so put labels in the "range pts" won't work
var labels ts.Labels
labels = append(labels, ts.Label{Label: internal.Label(int(internal.Mean)), Colour: internal.Colour(int(internal.Mean))})
labels = append(labels, ts.Label{Label: internal.Label(int(internal.Fifty)), Colour: internal.Colour(int(internal.Fifty))})
labels = append(labels, ts.Label{Label: internal.Label(int(internal.Ninety)), Colour: internal.Colour(int(internal.Ninety))})
p.SetLabels(labels)
if err = plotter.Draw(p, b); err != nil {
return weft.InternalServerError(err)
}
return &weft.StatusOK
}