#1a) CPU: idle % (usage peaks)
SELECT
    $timeSeries as t,
    host,
    min(val1 / 100)
FROM $table
WHERE
    $timeFilter
    and item = 'cpu'
    and measurement = 'percent-idle'
GROUP BY
    host,t
ORDER BY
    host,t

#1b) CPU: idle % (average)
SELECT
    $timeSeries as t,
    host,
    avg(val1 / 100)
FROM $table
WHERE
    $timeFilter
    and item = 'cpu'
    and measurement = 'percent-idle'
GROUP BY
    host,t
ORDER BY
    host,t

#2) CPU: Amount of context switches
SELECT
ts
,groupArray((label, var_point_tot_diff)) ga
from
(
  select
  ts
  ,label
  ,max_val
  ,var_point_tot_diff
  ,var_point_timespan
  ,(var_point_tot_diff / (var_point_timespan / 1000)) var_point_per_sec
  from
  (
    select
    ts
    ,label
    ,max_val
    ,if(label = neighbor(label, -1, '')
    	,max_val - neighbor(max_val, -1, 0)
    	,0
    	) var_point_tot_diff
    ,if(label = neighbor(label, -1, '')
    	,ts - neighbor(ts, -1, 0)
    	,0
    	) var_point_timespan
    from
    (
      SELECT       $timeSeries as ts
      ,host label       
      ,max(val1) max_val    
      FROM $table
      WHERE $timeFilter
      and item = 'contextswitch'
      and measurement = 'contextswitch'
      group by label, ts
      ORDER BY label, ts
    )
  )
  where var_point_timespan != 0
  and var_point_tot_diff >= 0
)
group by ts
order by ts

#3) Temperature & Fan RPMs
SELECT
    $timeSeries as t
    ,host
    ,avg(val1)
FROM $table
WHERE $timeFilter
and measurement like 'temperature-temp%'
and item = 'sensors-coretemp-isa-0000'
GROUP BY host, t
ORDER BY host, t

#4a) RAM: used %
SELECT
    t
    ,groupArray((label
    ,used_pct))
from 
(
select t, host label, free.mem_used / total.mem_total used_pct
from
(
select $timeSeries as t, host, item
,sum(val1) mem_used
FROM $table
WHERE $timeFilter
and item = 'memory'
and measurement in ('memory-used', 'memory-slab_unrecl')
group by t, host, item
) free
,(
select $timeSeries as t, host, item
,sum(val1) mem_total
FROM $table
WHERE $timeFilter
and item = 'memory'
and measurement in ('memory-buffered', 'memory-cached', 'memory-free', 'memory-slab_recl', 'memory-slab_unrecl', 'memory-used')
group by t, host, item
) total
where free.t = total.t
and free.host = total.host
and free.item = total.item
)
GROUP BY label, t
ORDER BY label, t

#4b) RAM: cached %
SELECT
    t
    ,groupArray((label
    ,used_pct))
from 
(
select t, host label, cached.mem_cached / total.mem_total used_pct
from
(
select $timeSeries as t, host, item
,sum(val1) mem_cached
FROM $table
WHERE $timeFilter
and item = 'memory'
and measurement = 'memory-cached'
group by t, host, item
) cached
,(
select $timeSeries as t, host, item
,sum(val1) mem_total
FROM $table
WHERE $timeFilter
and item = 'memory'
and measurement in ('memory-buffered', 'memory-cached', 'memory-free', 'memory-slab_recl', 'memory-slab_unrecl', 'memory-used')
group by t, host, item
) total
where cached.t = total.t
and cached.host = total.host
and cached.item = total.item
)
GROUP BY label, t
ORDER BY label, t

#5) Swap: MiB used
SELECT
    $timeSeries as t
    ,host
    ,avg(val1)
FROM $table
WHERE $timeFilter
and item = 'swap'
and measurement = 'swap-used'
GROUP BY host, t
ORDER BY host, t

