computers - hard drive usage per department.sql

-- A custom inventory rule held the size of the Users folder on the machine
-- Report shows the amount of space used on the drive and how much was used by user accounts per department
-- Initially created to estimate amount of storage needed for implementing a backup solution

SELECT DEPARTMENT.NAME as 'Department',
LOCATION.NAME as 'Location',
COUNT(MACHINE.NAME) `# Computers`,
SUM(CASE
    WHEN MACHINE.OS_NAME like 'Mac%' and MACUS.STR_FIELD_VALUE like "%G%" THEN substring_index(MACUS.STR_FIELD_VALUE, "G", 1)
	WHEN MACHINE.OS_NAME like 'Mac%' and MACUS.STR_FIELD_VALUE like "%M%" THEN round(substring_index(MACUS.STR_FIELD_VALUE, "M", 1)/1000,2)
    WHEN MACHINE.OS_NAME like 'Mic%' THEN round(substring_index(substring(WINUS.STR_FIELD_VALUE, LOCATE("Sum      : ", WINUS.STR_FIELD_VALUE)+ 11), "<br/>", 1)/1000000000, 0)
END) as `User Folder Size (G)`


FROM MACHINE
LEFT JOIN MACHINE_CUSTOM_INVENTORY WINUS on MACHINE.ID = WINUS.ID and WINUS.SOFTWARE_ID = 85480
LEFT JOIN MACHINE_CUSTOM_INVENTORY MACUS on MACHINE.ID = MACUS.ID AND MACUS.SOFTWARE_ID = 85481
LEFT JOIN MACHINE_LABEL_JT DEP_MLJT ON (DEP_MLJT.MACHINE_ID = MACHINE.ID)
LEFT JOIN LABEL DEPARTMENT ON (DEPARTMENT.ID = DEP_MLJT.LABEL_ID  AND DEPARTMENT.TYPE <> 'hidden')
LEFT JOIN LABEL_LABEL_JT DEP_JT ON (DEPARTMENT.ID = DEP_JT.CHILD_LABEL_ID AND DEP_JT.LABEL_ID = 258)
LEFT JOIN MACHINE_LABEL_JT LOC_MLJT on (LOC_MLJT.MACHINE_ID = MACHINE.ID)
LEFT JOIN LABEL LOCATION ON (LOCATION.ID = LOC_MLJT.LABEL_ID  AND LOCATION.TYPE <> 'hidden')
LEFT JOIN LABEL_LABEL_JT LOC_JT ON (LOCATION.ID = LOC_JT.CHILD_LABEL_ID AND LOC_JT.LABEL_ID = 439)
WHERE DEP_JT.LABEL_ID = 258
and LOC_JT.LABEL_ID = 439
GROUP BY Department
HAVING `User Folder Size (G)` > 0
and Department != "LIS"

ORDER BY Department