Hi, i got unsolved mistery that i can’t understand.
Our data is in a PostgreSQL database. This graph is using log table of our completed crawler (it can be either completed, failed, timedout or died).
For the exemple below, the inteval is 10min for a timefilter of the last 7 days
Graphana render the only fail of the day, over full day like if they were many failed in a row.
-
What Grafana version and what operating system are you using?
Unsure, don’t know where to find the graphana version. Pop’OS -
What are you trying to achieve?
Rendering a graph showing the the percentage of success of our process. -
How are you trying to achieve it?
SELECT
$__timeGroupAlias(crawler."timestamp",$__interval,0),
COUNT(CASE WHEN status='DIED' THEN 1 END)::float/NULLIF((COUNT(CASE WHEN status ~ '(COMPLETED|FAILED|DIED|TIMEDOUT).*' THEN 1 END)::float),0)*100 as "%DIED",
COUNT(CASE WHEN status='FAILED' THEN 1 END)::float/NULLIF((COUNT(CASE WHEN status ~ '(COMPLETED|FAILED|DIED|TIMEDOUT).*' THEN 1 END)::float),0)*100 as "%FAILED",
COUNT(CASE WHEN status='TIMEDOUT' THEN 1 END)::float/NULLIF((COUNT(CASE WHEN status ~ '(COMPLETED|FAILED|DIED|TIMEDOUT).*' THEN 1 END)::float),0)*100 as "%TIMEDOUT",
COUNT(CASE WHEN status='COMPLETED' THEN 1 END)::float/NULLIF((COUNT(CASE WHEN status ~ '(COMPLETED|FAILED|DIED|TIMEDOUT).*' THEN 1 END)::float),0)*100 as "%COMPLETED"
FROM crawler
LEFT JOIN biz on biz.id = biz_id
LEFT JOIN application ON crawler.application_id = application.id
LEFT JOIN vendor ON application.vendor_id = vendor.id
WHERE
$__timeFilter(crawler.timestamp) AND NOT biz.module IN('Pending') AND vendor.name IN($vendor_name) AND biz.name IN($biz_name)
GROUP BY 1
-
What happened?
-
What did you expect to happen?
-
Can you copy/paste the configuration(s) that you are having problems with?
"id": 23763571993,
"gridPos": {
"h": 9,
"w": 6,
"x": 0,
"y": 1
},
"type": "timeseries",
"title": "${biz_name} - Crawler result",
"scopedVars": {
"biz_name": {
"text": "Blurred",
"value": "Blurred",
"selected": false
}
},
"repeat": "biz_name",
"repeatDirection": "h",
"maxPerRow": 4,
"fieldConfig": {
"defaults": {
"custom": {
"drawStyle": "bars",
"lineInterpolation": "linear",
"barAlignment": 0,
"lineWidth": 0,
"fillOpacity": 84,
"gradientMode": "none",
"spanNulls": false,
"showPoints": "auto",
"pointSize": 5,
"stacking": {
"mode": "normal",
"group": "A"
},
"axisPlacement": "auto",
"axisLabel": "",
"scaleDistribution": {
"type": "linear"
},
"hideFrom": {
"tooltip": false,
"viz": false,
"legend": false
},
"thresholdsStyle": {
"mode": "off"
}
},
"color": {
"mode": "palette-classic"
},
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "green",
"value": null
},
{
"color": "red",
"value": 80
}
]
},
"mappings": [],
"max": 100
},
"overrides": [
{
"matcher": {
"id": "byName",
"options": "%COMPLETED"
},
"properties": [
{
"id": "color",
"value": {
"fixedColor": "dark-green",
"mode": "fixed"
}
}
]
},
{
"matcher": {
"id": "byName",
"options": "%TIMEDOUT"
},
"properties": [
{
"id": "color",
"value": {
"fixedColor": "dark-orange",
"mode": "fixed"
}
}
]
},
{
"matcher": {
"id": "byName",
"options": "%FAILED"
},
"properties": [
{
"id": "color",
"value": {
"fixedColor": "dark-yellow",
"mode": "fixed"
}
}
]
},
{
"matcher": {
"id": "byName",
"options": "%DIED"
},
"properties": [
{
"id": "color",
"value": {
"fixedColor": "dark-purple",
"mode": "fixed"
}
}
]
}
]
},
"options": {
"tooltip": {
"mode": "single"
},
"legend": {
"displayMode": "list",
"placement": "bottom",
"calcs": []
}
},
"targets": [
{
"format": "time_series",
"group": [],
"metricColumn": "none",
"rawQuery": true,
"rawSql": " SELECT\n $__timeGroupAlias(crawler.\"timestamp\",$__interval,0),\n COUNT(CASE WHEN status='DIED' THEN 1 END)::float/NULLIF((COUNT(CASE WHEN status IN ('COMPLETED','FAILED','DIED','TIMEDOUT') THEN 1 END)::float),0)*100 as \"%DIED\",\n COUNT(CASE WHEN status='FAILED' THEN 1 END)::float/NULLIF((COUNT(CASE WHEN status IN ('COMPLETED','FAILED','DIED','TIMEDOUT') THEN 1 END)::float),0)*100 as \"%FAILED\",\n COUNT(CASE WHEN status='TIMEDOUT' THEN 1 END)::float/NULLIF((COUNT(CASE WHEN status IN ('COMPLETED','FAILED','DIED','TIMEDOUT') THEN 1 END)::float),0)*100 as \"%TIMEDOUT\",\n COUNT(CASE WHEN status IN('COMPLETED') THEN 1 END)::float/NULLIF((COUNT(CASE WHEN status IN ('COMPLETED','FAILED','DIED','TIMEDOUT') THEN 1 END)::float),0)*100 as \"%COMPLETED\"\nFROM crawler\n LEFT JOIN biz on biz.id = biz_id\n LEFT JOIN application ON crawler.application_id = application.id\n LEFT JOIN vendor ON application.vendor_id = vendor.id\nWHERE\n $__timeFilter(crawler.timestamp) AND NOT biz.module IN('Pending') AND vendor.name IN($vendor_name) AND biz.name IN($biz_name)\nGROUP BY 1\n\n",
"refId": "A",
"select": [
[
{
"params": [
"value"
],
"type": "column"
}
]
],
"timeColumn": "time",
"where": [
{
"name": "$__timeFilter",
"params": [],
"type": "macro"
}
]
}
],
"transparent": true,
"description": "",
"datasource": null
}
Thanks for the help