#6a) Network: RX (values summed by point's timespan)
SELECT
ts
,groupArray((label, var_point_tot_diff)) ga
from
(
  select
  ts
  ,label
  ,max_val
  ,var_point_tot_diff
  ,var_point_timespan
  ,(var_point_tot_diff / (var_point_timespan / 1000)) var_point_per_sec
  from
  (
    select
    ts
    ,label
    ,max_val
    ,if(label = neighbor(label, -1, '')
    	,max_val - neighbor(max_val, -1, 0)
    	,0
    	) var_point_tot_diff
    ,if(label = neighbor(label, -1, '')
    	,ts - neighbor(ts, -1, 0)
    	,0
    	) var_point_timespan
    from
    (
      SELECT       $timeSeries as ts
      ,host || '.' || item label       
      ,max(val1) max_val    
      FROM $table
      WHERE $timeFilter
      and item like 'interface-e%'
      and measurement = 'if_octets'
      group by label, ts
      ORDER BY label, ts
    )
  )
  where var_point_timespan > 0
  and var_point_tot_diff >= 0
)
group by ts
order by ts

#6b) Network: TX (values summed by point's timespan)
SELECT
ts
,groupArray((label, var_point_tot_diff)) ga
from
(
  select
  ts
  ,label
  ,max_val
  ,var_point_tot_diff
  ,var_point_timespan
  ,(var_point_tot_diff / (var_point_timespan / 1000)) var_point_per_sec
  from
  (
    select
    ts
    ,label
    ,max_val
    ,if(label = neighbor(label, -1, '')
    	,max_val - neighbor(max_val, -1, 0)
    	,0
    	) var_point_tot_diff
    ,if(label = neighbor(label, -1, '')
    	,ts - neighbor(ts, -1, 0)
    	,0
    	) var_point_timespan
    from
    (
      SELECT       $timeSeries as ts
      ,host || '.' || item label       
      ,max(val2) max_val    
      FROM $table
      WHERE $timeFilter
      and item like 'interface-e%'
      and measurement = 'if_octets'
      group by label, ts
      ORDER BY label, ts
    )
  )
  where var_point_timespan > 0
  and var_point_tot_diff >= 0
)
group by ts
order by ts

#7a) Disk / host: reads (values summed by point's timespan)
SELECT
ts
,groupArray((label, var_point_tot_diff)) ga
from
(
  select
  ts
  ,label
  ,max_val
  ,var_point_tot_diff
  ,var_point_timespan
  ,(var_point_tot_diff / (var_point_timespan / 1000)) var_point_per_sec
  from
  (
    select
    ts
    ,label
    ,max_val
    ,if(label = neighbor(label, -1, '')
    	,max_val - neighbor(max_val, -1, 0)
    	,0
    	) var_point_tot_diff
    ,if(label = neighbor(label, -1, '')
    	,ts - neighbor(ts, -1, 0)
    	,0
    	) var_point_timespan
    from
    (
      SELECT       $timeSeries as ts
      ,host || '.' || item label       
      ,max(val1) max_val    
      FROM $table
      WHERE $timeFilter
      and item like 'disk-md%'
      and measurement = 'disk_octets'
      and host = 'PUT_HERE_YOUR_HOSTNAME'
      group by label, ts
      ORDER BY label, ts
    )
  )
  where var_point_timespan > 0
  and var_point_tot_diff >= 0
)
group by ts
order by ts

#7b) Disk / host: writes (values summed by point's timespan)
SELECT
ts
,groupArray((label, var_point_tot_diff)) ga
from
(
  select
  ts
  ,label
  ,max_val
  ,var_point_tot_diff
  ,var_point_timespan
  ,(var_point_tot_diff / (var_point_timespan / 1000)) var_point_per_sec
  from
  (
    select
    ts
    ,label
    ,max_val
    ,if(label = neighbor(label, -1, '')
    	,max_val - neighbor(max_val, -1, 0)
    	,0
    	) var_point_tot_diff
    ,if(label = neighbor(label, -1, '')
    	,ts - neighbor(ts, -1, 0)
    	,0
    	) var_point_timespan
    from
    (
      SELECT       $timeSeries as ts
      ,host || '.' || item label       
      ,max(val2) max_val    
      FROM $table
      WHERE $timeFilter
      and item like 'disk-md%'
      and measurement = 'disk_octets'
      and host = 'PUT_HERE_YOUR_HOSTNAME'
      group by label, ts
      ORDER BY label, ts
    )
  )
  where var_point_timespan > 0
  and var_point_tot_diff >= 0
)
group by ts
order by ts

