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
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.
Try menu → Manage → Summaries → List Summaries and then run the Software Summary. It should be close to what you need.
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
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.
Powered by a free Atlassian Confluence Open Source Project License granted to Opmantek. Evaluate Confluence today.