Need help create Visualization as shown below. Data is in sqlite3. Thaks for the help.
-
What Grafana version and what operating system are you using?
-
What are you trying to achieve? Create a visualization.
-
How are you trying to achieve it? sqlite3 database
-
What happened? Need to split visualization KPI
-
What did you expect to happen?
-
Can you copy/paste the configuration(s) that you are having problems with? Snapshot Provided
-
Did you receive any errors in the Grafana UI or in related logs? If so, please tell us exactly what they were.
-
Did you follow any online instructions? If so, what is the URL?
select date as time,
objectid as metric,
kpi1 as value
from vader
then make sure to select Format time series not table.
Thanks a lot for the reply.
Can you please show snapshot for then make sure to select Format time series not table.
Sorry for inconvenient new to Grafana.
1 Like
please share a screen shot of your grafana query window. right above it is format
Here is the screenshot.
Here is modified query and the result. Still not able to create separate lines for each server.
right below query Format As. currently you have it as table
Thanks yosiasz, below is the snapshot, any other suggestions?
click on the error icon top left click or hover on it
Error:
Status: 500. Message: can not convert to wide series, expected long format series input but got not series
Column 1 |
Column 2 |
Column 3 |
Date |
ObjectId |
KPI1 |
4/1/2024 |
Server1 |
1247 |
4/1/2024 |
Server2 |
3696 |
4/1/2024 |
Server3 |
1199 |
4/2/2024 |
Server1 |
1343 |
4/2/2024 |
Server2 |
5565 |
4/2/2024 |
Server3 |
3448 |
4/3/2024 |
Server1 |
1162 |
4/3/2024 |
Server2 |
4366 |
4/3/2024 |
Server3 |
3922 |
4/4/2024 |
Server1 |
874 |
4/4/2024 |
Server2 |
4018 |
4/4/2024 |
Server3 |
4005 |
4/5/2024 |
Server1 |
1105 |
4/5/2024 |
Server2 |
3474 |
4/5/2024 |
Server3 |
3599 |
4/6/2024 |
Server1 |
1945 |
4/6/2024 |
Server2 |
3324 |
4/6/2024 |
Server3 |
3993 |
4/7/2024 |
Server1 |
1865 |
4/7/2024 |
Server2 |
3187 |
4/7/2024 |
Server3 |
4174 |
4/8/2024 |
Server1 |
1304 |
4/8/2024 |
Server2 |
3339 |
4/8/2024 |
Server3 |
4063 |
4/9/2024 |
Server1 |
1308 |
4/9/2024 |
Server2 |
3113 |
4/9/2024 |
Server3 |
3818 |
4/10/2024 |
Server1 |
644 |
4/10/2024 |
Server2 |
4556 |
4/10/2024 |
Server3 |
3488 |
4/11/2024 |
Server1 |
62 |
4/11/2024 |
Server2 |
4540 |
4/11/2024 |
Server3 |
3544 |
4/12/2024 |
Server1 |
468 |
4/12/2024 |
Server2 |
3865 |
4/12/2024 |
Server3 |
3633 |
4/13/2024 |
Server1 |
925 |
4/13/2024 |
Server2 |
3215 |
4/13/2024 |
Server3 |
4149 |
4/14/2024 |
Server1 |
756 |
4/14/2024 |
Server2 |
2226 |
4/14/2024 |
Server3 |
4149 |
4/15/2024 |
Server1 |
698 |
4/15/2024 |
Server2 |
2035 |
4/15/2024 |
Server3 |
2348 |
4/16/2024 |
Server1 |
554 |
4/16/2024 |
Server2 |
3161 |
4/16/2024 |
Server3 |
2061 |
4/17/2024 |
Server1 |
625 |
4/17/2024 |
Server2 |
3152 |
4/17/2024 |
Server3 |
2380 |
4/18/2024 |
Server1 |
298 |
4/18/2024 |
Server2 |
1014 |
4/18/2024 |
Server3 |
1059 |
you need real datetime utc even if sqllite does not have datetime column ISO-8601 compliant date.
https://www.sqlite.org/lang_datefunc.html
Data has to be converted to pivot table. Sqlite3 does not support pivot tables (unless you load a module)
Below is a crude way to create pivot table in sqlite and achieve the same effect as pivot table. However, in a real application pivot should be created dynamically (Building a pivot table in SQLite)
Once pivot table is crated then the visualization for multiple line is crated as shown below.