none
SQL Query to check MS Office Service Pack level?

    Question

  • Hi,

    We are using SCCM 2007 R3.

    Can someone suggest the SQL Query to run a report on SCCM which should show Office Service Pack level on end points?

    Regards,

    maqsood


    Maqsood Mohammed Senior Systems Engineer MCITP-Enterprise Admin & ITILv3 Foundation Certified

    Tuesday, May 29, 2012 6:16 AM

Answers

  • SELECT     dbo.v_R_System.Name0, dbo.v_GS_OPERATING_SYSTEM.Caption0 AS [Operating System],
                          dbo.v_GS_OPERATING_SYSTEM.CSDVersion0 AS [OS Service Pack], arp.DisplayName0,
                          CASE WHEN arp.version0 LIKE '11.0.6361.0' THEN 'SP1' WHEN arp.version0 LIKE '11.0.7969.0' THEN 'SP2' WHEN arp.version0 LIKE '11.0.8173.0' THEN 'SP3' WHEN
                           arp.version0 LIKE '12.0.6215.1000' THEN 'SP1' WHEN arp.version0 LIKE '12.0.6425.1000' THEN 'SP2' WHEN arp.version0 LIKE '14.0.6029.1000' THEN 'SP1' ELSE '' END
                           AS 'Service Pack', arp.Version0
    FROM         dbo.v_Add_Remove_Programs AS arp INNER JOIN
                          dbo.v_R_System ON arp.ResourceID = dbo.v_R_System.ResourceID INNER JOIN
                          dbo.v_RA_System_SMSInstalledSites AS ASSG ON dbo.v_R_System.ResourceID = ASSG.ResourceID INNER JOIN
                          dbo.v_GS_OPERATING_SYSTEM ON dbo.v_R_System.ResourceID = dbo.v_GS_OPERATING_SYSTEM.ResourceID
    WHERE     (arp.DisplayName0 LIKE '%Microsoft Office%edition%' OR
                          arp.DisplayName0 LIKE '%Microsoft Office Standard 2007%' OR
                          arp.DisplayName0 LIKE '%Microsoft Office Enterprise 2007%' OR
                          arp.DisplayName0 LIKE '%Microsoft Office Professional%2007%' OR
                          arp.DisplayName0 LIKE '%Microsoft Office Standard 2010%' OR
                          arp.DisplayName0 LIKE '%Microsoft Office Enterprise 2010%' OR
                          arp.DisplayName0 LIKE '%Microsoft Office Professional%2010%' OR
                          arp.DisplayName0 LIKE 'Microsoft Office 2000%' OR
                          arp.DisplayName0 LIKE 'Microsoft Office XP%') AND (arp.DisplayName0 NOT LIKE '%update%') AND
                          (arp.DisplayName0 NOT LIKE '%Microsoft Office XP Web Components') AND (dbo.v_R_System.Operating_System_Name_and0 NOT LIKE '%server%') AND
                          (arp.InstallDate0 NOT LIKE 'NULL')
    ORDER BY dbo.v_R_System.Name0, arp.DisplayName0, arp.Version0
    Wednesday, May 30, 2012 10:45 AM

