Hi all.
I’m using Grafana 5.2.1 and Microsoft SQL server. The pie chart plugin 1.3.3? is installed on my instance of Grafana, and I can open a new pie chart on the dashboard.
I can’t figure out how do make a pie chart work. I understand it’s a time series, but don’t know how to get it running. I have a bunch of counts (int) that I want to display, but was hoping someone had an example of a functioning pie chart that I could model mine after.
Thanks in advance!
It depends on if you have a time column or not. If you don’t have a time column you need to fake it and could use a query similar to comment
If you have a time column please refer to Using Microsoft SQL Server in Grafana documentation.
Marcus
Thanks for the links! The issue I’m having is that I’m trying to do counts of items with specific attributes. I don’t have a table set up already of the metrics I want to measure, I’m trying to count them out from an existing table. Is setting up a table the easiest thing to do?
Thanks!
I dont understand what you mean. Can you provide an example of the data you have and query you would use to count of items with specific attributes?
No worries. I have a table with information on processes that were run on a certain server. I want to count by status tag so I have a visualization of how many processes failed, how many were completed, etc.
It’s not letting me past in my code, so take the following screenshot I guess (apologies in advance for probably bad style, I’ve never written SQL before):
If you want to use that in Grafana you must put that in a stored procedure
However, I think using one query instead of looping would be more optimized.
SELECT
GETUTCDATE() AS time,
COUNT(*) AS measurement,
CASE statusId
WHEN 0 THEN 'pending'
WHEN 1 THEN 'running'
...
ELSE 'something else'
END AS outcome
FROM
BPASession
GROUP BY
statusid
That should work fine for pie chart panel. Give it a go and let me know the outcome.
Yep, that works great, and that code is certainly much nicer than what I had.
I do wonder why there are no zero values though, is there a way to change that?
Thanks so much for your help!!
When i tested zero values are shown in legend. Please verify the response using the query inspector to see if rows with zero values are included.
Here’s the values from the query inspector:
{
“xhrStatus”: “complete”,
“request”: {
“method”: “POST”,
“url”: “api/tsdb/query”,
“data”: {
“from”: “1532508508175”,
“to”: “1532530108175”,
“queries”: [
{
“refId”: “A”,
“intervalMs”: 7200000,
“maxDataPoints”: 3,
“datasourceId”: 1,
“rawSql”: “SELECT\r\n GETUTCDATE() AS time,\r\n COUNT() AS measurement,\r\n CASE statusId\r\n WHEN 0 THEN ‘Pending’\r\n WHEN 1 THEN ‘Running’\r\n WHEN 2 THEN ‘Terminated’\r\n WHEN 3 THEN ‘Stopped’\r\n WHEN 4 THEN ‘Completed’\r\n WHEN 5 THEN ‘Debugging’\r\n WHEN 6 THEN ‘Archived’\r\n ELSE ‘Stopping’\r\n END AS outcome\r\nFROM\r\n BPASession\r\nGROUP BY\r\n statusid\r\n",
“format”: “time_series”
}
]
}
},
“response”: {
“results”: {
“A”: {
“refId”: “A”,
“meta”: {
“rowCount”: 0,
“sql”: "SELECT\r\n GETUTCDATE() AS time,\r\n COUNT() AS measurement,\r\n CASE statusId\r\n WHEN 0 THEN ‘Pending’\r\n WHEN 1 THEN ‘Running’\r\n WHEN 2 THEN ‘Terminated’\r\n WHEN 3 THEN ‘Stopped’\r\n WHEN 4 THEN ‘Completed’\r\n WHEN 5 THEN ‘Debugging’\r\n WHEN 6 THEN ‘Archived’\r\n ELSE ‘Stopping’\r\n END AS outcome\r\nFROM\r\n BPASession\r\nGROUP BY\r\n statusid\r\n”
},
“series”: [
{
“name”: “Terminated”,
“points”: [
[
106,
1532530108420
]
]
},
{
“name”: “Stopped”,
“points”: [
[
47,
1532530108420
]
]
},
{
“name”: “Completed”,
“points”: [
[
768,
1532530108420
]
]
},
{
“name”: “Debugging”,
“points”: [
[
473,
1532530108420
]
]
}
],
“tables”: null
}
}
}
}
Guess you don’t have any data with statusid=0 then?
Or make sure that you specify what to count, i.e. COUNT(*) or COUNT(1)
I mean, there are 0 items with statusid = 0, 1, 6 or 7. It’s not super important, but I would prefer to at least put them on the key so I know there are other statuses, even if they aren’t being used at the moment, if that makes sense?
If you would have a related table holding all available statuses with statusid and name it’s easy to accomplish what you want since you can select from related table and left join with BPASession - then you’ll get all statuses if they don’t have a row in the BPASession table. With this you wouldn’t need the case when logic since you can just select the measurement name from the related table.
Ok, gotcha. Thanks for your help!
I am a young developer (beginner), I really need help. My problem is similar to Adrianap’s initial issue, I think you can help me … please 
I have a table named project with a user_id column. My table looks like Adrianap’s table (screenshot). I would like to represent in a pie chart the number of projects realized by each user.
For example, user 1 has made 20 projects, users 2 and 3 have made 10, and user 4 has made 0. Thus, my graph will be completed by half by the user1’s id, a quarter by the id of user2, and the last quarter by the id of user3.
I’m blocked since this morning… Thanks for helping
Would help if you include the mssql queries you’ve tried so far.
Something like this maybe could work without knowing the exact schema of your table?
SELECT
GETUTCDATE() as time,
user_id as metric,
COUNT(1) as value
FROM
project
GROUP BY user_id
Refer to Using MSSQL in Grafana documentation for further reference (posted earlier in this topic) and/or MSSQL tutorials regarding queries with group by/aggregate functions (count in above example).
Good luck
Thanks for helping… I replaced GETUTCDATE by UTC_DATE (it didn’t work before this replacement).
My current error is : Column metric must be of type CHAR, VARCHAR, TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT. metric column name: metric type: INT but datatype is *uint32
In my database, user_id is int(10) unsigned NOT NULL,
Do you know what have I done wrong ?
I want to add that I use MySQL
I finally found the answer
SELECT
UTC_DATE() as time,
CAST(user_id as CHAR) as metric,
COUNT(*) as value
FROM
project
GROUP BY user_id;
Hello there i have the same problem ,i dont have any time column in my data
Select CAST(nn.ComputerID AS NVARCHAR(36))[Computer ID], client.ComputerName , count(*) as [number Software] From oems_client_nn_software as nn
Left Join oems_client as client on client.ComputerID = nn.ComputerID
GROUP BY nn.ComputerID, client.ComputerName
could someone show me how i need to turn this , to use it in a piechart?
Thanks