I wanted a report that would give me a list of computers sorted by Manufacturer, then Model with counts of each.
Here is the fairly simple SQL for the report I came up with.
SELECT CS_MANUFACTURER,CS_MODEL, COUNT(CS_MODEL) AS COUNT FROM MACHINE
GROUP BY CS_MODEL ORDER BY COUNT DESC
I wanted my report to have the most deployed systems at the top, but you can modify the order by to CS_MANUFACTURER,CS_MODEL if you want it ordered by Manufacturer/Model
John
_______________________
SELECT CS_MANUFACTURER AS MANUFACTURER,CS_MODEL AS MODEL,
CHASSIS_TYPE AS FORM_FACTOR, COUNT(CS_MODEL) AS COUNT
FROM MACHINE
GROUP BY CS_MODEL
ORDER BY FORM_FACTOR, MODEL - jverbosk 12 years ago
SELECT CS_MANUFACTURER AS MANUFACTURER,CS_MODEL AS MODEL,(SELECT MACHINE_CUSTOM_INVENTORY.STR_FIELD_VALUE
FROM MACHINE_CUSTOM_INVENTORY
WHERE MACHINE_CUSTOM_INVENTORY.ID=MACHINE.ID AND MACHINE_CUSTOM_INVENTORY.SOFTWARE_ID=1699) AS Lenovo_Models,
CHASSIS_TYPE AS FORM_FACTOR, COUNT(CS_MODEL) AS COUNT
FROM MACHINE
GROUP BY CS_MODEL
ORDER BY COUNT DESC
I'm wondering if there is a better way to say if manufacturer like Lenovo then replace Model with Lenovo_models? - dugullett 12 years ago
SELECT
CS_MANUFACTURER AS MANUFACTURER,
CASE WHEN CS_MANUFACTURER='Lenovo' THEN LENOVO_MODELS.MODEL END AS MODEL,
CASE WHEN CS_MANUFACTURER!='Lenovo' THEN CS_MODEL END AS MODEL
Where you select your lenovo_models into a temp table and join it in the original.
If your option works, I'm not sure this is any more graceful.
caveat: have not tested the above, it's just to convey the concept. - sklauminzer 12 years ago