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
1 Comment   [ + ] Show Comment

Comments

  • KACE stores the macOS versions by build number, so a report based just on that will be messy. How granular do you want your macOS versions to be? Do you just want macOS, or do you want it broken down by major release, e.g. 10.12, 10.11, 10.10, etc.?
    • both mac and windows only by major release

      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
  • This content is currently hidden from public view.
    Reason: Removed by member request
    For more information, visit our FAQ's.
Please log in to comment

Answers

0

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. 
Answered 12/11/2017 by: chucksteel
Red Belt

  • 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
    • 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
      • 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
Please log in to comment
Answer this question or Comment on this question for clarity