I have the following data in a MySQL database:
SELECT date AS "time",
platform,
remediated_in_sla AS "Remediated in SLA",
remediated_out_sla AS "Remediated out SLA",
not_remediated_in_sla AS "Not remediated in SLA",
not_remediated_out_sla AS "Not remediated out sla"
FROM fixit_sla
ORDER BY date desc
LIMIT 2;
+------------+--------------+-------------------+--------------------+-----------------------+------------------------+
| time | platform | Remediated in SLA | Remediated out SLA | Not remediated in SLA | Not remediated out sla |
+------------+--------------+-------------------+--------------------+-----------------------+------------------------+
| 2019-09-13 | Servers | 29139 | 54642 | 47934 | 89232 |
| 2019-09-13 | Workstations | 50393 | 30523 | 13676 | 42926 |
+------------+--------------+-------------------+--------------------+-----------------------+------------------------+
In excel, I can create a stacked horizontal chart that looks like so:
I am having trouble creating a similar chart in Grafana. Here are my settings:
.
I have tried using either the Graph or Grouped Bar Chart plugins. I am limited to only 2 links/images as a new user so I cant really post other screenshots. I hope that is enough info.
Ended up switching the rows and columns. What a terrible work around…
(select 'Workstations' as platform,
'remediated_in_sla' AS Metric,
(SELECT (CAST(a.remediated_in_sla AS SIGNED) *100 / (CAST(a.remediated_in_sla AS SIGNED) + CAST(a.remediated_out_sla AS SIGNED) + CAST(a.not_remediated_in_sla AS SIGNED) + CAST(a.not_remediated_out_sla AS SIGNED))) AS remediated_in_sla
from (
select remediated_in_sla, remediated_out_sla, not_remediated_in_sla, not_remediated_out_sla
from fixit_sla
where platform = "workstations"
order by date desc limit 1
) AS a) as Value
from fixit_sla
Where platform = "Workstations"
order by date desc
limit 1)
UNION
(select 'Workstations' as platform,
'remediated_out_sla' AS Metric,
(SELECT (CAST(a.remediated_out_sla AS SIGNED) *100 / (CAST(a.remediated_in_sla AS SIGNED) + CAST(a.remediated_out_sla AS SIGNED) + CAST(a.not_remediated_in_sla AS SIGNED) + CAST(a.not_remediated_out_sla AS SIGNED))) AS remediated_out_sla
from (
select remediated_in_sla, remediated_out_sla, not_remediated_in_sla, not_remediated_out_sla
from fixit_sla
where platform = "workstations"
order by date desc limit 1
) AS a) as Value
from fixit_sla
Where platform = "Workstations"
order by date desc
limit 1)
UNION
(select 'Workstations' as platform,
'not_remediated_in_sla' AS Metric,
(SELECT (CAST(a.not_remediated_in_sla AS SIGNED) *100 / (CAST(a.remediated_in_sla AS SIGNED) + CAST(a.remediated_out_sla AS SIGNED) + CAST(a.not_remediated_in_sla AS SIGNED) + CAST(a.not_remediated_out_sla AS SIGNED))) AS not_remediated_in_sla
from (
select remediated_in_sla, remediated_out_sla, not_remediated_in_sla, not_remediated_out_sla
from fixit_sla
where platform = "workstations"
order by date desc
limit 1
) AS a) as Value
from fixit_sla
Where platform = "Workstations"
order by date desc
limit 1)
UNION
(select 'Workstations' as platform,
'not_remediated_out_sla' AS Metric,
(SELECT (CAST(a.not_remediated_out_sla AS SIGNED) *100 / (CAST(a.remediated_in_sla AS SIGNED) + CAST(a.remediated_out_sla AS SIGNED) + CAST(a.not_remediated_in_sla AS SIGNED) + CAST(a.not_remediated_out_sla AS SIGNED))) AS not_remediated_out_sla
from (
select remediated_in_sla, remediated_out_sla, not_remediated_in_sla, not_remediated_out_sla
from fixit_sla
where platform = "workstations"
order by date desc
limit 1
) AS a) as Value
from fixit_sla
Where platform = "Workstations"
order by date desc
limit 1)
UNION
(select 'Servers' as platform,
'remediated_in_sla' AS Metric,
(SELECT (CAST(a.remediated_in_sla AS SIGNED) *100 / (CAST(a.remediated_in_sla AS SIGNED) + CAST(a.remediated_out_sla AS SIGNED) + CAST(a.not_remediated_in_sla AS SIGNED) + CAST(a.not_remediated_out_sla AS SIGNED))) AS remediated_in_sla
from (
select remediated_in_sla, remediated_out_sla, not_remediated_in_sla, not_remediated_out_sla
from fixit_sla
where platform = "Servers"
order by date desc limit 1
) AS a) as Value
from fixit_sla
Where platform = "Servers"
order by date desc
limit 1)
UNION
(select 'Servers' as platform,
'remediated_out_sla' AS Metric,
(SELECT (CAST(a.remediated_out_sla AS SIGNED) *100 / (CAST(a.remediated_in_sla AS SIGNED) + CAST(a.remediated_out_sla AS SIGNED) + CAST(a.not_remediated_in_sla AS SIGNED) + CAST(a.not_remediated_out_sla AS SIGNED))) AS remediated_out_sla
from (
select remediated_in_sla, remediated_out_sla, not_remediated_in_sla, not_remediated_out_sla
from fixit_sla
where platform = "Servers"
order by date desc limit 1
) AS a) as Value
from fixit_sla
Where platform = "Servers"
order by date desc
limit 1)
UNION
(select 'Servers' as platform,
'not_remediated_in_sla' AS Metric,
(SELECT (CAST(a.not_remediated_in_sla AS SIGNED) *100 / (CAST(a.remediated_in_sla AS SIGNED) + CAST(a.remediated_out_sla AS SIGNED) + CAST(a.not_remediated_in_sla AS SIGNED) + CAST(a.not_remediated_out_sla AS SIGNED))) AS not_remediated_in_sla
from (
select remediated_in_sla, remediated_out_sla, not_remediated_in_sla, not_remediated_out_sla
from fixit_sla
where platform = "Servers"
order by date desc
limit 1
) AS a) as Value
from fixit_sla
Where platform = "Servers"
order by date desc
limit 1)
UNION
(select 'Servers' as platform,
'not_remediated_out_sla' AS Metric,
(SELECT (CAST(a.not_remediated_out_sla AS SIGNED) *100 / (CAST(a.remediated_in_sla AS SIGNED) + CAST(a.remediated_out_sla AS SIGNED) + CAST(a.not_remediated_in_sla AS SIGNED) + CAST(a.not_remediated_out_sla AS SIGNED))) AS not_remediated_out_sla
from (
select remediated_in_sla, remediated_out_sla, not_remediated_in_sla, not_remediated_out_sla
from fixit_sla
where platform = "Servers"
order by date desc
limit 1
) AS a) as Value
from fixit_sla
Where platform = "Servers"
order by date desc
limit 1)
Can we achieve the same using elastic search?