#8a) Disk / VMs: reads (values summed by point's timespan)
SELECT
ts
,groupArray((label, var_point_tot_diff)) ga
from
(
  select
  ts
  ,label
  ,max_val
  ,var_point_tot_diff
  ,var_point_timespan
  ,(var_point_tot_diff / (var_point_timespan / 1000)) var_point_per_sec
  from
  (
    select
    ts
    ,transform(label
      ,['PUT_HERE_YOUR_HOSTNAME1.disk-vdb','PUT_HERE_YOUR_HOSTNAME1.disk-vdc','PUT_HERE_YOUR_HOSTNAME1.disk-vda'
        ,'PUT_HERE_YOUR_HOSTNAME2.disk-vda','PUT_HERE_YOUR_HOSTNAME2.disk-vdb','PUT_HERE_YOUR_HOSTNAME2.disk-vdc'
        ,'PUT_HERE_YOUR_HOSTNAME3.disk-vda','PUT_HERE_YOUR_HOSTNAME3.disk-vdb'
        ,'PUT_HERE_YOUR_HOSTNAME4.disk-vda','PUT_HERE_YOUR_HOSTNAME4.disk-vdb']
      ,['PUT_HERE_YOUR_ALIAS_FOR_HOSTNAME1.root','PUT_HERE_YOUR_ALIAS_FOR_HOSTNAME1.swap','PUT_HERE_YOUR_ALIAS_FOR_HOSTNAME1.nxtcloud'
        ,'PUT_HERE_YOUR_ALIAS_FOR_HOSTNAME2.root','PUT_HERE_YOUR_ALIAS_FOR_HOSTNAME2.swap','PUT_HERE_YOUR_ALIAS_FOR_HOSTNAME2.db'
        ,'PUT_HERE_YOUR_ALIAS_FOR_HOSTNAME3.root','PUT_HERE_YOUR_ALIAS_FOR_HOSTNAME3.swap'
        ,'PUT_HERE_YOUR_ALIAS_FOR_HOSTNAME4.root','PUT_HERE_YOUR_ALIAS_FOR_HOSTNAME4.swap']
      ,'UNKNOWN - FIX ME!' || label) label
    ,label
    ,max_val
    ,if(label = neighbor(label, -1, '')
    	,max_val - neighbor(max_val, -1, 0)
    	,0
    	) var_point_tot_diff
    ,if(label = neighbor(label, -1, '')
    	,ts - neighbor(ts, -1, 0)
    	,0
    	) var_point_timespan
    from
    (
      SELECT       $timeSeries as ts
      ,host || '.' || item label       
      ,max(val1) max_val    
      FROM $table
      WHERE $timeFilter
      and item like 'disk-vd_'
      and measurement = 'disk_octets'
      and host like 'PUT_HERE_YOUR_ALIAS_FOR_HOSTNAME_SUBSTRING%'
      group by label, ts
      ORDER BY label, ts
    )
  )
  where var_point_timespan > 0
  and var_point_tot_diff >= 0
)
group by ts
order by ts

#8b) Disk / VMs: writes (values summed by point's timespan)
SELECT
ts
,groupArray((label, var_point_tot_diff)) ga
from
(
  select
  ts
  ,label
  ,max_val
  ,var_point_tot_diff
  ,var_point_timespan
  ,(var_point_tot_diff / (var_point_timespan / 1000)) var_point_per_sec
  from
  (
    select
    ts
    ,transform(label
      ,['PUT_HERE_YOUR_HOSTNAME1.disk-vdb','PUT_HERE_YOUR_HOSTNAME1.disk-vdc','PUT_HERE_YOUR_HOSTNAME1.disk-vda'
        ,'PUT_HERE_YOUR_HOSTNAME2.disk-vda','PUT_HERE_YOUR_HOSTNAME2.disk-vdb','PUT_HERE_YOUR_HOSTNAME2.disk-vdc'
        ,'PUT_HERE_YOUR_HOSTNAME3.disk-vda','PUT_HERE_YOUR_HOSTNAME3.disk-vdb'
        ,'PUT_HERE_YOUR_HOSTNAME4.disk-vda','PUT_HERE_YOUR_HOSTNAME4.disk-vdb']
      ,['PUT_HERE_YOUR_ALIAS_FOR_HOSTNAME1.root','PUT_HERE_YOUR_ALIAS_FOR_HOSTNAME1.swap','PUT_HERE_YOUR_ALIAS_FOR_HOSTNAME1.nxtcloud'
        ,'PUT_HERE_YOUR_ALIAS_FOR_HOSTNAME2.root','PUT_HERE_YOUR_ALIAS_FOR_HOSTNAME2.swap','PUT_HERE_YOUR_ALIAS_FOR_HOSTNAME2.db'
        ,'PUT_HERE_YOUR_ALIAS_FOR_HOSTNAME3.root','PUT_HERE_YOUR_ALIAS_FOR_HOSTNAME3.swap'
        ,'PUT_HERE_YOUR_ALIAS_FOR_HOSTNAME4.root','PUT_HERE_YOUR_ALIAS_FOR_HOSTNAME4.swap']
      ,'UNKNOWN - FIX ME!' || label) label
    ,label
    ,max_val
    ,if(label = neighbor(label, -1, '')
    	,max_val - neighbor(max_val, -1, 0)
    	,0
    	) var_point_tot_diff
    ,if(label = neighbor(label, -1, '')
    	,ts - neighbor(ts, -1, 0)
    	,0
    	) var_point_timespan
    from
    (
      SELECT       $timeSeries as ts
      ,host || '.' || item label       
      ,max(val2) max_val    
      FROM $table
      WHERE $timeFilter
      and item like 'disk-vd_'
      and measurement = 'disk_octets'
      and host like 'PUT_HERE_YOUR_ALIAS_FOR_HOSTNAME_SUBSTRING%'
      group by label, ts
      ORDER BY label, ts
    )
  )
  where var_point_timespan > 0
  and var_point_tot_diff >= 0
)
group by ts
order by ts

