How do I keep Grafana from combining multiple metrics into a single line?

[dwhite21]

Hello. I am using Grafana v10.0.0 and am trying to make a time series to graph hosts and their CPU usage. But I think Grafana is combining the results of the two hosts I’m querying for into a single line, instead of creating two lines for two hosts.

Here is my SQLite data source. “timestamp” is a unix epoch integer (e.g.1710210340)

CREATE TABLE stats (
        hostname TEXT NOT NULL,
        timestamp INTEGER NOT NULL,
        kbytes_received INTEGER NOT NULL,
        kbytes_sent INTEGER NOT NULL,
        nessus_cpu INTEGER NOT NULL,
        cpu_load_avg INTEGER NOT NULL,
        num_tcp_sessions INTEGER NOT NULL,
        nessus_vmem INTEGER NOT NULL,
        nessus_mem INTEGER NOT NULL,
        sys_cores INTEGER NOT NULL,
        num_hosts INTEGER NOT NULL,
        num_scans INTEGER NOT NULL,
        PRIMARY KEY (hostname, timestamp)
);
CREATE INDEX "hostname_idx" ON "stats" (
        "hostname"
);

and some sample data:

INSERT INTO stats VALUES('myhost1',1710210940,0,0,0,0,0,16511,6643,16,0,0);
INSERT INTO stats VALUES('myhost1',1710210970,0,0,0,0,0,16511,6643,16,0,0);
INSERT INTO stats VALUES('myhost1',1710211000,1,0,0,0,0,16511,6643,16,0,0);
INSERT INTO stats VALUES('myhost2',1708545833,39,21,1,0,29,30135,7683,16,6,2);
INSERT INTO stats VALUES('myhost2',1708545863,19,14,1,0,30,30135,7684,16,6,2);
INSERT INTO stats VALUES('myhost2',1708545893,7,8,1,0,29,30135,7683,16,6,2);
...

The table holds about 3.5 million records, with about 71 distinct hostnames.

My query is: SELECT hostname as metric, timestamp, cpu_load_avg as value FROM stats WHERE hostname='myhost1' OR hostname='myhost2' ORDER BY timestamp

The result looks like this:

This appears to be either the CPU usage one of my two hosts (I’m not sure which), or both hosts plotted together on the same line (which I think is more likely). Flipping the switch to get “Table view” shows this, where there is no value listed for “metric”.

How can I get a graph that will allow me to plot 2, or 5, or 10 hosts on the same graph, but each with its own line and color?

Thanks to anyone who can help!

[yosiasz]

Also do select timestamp as [time]

[sowdenraymond]

@dwhite21

SELECT hostname as metric, timestamp, cpu_load_avg as value FROM stats WHERE hostname=‘myhost1’ OR hostname=‘myhost2’ ORDER BY timestamp

try:

SELECT hostname as hostname, timestamp, cpu_load_avg as metric FROM stats WHERE hostname=‘myhost1’ OR hostname=‘myhost2’ ORDER BY timestamp

[dwhite21]

yosiasz, thanks, but the query below didn’t seem to change the graph.

SELECT hostname as metric, timestamp as [time], cpu_load_avg as value 
FROM stats 
WHERE hostname='myhost1' OR hostname='myhost2' ORDER BY timestamp

[dwhite21]

Thanks, sowdenraymond, but that query doesn’t seem to affect much (the labels change, but there’s still no “hostname” listed and the metrics for the two hosts are still combined into a single line)

SELECT hostname as hostname, timestamp, cpu_load_avg as metric 
FROM stats 
WHERE hostname='myhost1' OR hostname='myhost2' ORDER BY timestamp;

[yosiasz]

ok next step try this transformation

[dwhite21]

Like this? It doesn’t seem to make a difference to the graph.

and again when I look at the table view, there are no values for “metric” at all.

[dwhite21]

Ugh… sorry, there appears to have been an underlying problem with the database. I had planned the schema with hostname to have a TEXT data type , but somewhere along the way it got defined as an INTEGER field instead (!) SQLite is flexible enough to store text data in the field anyway, but the INTEGER data type threw Grafana off. Regenerating the table with the correct TEXT data type for “hostname” seems to have resolved the problem. Sorry for the trouble!