I’m trying to make a time series line graph for some compliance reporting I’m doing. My datasource is MySQL(MariaDB).
I have a dateTime column where the values look like this: '2021-09-22 15:09:18' which I’d like to use as the timeFilter, if possible. I should note that this column was used to label the data copied from a bunch of SQLite files. One for each day, times may vary slightly as they were based on write end time. I’m trying to use this column as the x axis, which would be each day.
For each dateTime, I was trying to use the count of all different types in my Status column, there are 4, (Exception, Scanned, Puppet, Nessus).
I’ve tried using the query builder and just can’t seem to get any data. I have no clue what I’m doing wrong, and apparently how to do it at all. lol
Could someone give me a hand? I’d truly appreciate it. Thanks!
@tvgriffi where are you at in solving this problem? I mean, have you configured a datasource plugin and can you query the data and view it in grafana in, say, the table panel? Or are we not that far along yet?
1 Like
Sorry, I should have been more clear.
I have configured the datasource and I am able to query data from it with simpler queries.
I guess I’m just not really understanding how to write the queries to make the x-axis use days, instead of time, and count all unique occurrences of the status column. I wasn’t sure if that was possible. One line graph with 4 “lines” based on the status counts for each day.
gotcha 
Can you share your raw, unformatted data? In general the x axis on the time series graph is designed to use timestamps.
If you are determined to put something other than timestamps on the x axis, then the categorical Bar Chart is probably your best best, which can use arbitrary strings. You could group by day and then stack your status code to view the count / dispersion per day:
https://play.grafana.org/d/ktMs4D6Mk/5-bar-charts-and-pie-charts?orgId=1
1 Like
# Status, dateTime
Scanned, 2021-11-09 07:51:39
Exception, 2021-11-09 07:51:39
Nessus Missing/Broken, 2021-11-09 07:51:39
Puppet Missing/Broken, 2021-11-09 07:51:39
That’s a small piece of my data. I can’t share the entire table since the majority of it is internal data.
I just need to count a string column and group by a datetime column, going back 10 days. I’m pretty sure I’m just going to do the 10 day part with python using a retention script, since this is essentially just a state table.
I suppose I could use the bar chart. But, I was hoping to use a line graph, if at all possible.
