none
Report for count of Operating System and MS Office RRS feed

  • Question

  • Hi,

    I need report for count of MS Office and Operating systems in my environment.

    I have checked the report "Count of Operating systems" and "Installed Software" reports but it shows multiple entries depending on the version like Win 10 Ent, Win 10 Pro etc.

    I need a compile report actually not based on versions, so i can get how much i have Office 2016, Office 2013, Win 10 and Win 7 is installed on systems.

    regards

    Kashan Nawaz


    Thursday, September 5, 2019 4:20 AM

Answers

  • Hello Kashan Nawaz,
     
    Thanks for posing in TechNet.
     
    For operating systems, try the following SQL.
     
    select 
    OS,
    count(OS) as COUNT
    from(
    select
    case
    	when Caption0 like '%windows 10%' then 'Windows 10'
    	when Caption0 like '%Windows 7%' then 'Windows 7'
    	else 'Others'
    end as OS
    from v_GS_OPERATING_SYSTEM
    ) as vos
    group by OS
     
    You could add the entry of OS in "case when" to meet your requirement.
     
    As the same, this is for Office.
     
    select 
    OFFICE,
    count(OFFICE) as COUNT
    from(
    select
    case
    	when ARPDisplayName0 like '%2016%' then 'Office 2016'
    	when ARPDisplayName0 like '%2013%' then 'Office 2013'
    	else 'Others'
    end as OFFICE
    from 
    v_GS_INSTALLED_SOFTWARE
    where
    ARPDisplayName0 like '%Microsoft Office%'
    ) as arp
    group by OFFICE
     
    Hope my answer could help you and look forward to your feedback.
     
    Best Regards,
    Ray

    Please remembers to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.


    Thursday, September 5, 2019 5:26 AM
  • For MS office report i tried below link which helps more efficient as i can know the systems having the Office version.

    http://eskonr.com/2015/01/sccm-configmgr-2012-ssrs-report-count-ms-office-versions/

    Regards

    Kashan Nawaz

    • Marked as answer by Kashan Nawaz Friday, September 6, 2019 4:01 AM
    Friday, September 6, 2019 4:01 AM

All replies

  • Hello Kashan Nawaz,
     
    Thanks for posing in TechNet.
     
    For operating systems, try the following SQL.
     
    select 
    OS,
    count(OS) as COUNT
    from(
    select
    case
    	when Caption0 like '%windows 10%' then 'Windows 10'
    	when Caption0 like '%Windows 7%' then 'Windows 7'
    	else 'Others'
    end as OS
    from v_GS_OPERATING_SYSTEM
    ) as vos
    group by OS
     
    You could add the entry of OS in "case when" to meet your requirement.
     
    As the same, this is for Office.
     
    select 
    OFFICE,
    count(OFFICE) as COUNT
    from(
    select
    case
    	when ARPDisplayName0 like '%2016%' then 'Office 2016'
    	when ARPDisplayName0 like '%2013%' then 'Office 2013'
    	else 'Others'
    end as OFFICE
    from 
    v_GS_INSTALLED_SOFTWARE
    where
    ARPDisplayName0 like '%Microsoft Office%'
    ) as arp
    group by OFFICE
     
    Hope my answer could help you and look forward to your feedback.
     
    Best Regards,
    Ray

    Please remembers to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.


    Thursday, September 5, 2019 5:26 AM
  • Hi Ray,

    i run the query for OS, it shows the below error but when i run it on query designer it works perfectly.

    " The report parameter ‘UserSIDs’ uses the field ‘UserSIDs’ in a dataset reference, but the dataset ‘DataSetAdminID’ does not contain that field."


    • Edited by Kashan Nawaz Thursday, September 5, 2019 6:03 AM
    Thursday, September 5, 2019 6:02 AM
  • Hello,
     
    Thanks for the feedback.
     
    Are you using above SQL in a report? How did you do it? Above SQL query doesn't need parameter 'UserSIDs' .
     
    Hope my answer could help you.
     
    Best Regards,
    Ray

    Please remembers to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Thursday, September 5, 2019 9:10 AM
  • i simply create a new report from SCCM reports and yes, we are using SQL server for this (SQL 2014)
    Thursday, September 5, 2019 9:12 AM
  • Hello,
     
    Have you created the dateset using above query? Then choose it to generate the table?
     

     

     
    Best Regards,
    Ray

    Please remembers to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Thursday, September 5, 2019 9:35 AM
  • No i have only single data set and in table i have selected the same data set.
    Thursday, September 5, 2019 9:44 AM
  • Hello,
     
    We need to create a new dataset using above query as shown in my screenshot, then we be able to create the table using the data in it.
     
    It seems that you are not very familiar with the report builder. The following article is a good start to create a basic report.
     
    https://www.red-gate.com/simple-talk/sql/reporting-services/report-builder-3-0-creating-a-basic-report/ 
     
    Feel free to feedback if you have any issues.
     
    Best Regards,
    Ray

    Please remembers to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Thursday, September 5, 2019 10:04 AM
  • Hi Ray,

    Thanks for the support. I tried the given link but the issue was same. but when i create a fresh report with the given query it start works. dont know how but it works
    :)

    thanks and regards

    Kashan Nawaz

    Friday, September 6, 2019 3:59 AM
  • For MS office report i tried below link which helps more efficient as i can know the systems having the Office version.

    http://eskonr.com/2015/01/sccm-configmgr-2012-ssrs-report-count-ms-office-versions/

    Regards

    Kashan Nawaz

    • Marked as answer by Kashan Nawaz Friday, September 6, 2019 4:01 AM
    Friday, September 6, 2019 4:01 AM