Queries - GROUP BY software.name, software.version

 
1
0
-1

I want to add queries that are grouped off of software name and version.

So for example - current normal query looking for installation of 7-Zip.

SELECT system.id AS `system.id`, system.icon AS `system.icon`, system.type AS `system.type`, system.name AS `system.name`, system.domain AS `system.domain`, system.ip AS `system.ip`, system.class AS `system.class`,
system.os_family AS `system.os_family`, orgs.name AS `orgs.name`, windows.user_name AS `windows.user_name`, software.name as `software.name`, software.version AS `software.version`, software.id as `software.id` FROM software LEFT JOIN system ON (software.system_id = system.id AND software.current = 'y' AND (software.name LIKE '%7-Zip%')) LEFT JOIN orgs ON (orgs.id = system.org_id) LEFT JOIN windows ON (windows.system_id = system.id AND windows.current = 'y') WHERE @filter


But I want it to return grouped by software.name, software.version


Installed - 7-zip

Software Name - Version - Installs

7-Zip 19.00 (x64 edition) - 19.00.00.0 -157

7-Zip 19.00 (x64) - 19.00 - 2

7-Zip 9.20 (x64 edition)  - 9.20.00.0 - 2

    CommentAdd your comment...

    3 answers

    1.  
      1
      0
      -1

      Mark

      That is close to what I was looking for.


      But in the older version I could then click on each individual version and it would bring up those devices that had version X. This way I know which devices are not getting the updates I am pushing out. See below on my Office 365 example. Select the query and it shows the 1st window below. Then if I select the top one which shows count as 2 it would bring up the next window and show me what two devices are on that version.




      1. Mark Unwin

        Chad,

        Try menu → Manage → Summaries → List Summaries and then run the Software Summary. It should be close to what you need.

        Mark.

      CommentAdd your comment...
    2.  
      1
      0
      -1

      The below should get close.

      SELECT software.name, software.version, TRIM(TRAILING ',' FROM GROUP_CONCAT(system.name)) AS `system.names`, TRIM(TRAILING ',' FROM GROUP_CONCAT(system.id)) AS `system.ids`, TRIM(TRAILING ',' FROM GROUP_CONCAT(windows.user_name)) AS `windows.user_names`, count(*) AS `count` FROM system LEFT JOIN software ON (system.id = software.system_id AND software.current = 'y') LEFT JOIN windows ON (system.id = windows.system_id AND windows.current = 'y') WHERE @filter AND system.os_group = 'windows' AND software.name IS NOT NULL AND system.id IS NOT NULL GROUP BY software.name, software.version
        CommentAdd your comment...
      1.  
        1
        0
        -1

        So you want a row per PC, per piece of installed software? IE - PC Count * Software Count number of rows?

        Or do you want a single row per piece of software, grouped by name, then version? If you're trying to ALSO show which user is associated (ie, windows.user_name), you go back to option #1 (PCs * Software).

        You could group all users and PCs per row of software. I'll see what I can come up with.

          CommentAdd your comment...