#9) Free disk space: VMs (%)
SELECT
    t
    ,groupArray((label
    ,total_free_pct))
from 
(
select t, host || '.' || item label, free.df_free / (free.df_free + used.df_used) total_free_pct
from
(
select $timeSeries as t, host, item
,sum(val1) df_free
FROM $table
WHERE $timeFilter
and item like 'df-vd%'
and measurement = 'df_complex-free'
group by t, host, item
) free
,(
select $timeSeries as t, host, item
,sum(val1) df_used
FROM $table
WHERE $timeFilter
and item like 'df-vd%'
and measurement = 'df_complex-used'
group by t, host, item
) used
where free.t = used.t
and free.host = used.host
and free.item = used.item
)
GROUP BY label, t
ORDER BY label, t

#10) Uptime
SELECT
    $timeSeries as t
    ,host
    ,max(val1)
FROM $table
WHERE $timeFilter
and item = 'uptime'
and measurement = 'uptime'
GROUP BY host, t
ORDER BY host, t

#11a - left axis) Apache VMweb: general stats (values summed by point's timespan)
SELECT
ts
,groupArray((label, var_point_tot_diff)) ga
from
(
  select
  ts
  ,label
  ,max_val
  ,var_point_tot_diff
  ,var_point_timespan
  ,(var_point_tot_diff / (var_point_timespan / 1000)) var_point_per_sec
  from
  (
    select
    ts
    ,label
    ,max_val
    ,if(label = neighbor(label, -1, '')
    	,max_val - neighbor(max_val, -1, 0)
    	,0
    	) var_point_tot_diff
    ,if(label = neighbor(label, -1, '')
    	,ts - neighbor(ts, -1, 0)
    	,0
    	) var_point_timespan
    from
    (
      SELECT       $timeSeries as ts
      ,measurement label       
      ,max(val1) max_val    
      FROM $table
      WHERE $timeFilter
      and host = 'YOUR_WEBSERVER_HOSTNAME'
      and item = 'apache-apache80'
      and measurement in ('apache_bytes', 'apache_requests')
      group by label, ts
      ORDER BY label, ts
    )
  )
  where var_point_timespan > 0
  and var_point_tot_diff >= 0
)
group by ts
order by ts

#11a - right axis) Apache VMweb: general stats (values summed by point's timespan)
select
t
,groupArray((measurement, myresult))
FROM
(
SELECT
    $timeSeries as t
    ,measurement
    ,sum(val1) myresult
FROM $table
WHERE $timeFilter
and host = 'YOUR_WEBSERVER_HOSTNAME'
and item = 'apache-apache80'
and measurement = 'apache_connections'
GROUP BY measurement, t
)
group by measurement, t
order by measurement, t

#11b) Apache VMweb: CPU & RAM
#(4 separate queries plotted in the same chart)
#total amount of RAM used
SELECT
    $timeSeries as t
    ,measurement
    ,avg(val1)
