Report Request system count by OS and by office version
My Director is wanting a report monthly on how many systems have xyz os and xyz office install.
So basically im looking for 2 reports
Report 1
windows XP = ### of systems
windows Vista = ### of systems
windows 7 = ### of systems
windows 8 = ### of systems
windows 10 = ### of systems
MacOS = ### of systems
and
report 2
office 2000 = ### of systems
office 2003 = ### of systems
office 2007 = ### of systems
office 2013 = ### of systems
office 2016 = ### of systems
Sadly i do not know anything about SQL and was hoping y'all could help
So basically im looking for 2 reports
Report 1
windows XP = ### of systems
windows Vista = ### of systems
windows 7 = ### of systems
windows 8 = ### of systems
windows 10 = ### of systems
MacOS = ### of systems
and
report 2
office 2000 = ### of systems
office 2003 = ### of systems
office 2007 = ### of systems
office 2013 = ### of systems
office 2016 = ### of systems
Sadly i do not know anything about SQL and was hoping y'all could help
1 Comment
[ + ] Show comment
Answers (1)
Please log in to answer
Posted by:
chucksteel
6 years ago
This OS versions query isn't the prettiest, but it works:
SELECT FAMILY, MAJOR_VERSION, MINOR_VERSION,
SUM((SELECT COUNT(ID) FROM MACHINE WHERE OS_ID = OPERATING_SYSTEMS.ID)) as COMPUTERS
FROM ORG1.OPERATING_SYSTEMS
GROUP BY FAMILY, MAJOR_VERSION, MINOR_VERSION
HAVING COMPUTERS > 0
Getting the counts for Office suites is a bit more tricky due to the multiple versions and names associated with the various suites. If you are able to label all of the software titles that you want to include, that would make it easier.
Comments:
-
this is alot better than what i was coming up with. what im wondering is can we add a column for OS_ NAME and have the report break on FAMILY?
as for the report on office i never though about placing the softwares in a lable then running a report against that. ill try that and post my results - eelder 6 years ago-
Sure, here's the updated query that adds the OS Name and orders it such that it will work better for breaking on the FAMILY column:
SELECT FAMILY, NAME, MAJOR_VERSION, MINOR_VERSION,
SUM((SELECT COUNT(ID) FROM MACHINE WHERE OS_ID = OPERATING_SYSTEMS.ID)) as COMPUTERS
FROM ORG1.OPERATING_SYSTEMS
GROUP BY FAMILY, MAJOR_VERSION, MINOR_VERSION
HAVING COMPUTERS > 0
ORDER BY FAMILY, MAJOR_VERSION, MINOR_VERSION - chucksteel 6 years ago-
perfect! and i was able to get a clean report for the office products too. here is the SQL generated by the wizard. i created a label for each deployed major version and ran a report against those
SELECT DISPLAY_NAME, DISPLAY_VERSION, (select count(distinct MACHINE_ID) from MACHINE_SOFTWARE_JT WHERE SOFTWARE_ID=SOFTWARE.ID) as MACHINES_AFFECTED FROM SOFTWARE WHERE (((select count(distinct MACHINE_ID) from MACHINE_SOFTWARE_JT WHERE SOFTWARE_ID=SOFTWARE.ID) > '0') AND ((( exists (select 1 from LABEL, SOFTWARE_LABEL_JT where SOFTWARE.ID = SOFTWARE_LABEL_JT.SOFTWARE_ID and SOFTWARE_LABEL_JT.LABEL_ID = LABEL.ID and LABEL.NAME = 'S - Office 365')) ) OR (( exists (select 1 from LABEL, SOFTWARE_LABEL_JT where SOFTWARE.ID = SOFTWARE_LABEL_JT.SOFTWARE_ID and SOFTWARE_LABEL_JT.LABEL_ID = LABEL.ID and LABEL.NAME like '%S - Microsoft Office%')) ))) ORDER BY DISPLAY_NAME, DISPLAY_VERSION - eelder 6 years ago
Basically we are looking for a report that shows us how many systems have windows 10 and how many systems have XP and how many have OSX 10.11 and how many have OSX 10.13 etc....
then another report same as above but only for the office suite - eelder 6 years ago