All replies

  • SELECT arp.DisplayName0, case
          when arp.version0 LIKE '11.0.6361.0' then 'SP1' when arp.version0 LIKE '11.0.7969.0' then 'SP2' when arp.version0 LIKE '11.0.8173.0' then 'SP3' when arp.version0 LIKE '12.0.6215.1000' then 'SP1' when arp.version0 LIKE '12.0.6425.1000' then 'SP2'  when arp.version0 LIKE '12.0.6612.1000' then 'SP3' when arp.version0 LIKE '14.0.6029.1000' then 'SP1'
      else ''
      end as 'Service Pack', arp.Version0, Count(DISTINCT v_r_system.resourceid) AS 'Count'
    FROM v_ADD_REMOVE_PROGRAMS arp, v_r_system, V_RA_System_SMSInstalledSites ASSG
    WHERE ARP.resourceid =  v_r_system.resourceid and v_r_system.resourceid = assg.resourceid and
     (arp.displayname0 like '%Microsoft Office%edition%' or arp.displayname0 like '%Microsoft Office Standard 2007%' or arp.displayname0 like '%Microsoft Office Enterprise 2007%' or arp.displayname0 like '%Microsoft Office Professional%2007%' or arp.displayname0 like '%Microsoft Office Standard 2010%' or arp.displayname0 like '%Microsoft Office Enterprise 2010%' or arp.displayname0 like '%Microsoft Office Professional%2010%' or arp.displayname0 like 'Microsoft Office 2000%' or arp.displayname0 like 'Microsoft Office XP%')and arp.displayname0 not like '%update%'  and arp.displayname0 not like '%Microsoft Office XP Web Components' and v_r_system.operating_system_name_and0 not like '%server%' and (InstallDate0 not like 'NULL')
    group BY arp.DisplayName0,  arp.version0
    ORDER  BY arp.DisplayName0,  arp.version0

    Wednesday, May 30, 2012 4:36 AM
  • Thanks for the response.

    This query gives me the following result.

    I also want to see the computer names where the office is installed in the same query, please suggest.

    Regards,

    Maqsood


    Maqsood Mohammed Senior Systems Engineer MCITP-Enterprise Admin & ITILv3 Foundation Certified

    Wednesday, May 30, 2012 10:21 AM
  • SELECT     dbo.v_R_System.Name0, dbo.v_GS_OPERATING_SYSTEM.Caption0 AS [Operating System],
                          dbo.v_GS_OPERATING_SYSTEM.CSDVersion0 AS [OS Service Pack], arp.DisplayName0,
                          CASE WHEN arp.version0 LIKE '11.0.6361.0' THEN 'SP1' WHEN arp.version0 LIKE '11.0.7969.0' THEN 'SP2' WHEN arp.version0 LIKE '11.0.8173.0' THEN 'SP3' WHEN
                           arp.version0 LIKE '12.0.6215.1000' THEN 'SP1' WHEN arp.version0 LIKE '12.0.6425.1000' THEN 'SP2' WHEN arp.version0 LIKE '14.0.6029.1000' THEN 'SP1' ELSE '' END
                           AS 'Service Pack', arp.Version0
    FROM         dbo.v_Add_Remove_Programs AS arp INNER JOIN
                          dbo.v_R_System ON arp.ResourceID = dbo.v_R_System.ResourceID INNER JOIN
                          dbo.v_RA_System_SMSInstalledSites AS ASSG ON dbo.v_R_System.ResourceID = ASSG.ResourceID INNER JOIN
                          dbo.v_GS_OPERATING_SYSTEM ON dbo.v_R_System.ResourceID = dbo.v_GS_OPERATING_SYSTEM.ResourceID
    WHERE     (arp.DisplayName0 LIKE '%Microsoft Office%edition%' OR
                          arp.DisplayName0 LIKE '%Microsoft Office Standard 2007%' OR
                          arp.DisplayName0 LIKE '%Microsoft Office Enterprise 2007%' OR
                          arp.DisplayName0 LIKE '%Microsoft Office Professional%2007%' OR
                          arp.DisplayName0 LIKE '%Microsoft Office Standard 2010%' OR
                          arp.DisplayName0 LIKE '%Microsoft Office Enterprise 2010%' OR
                          arp.DisplayName0 LIKE '%Microsoft Office Professional%2010%' OR
                          arp.DisplayName0 LIKE 'Microsoft Office 2000%' OR
                          arp.DisplayName0 LIKE 'Microsoft Office XP%') AND (arp.DisplayName0 NOT LIKE '%update%') AND
                          (arp.DisplayName0 NOT LIKE '%Microsoft Office XP Web Components') AND (dbo.v_R_System.Operating_System_Name_and0 NOT LIKE '%server%') AND
                          (arp.InstallDate0 NOT LIKE 'NULL')
    ORDER BY dbo.v_R_System.Name0, arp.DisplayName0, arp.Version0
    Wednesday, May 30, 2012 10:45 AM
  • Perfect.

    That is exactly what i am was looking for...

    Thanks,

    Maqsood


    Maqsood Mohammed Senior Systems Engineer MCITP-Enterprise Admin & ITILv3 Foundation Certified

    • Proposed as answer by Syedasad89 Monday, September 3, 2018 5:19 AM
    • Unproposed as answer by Syedasad89 Monday, September 3, 2018 5:19 AM
    Wednesday, May 30, 2012 11:24 AM
  • I need to run another report basically i want to know all the machines which are running Symantec End Point Protection and which are not.

    Regards,

    Maqsood


    Maqsood Mohammed Senior Systems Engineer MCITP-Enterprise Admin & ITILv3 Foundation Certified

    Wednesday, May 30, 2012 11:40 AM
  • try this http://eskonr.com/2011/02/sccm-report-for-computers-doesnt-have-software-installed/

    Please click on "vote as Helpful" if you feel this post helpful to you.

    Eswar Koneti | My Tech blog: www.eskonr.com | Linkedin: Eswar Koneti

    Wednesday, May 30, 2012 2:51 PM
  • I also want to see the computer names where the office is installed in the same query, please suggest.

    Regards,

    Maqsood


    Maqsood Mohammed Senior Systems Engineer MCITP-Enterprise Admin & ITILv3 Foundation Certified

    Try this to get what version the computers has in simplified way http://eskonr.com/2012/05/sccm-linked-report-ms-office-version-with-service-pack-installed-machines/

    Please click on "vote as Helpful" if you feel this post helpful to you.

    Eswar Koneti | My Tech blog: www.eskonr.com | Linkedin: Eswar Koneti

    Wednesday, May 30, 2012 4:07 PM
  • Hi Eswar,

    Do i need to modify the query to meet our requirement? If yes, please suggest.

    Regards,
    Maqsood


    Maqsood Mohammed Senior Systems Engineer MCITP-Enterprise Admin & ITILv3 Foundation Certified

    Thursday, May 31, 2012 5:43 AM
  • No you dont have to do anything but you would require to link the report as given in the post.(take of fancy quotes)


    Please click on "vote as Helpful" if you feel this post helpful to you.

    Eswar Koneti | My Tech blog: www.eskonr.com | Linkedin: Eswar Koneti

    Friday, June 1, 2012 1:41 PM
  • Thanks Eswar.

    Maqsood Mohammed Senior Systems Engineer MCITP-Enterprise Admin & ITILv3 Foundation Certified

    Sunday, June 3, 2012 7:03 AM
  • Thank you very much. Needed something like this today.

    Tuesday, December 15, 2015 3:25 PM
  • Dear Maqsood,

    this query is fine, if it can be limited to collection then we can pull collections based reports.

    Kindly help.

    Thanks,

    Bharath

    Thursday, August 11, 2016 4:12 AM
  • Thank you so much!!! Nice query. It's that i was looking all day
    Wednesday, July 5, 2017 11:00 PM
  • SELECT     dbo.v_R_System.Name0, dbo.v_GS_OPERATING_SYSTEM.Caption0 AS [Operating System],
                          dbo.v_GS_OPERATING_SYSTEM.CSDVersion0 AS [OS Service Pack], arp.DisplayName0,
                          CASE WHEN arp.version0 LIKE '11.0.6361.0' THEN 'SP1' WHEN arp.version0 LIKE '11.0.7969.0' THEN 'SP2' WHEN arp.version0 LIKE '11.0.8173.0' THEN 'SP3' WHEN
                           arp.version0 LIKE '12.0.6215.1000' THEN 'SP1' WHEN arp.version0 LIKE '12.0.6425.1000' THEN 'SP2' WHEN arp.version0 LIKE '14.0.6029.1000' THEN 'SP1' ELSE '' END
                           AS 'Service Pack', arp.Version0
    FROM         dbo.v_Add_Remove_Programs AS arp INNER JOIN
                          dbo.v_R_System ON arp.ResourceID = dbo.v_R_System.ResourceID INNER JOIN
                          dbo.v_RA_System_SMSInstalledSites AS ASSG ON dbo.v_R_System.ResourceID = ASSG.ResourceID INNER JOIN
                          dbo.v_GS_OPERATING_SYSTEM ON dbo.v_R_System.ResourceID = dbo.v_GS_OPERATING_SYSTEM.ResourceID
    WHERE     (arp.DisplayName0 LIKE '%Microsoft Office%edition%' OR
                          arp.DisplayName0 LIKE '%Microsoft Office Standard 2007%' OR
                          arp.DisplayName0 LIKE '%Microsoft Office Enterprise 2007%' OR
                          arp.DisplayName0 LIKE '%Microsoft Office Professional%2007%' OR
                          arp.DisplayName0 LIKE '%Microsoft Office Standard 2010%' OR
                          arp.DisplayName0 LIKE '%Microsoft Office Enterprise 2010%' OR
                          arp.DisplayName0 LIKE '%Microsoft Office Professional%2010%' OR
                          arp.DisplayName0 LIKE 'Microsoft Office 2000%' OR
                          arp.DisplayName0 LIKE 'Microsoft Office XP%') AND (arp.DisplayName0 NOT LIKE '%update%') AND
                          (arp.DisplayName0 NOT LIKE '%Microsoft Office XP Web Components') AND (dbo.v_R_System.Operating_System_Name_and0 NOT LIKE '%server%') AND
                          (arp.InstallDate0 NOT LIKE 'NULL')
    ORDER BY dbo.v_R_System.Name0, arp.DisplayName0, arp.Version0
    Thank you so much
    Tuesday, November 27, 2018 4:25 PM