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 }