Hello dear all! I faced with next problem - I need a graph that will show COUNT(*) of certain columns for time period, but I can’t understand how to do that, maybe it’s a lack of mysql knowledge, hope, someone can help me with it, so…
My table:
desc audit_logging;
±------------±-------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±------------±-------------±-----±----±--------±------+
| id | bigint(20) | NO | PRI | NULL | |
| loginResult | varchar(255) | YES | | NULL | |
| platform | varchar(255) | YES | | NULL | |
| timestamp | bigint(20) | YES | | NULL | |
±------------±-------------±-----±----±--------±------+
Example:
±-----±-------------------±---------±--------------+
| id | loginResult | platform | timeStamp |
±-----±-------------------±---------±--------------+
| 9811 | Successful | iOS | 1538044971569 |
±-----±-------------------±---------±--------------+
To gather count of entries in this table I’m using next query:
SELECT COUNT(*) from audit_logging where platform = “iOS”
and timeStamp >= 1534921200000
and timeStamp < 1537513200000;
To gather metrics in grafana I’m try to use this:
SELECT timeStamp as time_sec,
COUNT(*) as value
FROM audit_logging
WHERE platform = “iOS” AND timeStamp > (UNIX_TIMESTAMP(NOW() - INTERVAL 24 HOUR)*1000) AND timeStamp < (UNIX_TIMESTAMP(NOW())*1000)
ORDER BY timeStamp ASC;
I need to build graph or something to show count of entries for every day, but all what I see is one dot on graph, please help me to fix that.