Hi there. I am not sure if this the correct place to ask this, please point me in the right direction if not.
I have a query, extracting monthly drive information, free space etc for all monitored machines.
I would like to add some trending functionality to the query so we can start this data “working for us”.
This is the query in question.
SELECT c.computerID AS 'ComputerID', c.name AS 'Machine Name',
d.Letter AS 'Drive',
dsm.DriveID AS 'Drive ID', dsm.Free AS '% Free', 100 - dsm.Free AS '% Used', dsm.Year AS 'Year', dsm.Month AS 'Month',
CONCAT((CASE dsm.Month
WHEN '1' THEN 'Jan'
WHEN '2' THEN 'Feb'
WHEN '3' THEN 'Mar'
WHEN '4' THEN 'Apr'
WHEN '5' THEN 'May'
WHEN '6' THEN 'Jun'
WHEN '7' THEN 'Jul'
WHEN '8' THEN 'Aug'
WHEN '9' THEN 'Sep'
WHEN '10' THEN 'Oct'
WHEN '11' THEN 'Nov'
WHEN '12' THEN 'Dec'
END), ' ', dsm.Year) AS 'Date'
FROM h_drivestatsmonthly dsm
JOIN drives d ON dsm.driveid=d.driveid
JOIN computers c ON c.computerid=d.computerid
WHERE LOCATE('server', LOWER(c.OS))>0
ORDER BY
c.name ASC, dsm.year ASC, dsm.Month
Specifically, what I would like is to have an average growth display for each drive, and possibly project that forward 6 ( variable ) months.