How to create a simple bar graph

Hi Team,

Its first time I am trying to execute sql query in grafana.
My query is quite simple as I am looking for bar chart between number of user login w.rt. to date.
So I have selected dashboard-> graph -> edit and pasted my query ,but its showing error
“Found no column named time”.
I want to see a bar graph (user login w.rt. to date).
Current Login_Time is in format of mm/dd/yyyy so I need to convert this into only date format and create a graph.
SELECT
[USER_ID]
,[LOGIN_TIME]
FROM [database].[dbo].[table]

I have gone through many links and tried few of them but unsuccessful.
The below 2 solution which is provided on support side:
now() as time_sec – > error mssql: ‘now’ is not a recognized built-in function name.
__timeGroup(“GMT_CREATED”, ‘24h’, 0),-> erro mssql: ‘__timeGroup’ is not a recog

Kindly guide me .

Thanks,

You’ve specified that you’re using mysql, but based on the errors you receive it looks like you’re using the mssql datasource?

1 Like

Thanks !!!
After applying suggested changes by you I am getting below error

mssql: Incorrect syntax near ‘>’.

1 Like

Thanks for the response !!!
Yes, I am using mssql as datasource and querying it directly.

send me the exact query what u have used .

1 Like

SELECT
[USER_ID]
,$__timeFilter(time) as time_sec
FROM [mydb].[dbo].[loginuser]

where “USER_ID” and “time” are colom names of the table “loginuser”
mydb is the database name.

SELECT time ,

USER_ID

FROM loginuser;

It would get expected result .

1 Like

Thanks Siva but now I am getting Found no column named time.

Hey Gaurav

that’s your table column which has date and time ,

example: your table has a two columns datetime and userid

garfana query: select datetime as time_sec , count(userid) as Total from your table ;

this I the pattern , max of metrics includes time series , so please go through basics

regards

Siva

1 Like

Hi @gauravpurohit

I think you should check connection to database, If you Press Save and Test => Database Connection OK => Done.

The database user you specify when you add the data source should only be granted SELECT permissions on the specified database & tables you want to query. Grafana does not validate that the query is safe. The query could include any SQL statement.

 CREATE USER 'grafanaReader' IDENTIFIED BY 'password';
 GRANT SELECT ON mydatabase.mytable TO 'grafanaReader';

Refer to: MySQL data source | Grafana documentation

This is my query:

SELECT USER_ID, time from loginuser;

Thanks All I have manged to fix it :grinning:

Hi,

I am facing the same issue connecting MySQL to grafana. I am not able to create a bar graph - time series in grafana. I have the following fields in my table - id, location, name, serial_number, manufacturer, model_id, className, os_version, operational_status, dns_domain, last_discovered, ip_address.

SELECT _id, location, name, serial_number, manufacturer, model_id, className, os_version, operational_status, dns_domain, last_discovered, ip_address
FROM cmdb_server

I want to create a graph using these columns. The last_discovered is a string data type so I used the following query to create a graph, but no luck! Can you please guide me.

SELECT
str_to_date(last_discovered, ‘%m/%d/%Y %H:%i:%s’) as time_sec,
_id as value,
classname as metric
FROM cmdb_server
WHERE $__timeFilter(last_discovered)
ORDER BY last_discovered ASC

I am not sure if I am using the correct value or metric and do not know what to use for value and metric. We are not targeting a specific report for now, for test purpose you can pick any column from here which will suit the query best to help me out! Thanks.