FROM $table
WHERE $timeFilter
and item like 'processes-apache2'
and measurement = 'ps_rss'
and host = 'YOUR_WEBSERVER_HOSTNAME'
GROUP BY measurement, t
ORDER BY measurement, t

#some misterious "data" metric
SELECT
    $timeSeries as t
    ,measurement
    ,avg(val1)
FROM $table
WHERE $timeFilter
and item like 'processes-apache2'
and measurement = 'ps_data'
and host = 'YOUR_WEBSERVER_HOSTNAME'
GROUP BY measurement, t
ORDER BY measurement, t

#"user" CPU used
SELECT
    t,
    groupArray((label,if(myresult > 0, myresult, 0)))
from
(
SELECT
    $timeSeries as t
    ,'CPU-user' label
    ,avg(runningDifference(val1)) myresult
FROM $table
WHERE $timeFilter
and item = 'processes-apache2'
and measurement = 'ps_cputime'
and host = 'YOUR_WEBSERVER_HOSTNAME'
GROUP BY t, 'CPU-user'
ORDER BY t, 'CPU-user'
)
group by  t
order by  t asc

#"system" CPU used

SELECT
    t,
    groupArray((label, if(myresult > 0, myresult, 0))) as groupArr
from
(
SELECT
    $timeSeries as t
    ,'CPU-sys' label
    ,avg(runningDifference(val2)) myresult
FROM $table
WHERE $timeFilter
and item = 'processes-apache2'
and measurement = 'ps_cputime'
and host = 'YOUR_WEBSERVER_HOSTNAME'
GROUP BY t, 'CPU-sys'
ORDER BY t, 'CPU-sys'
)
group by  t
order by  t asc

#11c) Apache VMweb: #processes vs. #requests
#(left side: amount of processes)
SELECT
    t
    ,groupArray((label, myresult))
FROM
(
select $timeSeries as t, 'Processes' label, max(val1) myresult
FROM $table
WHERE $timeFilter
and item = 'processes-apache2'
and measurement = 'ps_count'
and host = 'YOUR_WEBSERVER_HOSTNAME'
group by t, label
)
GROUP BY label, t
ORDER BY label, t

#(right side: amount of requests)
SELECT
    t
    ,groupArray((label, if(myresult > 0, myresult, 0)))
FROM
(
select $timeSeries as t, 'Requests' label, max(runningDifference(val1)) myresult
FROM $table
WHERE $timeFilter
and item = 'apache-apache80'
and measurement = 'apache_requests'
and host = 'YOUR_WEBSERVER_HOSTNAME'
group by t, label
)
GROUP BY label, t
ORDER BY label, t

#12) MariaDB ops (values summed by point's timespan)
SELECT
ts
,groupArray((label, var_point_tot_diff)) ga
from
(
  select
  ts
  ,label
  ,max_val
  ,var_point_tot_diff
  ,var_point_timespan
  ,(var_point_tot_diff / (var_point_timespan / 1000)) var_point_per_sec
  from
  (
    select
    ts
    ,label
    ,max_val
    ,if(label = neighbor(label, -1, '')
    	,max_val - neighbor(max_val, -1, 0)
    	,0
    	) var_point_tot_diff
    ,if(label = neighbor(label, -1, '')
    	,ts - neighbor(ts, -1, 0)
    	,0
    	) var_point_timespan
    from
    (
      SELECT       $timeSeries as ts
      ,transform(measurement
      ,['mysql_commands-select', 'mysql_commands-insert','mysql_commands-delete','mysql_commands-update','mysql_commands-truncate','mysql_commands-commit','mysql_commands-drop_table']
        ,['Selects','Inserts','Deletes','Updates','Truncates','Commits','Drops']
        ,'UNKNOWN - FIX ME!') label
      ,max(val1) max_val    
      FROM $table
      WHERE $timeFilter
      and item = 'mysql-general'
      and measurement in ('mysql_commands-select', 'mysql_commands-insert', 'mysql_commands-delete', 'mysql_commands-update', 'mysql_commands-truncate', 'mysql_commands-commit', 'mysql_commands-drop_table')
      and host = 'YOUR_DATABASE_HOSTNAME'
      group by label, ts
      ORDER BY label, ts
    )
  )
  where var_point_timespan > 0
  and var_point_tot_diff >= 0
)
group by ts
order by ts