/build/static/layout/Breadcrumb_cap_w.png

K1000 Report: Distinct Computer Models with Counts

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


Comments

  • Interesting. I never thought to do this. Apparently we have a ton of useless machines out there. - dugullett 11 years ago
  • I like it! Revised it a bit for my own preferences, but great idea!

    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 11 years ago
    • I changed mine up some as well. Since our Lenovo's model numbers are usually 2522CTO, and 2598W1R I added the query for my Lenovo Models. http://www.itninja.com/blog/view/get-system-model

      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 11 years ago
      • You could include a CASE statement in the SELECT like:

        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 11 years ago
  • I'm with John on this one. It may require a bit of tweaking, but the idea is way cool :) - Ben M 11 years ago
This post is locked
 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