none
Report of all systems with IE and Office version

    Question

  • Hello,

    I'm trying to get this report to work and banging my head against the wall for several hours. A similar report worked at another client, but perhaps that was because multiple versions of Office weren't installed on the systems? I'm not sure, so it's definitely a question for the SQL gurus. If we can get it to work, this is a great report for techs or management for what is in the environment. The idea is to show all the systems in the environment, their hardware, their office version(s) and their IE version.

    I'm expecting anywhere from 8000 - 14000 rows returned on this, but am currently getting way more than that with many duplicates. Maybe I just have it formatted wrong. Please take a look and lend a hand if you can. Here is the SQL code. Thank you!

    SELECT     TOP (100) PERCENT SMS_R_System.Name0 AS System_Name, SMS_G_System_OPERATING_SYSTEM.Description0 AS [Primary User], 
                          SMS_R_System.User_Name0 AS [Last Logon], SMS_R_System.AD_Site_Name0 AS AD_Site_Name, 
                          ___System_WORKSTATION_STATUS0.LastHWScan AS [Last HW Scan], SMS_G_System_OPERATING_SYSTEM.LastBootUpTime0 AS [Last Boot Time], 
                          SMS_G_System_COMPUTER_SYSTEM.Manufacturer00 AS __Manufacturer__, SMS_G_System_COMPUTER_SYSTEM.Model0 AS Model, 
                          SMS_G_System_PC_BIOS.SerialNumber00 AS [Serial Number], SMS_G_System_PROCESSOR.Name0 AS Processor, 
                          SMS_G_System_X86_PC_MEMORY.TotalPhysicalMemory0 AS [Total Physical Menory], 
                          SMS_G_System_X86_PC_MEMORY.TotalVirtualMemory0 AS [Total Virtual Menory], SMS_G_System_PROCESSOR.AddressWidth00 AS [OS 32/64BIT], 
                          SMS_G_System_OPERATING_SYSTEM.Version0 AS [OS Version], SMS_G_System_OPERATING_SYSTEM.CSDVersion0 AS [Service Pack], 
                          __System_ADD_REMOVE_PROGRAMS0.DisplayName00 AS [Office Version], SMS_G_System_SoftwareFile.FileVersion AS [IE Version]
    FROM         dbo.System_DISC AS SMS_R_System INNER JOIN
                          dbo.Operating_System_DATA AS SMS_G_System_OPERATING_SYSTEM ON SMS_G_System_OPERATING_SYSTEM.MachineID = SMS_R_System.ItemKey INNER JOIN
                          dbo.WorkstationStatus_DATA AS ___System_WORKSTATION_STATUS0 ON ___System_WORKSTATION_STATUS0.MachineID = SMS_R_System.ItemKey INNER JOIN
                          dbo.PC_BIOS_DATA AS SMS_G_System_PC_BIOS ON SMS_G_System_PC_BIOS.MachineID = SMS_R_System.ItemKey INNER JOIN
                          dbo.Computer_System_DATA AS SMS_G_System_COMPUTER_SYSTEM ON SMS_G_System_COMPUTER_SYSTEM.MachineID = SMS_R_System.ItemKey INNER JOIN
                          dbo.Processor_DATA AS SMS_G_System_PROCESSOR ON SMS_G_System_PROCESSOR.MachineID = SMS_R_System.ItemKey INNER JOIN
                          dbo.PC_Memory_DATA AS SMS_G_System_X86_PC_MEMORY ON SMS_G_System_X86_PC_MEMORY.MachineID = SMS_R_System.ItemKey INNER JOIN
                          dbo.System_Enclosure_DATA AS SMS_G_System_SYSTEM_ENCLOSURE ON 
                          SMS_G_System_SYSTEM_ENCLOSURE.MachineID = SMS_R_System.ItemKey INNER JOIN
                          dbo.vSMS_G_System_SoftwareFile AS SMS_G_System_SoftwareFile ON SMS_G_System_SoftwareFile.ClientId = SMS_R_System.ItemKey INNER JOIN
                          dbo.Add_Remove_Programs_DATA AS __System_ADD_REMOVE_PROGRAMS0 ON 
                          __System_ADD_REMOVE_PROGRAMS0.MachineID = SMS_R_System.ItemKey
    WHERE     (__System_ADD_REMOVE_PROGRAMS0.DisplayName00 LIKE 'Microsoft Office Professional%2010') AND (SMS_G_System_SoftwareFile.FileName = 'iexplore.exe') 
                          AND (SMS_G_System_SoftwareFile.FileVersion LIKE '[456789].%') OR
                          (__System_ADD_REMOVE_PROGRAMS0.DisplayName00 LIKE 'Microsoft Office Professional%2007') AND (SMS_G_System_SoftwareFile.FileName = 'iexplore.exe') 
                          AND (SMS_G_System_SoftwareFile.FileVersion LIKE '[456789].%') OR
                          (__System_ADD_REMOVE_PROGRAMS0.DisplayName00 LIKE 'Microsoft Office Professional%2003') AND (SMS_G_System_SoftwareFile.FileName = 'iexplore.exe') 
                          AND (SMS_G_System_SoftwareFile.FileVersion LIKE '[456789].%') OR
                          (__System_ADD_REMOVE_PROGRAMS0.DisplayName00 LIKE 'Microsoft Office Standard%2010') AND (SMS_G_System_SoftwareFile.FileName = 'iexplore.exe') AND 
                          (SMS_G_System_SoftwareFile.FileVersion LIKE '[456789].%') OR
                          (__System_ADD_REMOVE_PROGRAMS0.DisplayName00 LIKE 'Microsoft Office Standard%2007') AND (SMS_G_System_SoftwareFile.FileName = 'iexplore.exe') AND 
                          (SMS_G_System_SoftwareFile.FileVersion LIKE '[456789].%') OR
                          (__System_ADD_REMOVE_PROGRAMS0.DisplayName00 LIKE 'Microsoft Office Standard%2003') AND (SMS_G_System_SoftwareFile.FileName = 'iexplore.exe') AND 
                          (SMS_G_System_SoftwareFile.FileVersion LIKE '[456789].%') OR
                          (__System_ADD_REMOVE_PROGRAMS0.DisplayName00 LIKE 'Microsoft Office%2000 [PS]%') AND (SMS_G_System_SoftwareFile.FileName = 'iexplore.exe') AND 
                          (SMS_G_System_SoftwareFile.FileVersion LIKE '[456789].%')
    ORDER BY System_Name

    Thursday, July 19, 2012 11:48 PM

Answers

  • I re-wrote you query.. Try this. the Null for Offcie will be because they don't have it.

    SELECT distinct
    	R.Name0 AS 'System Name', 
    	OS.Description0 AS 'Primary User', 
    	R.User_Name0 AS 'Last Logon', 
    	R.AD_Site_Name0 AS 'AD Site Name', 
    	WS.LastHWScan AS 'Last HW Scan', 
    	OS.LastBootUpTime0 AS 'Last Boot Time', 
    	CS.Manufacturer0 AS 'Manufacturer', 
    	CS.Model0 AS 'Model', 
    	BIOS.SerialNumber0 AS 'Serial Number', 
    	Pro.Name0 AS 'Processor', 
    	RAM.TotalPhysicalMemory0 AS 'Total Physical Menory', 
    	RAM.TotalVirtualMemory0 AS 'Total Virtual Menory', 
    	Pro.AddressWidth0 AS 'OS 32/64BIT', 
    	OS.Version0 AS 'OS Version', 
    	OS.CSDVersion0 AS 'Service Pack', 
    	ARP.DisplayName0 AS 'Office Version', 
    	SF.FileVersion AS 'IE Version'
    FROM
    	dbo.v_GS_COMPUTER_SYSTEM as CS
    	left outer join dbo.v_R_System as R ON CS.ResourceID = R.ResourceID 
    	left outer join dbo.v_GS_OPERATING_SYSTEM AS OS ON OS.ResourceID = R.ResourceID 
    	left outer join dbo.v_GS_WORKSTATION_STATUS AS WS ON WS.ResourceID = R.ResourceID 
    	left outer join dbo.v_GS_PC_BIOS AS BIOS ON BIOS.ResourceID = R.ResourceID 
    	left outer join dbo.v_GS_PROCESSOR AS Pro ON Pro.ResourceID = R.ResourceID 
    	left outer join dbo.v_GS_X86_PC_MEMORY AS RAM ON RAM.ResourceID = R.ResourceID 
    	left outer join dbo.v_GS_SoftwareFile AS SF ON SF.ResourceID = R.ResourceID and SF.FileName = 'iexplore.exe' and SF.FilePath LIKE 'C:\Program Files\Internet Explorer\' and SF.FileVersion LIKE '[456789].%'
    	left outer join dbo.v_Add_Remove_Programs AS ARP ON ARP.ResourceID = R.ResourceID and ARP.DisplayName0 LIKE 'Microsoft Office Standard %2010'
    ORDER BY 
    	R.Name0


    http://www.enhansoft.com/

    • Marked as answer by skissel Friday, August 24, 2012 4:06 PM
    Monday, August 20, 2012 6:41 PM
  • Perfect! Thank you!

    Here's the final SQL code, in case anyone is interested:

    SELECT DISTINCT 
                          TOP (100) PERCENT R.Name0 AS 'System Name', OS.Description0 AS 'Primary User', R.User_Name0 AS 'Last Logon', R.AD_Site_Name0 AS 'AD Site Name', 
                          WS.LastHWScan AS 'Last HW Scan', OS.LastBootUpTime0 AS 'Last Boot Time', CS.Manufacturer0 AS 'Manufacturer', CS.Model0 AS 'Model', 
                          BIOS.SerialNumber0 AS 'Serial Number', Pro.Name0 AS 'Processor', RAM.TotalPhysicalMemory0 AS 'Total Physical Menory', 
                          RAM.TotalVirtualMemory0 AS 'Total Virtual Menory', Pro.AddressWidth0 AS 'OS 32/64BIT', OS.Version0 AS 'OS Version', OS.CSDVersion0 AS 'Service Pack', 
                          ARP.DisplayName0 AS 'Office Version', SF.FileVersion AS 'IE Version'
    FROM         dbo.v_GS_COMPUTER_SYSTEM AS CS LEFT OUTER JOIN
                          dbo.v_R_System AS R ON CS.ResourceID = R.ResourceID LEFT OUTER JOIN
                          dbo.v_GS_OPERATING_SYSTEM AS OS ON OS.ResourceID = R.ResourceID LEFT OUTER JOIN
                          dbo.v_GS_WORKSTATION_STATUS AS WS ON WS.ResourceID = R.ResourceID LEFT OUTER JOIN
                          dbo.v_GS_PC_BIOS AS BIOS ON BIOS.ResourceID = R.ResourceID LEFT OUTER JOIN
                          dbo.v_GS_PROCESSOR AS Pro ON Pro.ResourceID = R.ResourceID LEFT OUTER JOIN
                          dbo.v_GS_X86_PC_MEMORY AS RAM ON RAM.ResourceID = R.ResourceID LEFT OUTER JOIN
                          dbo.v_GS_SoftwareFile AS SF ON SF.ResourceID = R.ResourceID AND SF.FileName = 'iexplore.exe' AND SF.FilePath LIKE 'C:\Program Files\Internet Explorer\' AND 
                          SF.FileVersion LIKE '[456789].%' LEFT OUTER JOIN
                          dbo.v_Add_Remove_Programs AS ARP ON ARP.ResourceID = R.ResourceID AND (ARP.DisplayName0 LIKE 'Microsoft Office Standard %2010' OR
                          ARP.DisplayName0 LIKE 'Microsoft Office Professional %2010' OR
                          ARP.DisplayName0 LIKE 'Microsoft Office Standard %2007' OR
                          ARP.DisplayName0 LIKE 'Microsoft Office Professional %2007' OR
                          ARP.DisplayName0 LIKE 'Microsoft Office Standard %2003' OR
                          ARP.DisplayName0 LIKE 'Microsoft Office Professional %2003' OR
                          ARP.DisplayName0 LIKE 'Microsoft Office %2000 [PS]%')
    ORDER BY 'System Name'

    • Marked as answer by skissel Friday, August 24, 2012 4:06 PM
    Friday, August 24, 2012 4:06 PM

All replies

  • Replace your "Select" section with “SELECT     Distinct”

    On a side note you should NOT query the table directly, you should always query the view.  


    http://www.enhansoft.com/

    Friday, July 20, 2012 12:00 PM
  • Thanks Garth! Done, but it's still showing several duplicate records. Just to try to narrow it down, I used a single where clause:

    WHERE     (__System_ADD_REMOVE_PROGRAMS0.DisplayName00 LIKE 'Microsoft Office Professional%2010') AND (SMS_G_System_SoftwareFile.FileName = 'iexplore.exe') 
                          AND (SMS_G_System_SoftwareFile.FileVersion LIKE '[456789].%')


    As an example of what it's outputting is , it will display Microsoft Office Professional Plus 2010 for the Office version, but the IE version will have a different version on each row. So in the 1st row of the IE column, it will display 7.00.6000.2107... The 2nd row of the IE comlumn will display 7.00.6000.2111... and the 3rd row of the IE column will display 8.00.6001.1870... When I check the IE version on the system, the only one that is there is 8.00.6001.18702. So why is it retrieving previous records as well?
    Friday, July 20, 2012 3:52 PM
  • So I clean up the query, converted it to use the views, added File path as a column, allow th equeyr to see both x86 and x64 bit data. Based on my lab it is file path that will show the alternate version of IE installed on the PC.

    SELECT distinct
    	R.Name0 AS 'System Name', 
    	OS.Description0 AS 'Primary User', 
    	R.User_Name0 AS 'Last Logon', 
    	R.AD_Site_Name0 AS 'AD Site Name', 
    	WS.LastHWScan AS 'Last HW Scan', 
    	OS.LastBootUpTime0 AS 'Last Boot Time', 
    	CS.Manufacturer0 AS 'Manufacturer', 
    	CS.Model0 AS 'Model', 
    	BIOS.SerialNumber0 AS 'Serial Number', 
    	Pro.Name0 AS 'Processor', 
    	RAM.TotalPhysicalMemory0 AS 'Total Physical Menory', 
    	RAM.TotalVirtualMemory0 AS 'Total Virtual Menory', 
    	Pro.AddressWidth0 AS 'OS 32/64BIT', 
    	OS.Version0 AS 'OS Version', 
    	OS.CSDVersion0 AS 'Service Pack', 
    	ARP.DisplayName0 AS 'Office Version', 
    	SF.FileVersion AS 'IE Version',
    	SF.FilePath as 'Path'
    FROM
    	dbo.v_R_System AS R
    	INNER JOIN dbo.v_GS_OPERATING_SYSTEM AS OS ON OS.ResourceID = R.ResourceID 
    	INNER JOIN dbo.v_GS_WORKSTATION_STATUS AS WS ON WS.ResourceID = R.ResourceID 
    	INNER JOIN dbo.v_GS_PC_BIOS AS BIOS ON BIOS.ResourceID = R.ResourceID 
    	INNER JOIN dbo.v_GS_COMPUTER_SYSTEM AS CS ON CS.ResourceID = R.ResourceID 
    	INNER JOIN dbo.v_GS_PROCESSOR AS Pro ON Pro.ResourceID = R.ResourceID 
    	INNER JOIN dbo.v_GS_X86_PC_MEMORY AS RAM ON RAM.ResourceID = R.ResourceID 
    	INNER JOIN dbo.v_GS_SoftwareFile AS SF ON SF.ResourceID = R.ResourceID 
    	INNER JOIN dbo.v_Add_Remove_Programs AS ARP ON ARP.ResourceID = R.ResourceID
    WHERE     
    	(ARP.DisplayName0 LIKE 'Microsoft Office Standard %2010') 
    	AND ((SF.FileName = 'iexplore.exe')
    	AND (SF.FileVersion LIKE '[456789].%'))
    ORDER BY 
    	R.Name0


    http://www.enhansoft.com/

    Wednesday, July 25, 2012 2:22 AM
  • Thanks Garth! It worked well for the most part. I did need to modify it a bit because I was getting too may results returned for the FilePath in that it would show several rows for a client having IE in C:\Program Files\Internet Explorer\, C:\Program Files (x86)\Internet Explorer\, and C:\Windows\SXS or something along those lines. I simplified it in the query below by just looking for C:\Program Files\Internet Explorer\... while not a perfect representation of the version number, I'm concerned more with the major releases at this point.

    SELECT DISTINCT TOP (100) PERCENT R.Name0 AS 'System Name', OS.Description0 AS [Primary User], R.User_Name0 AS [Last Logon], R.AD_Site_Name0 AS 'AD Site Name', WS.LastHWScan AS [Last HW Scan], OS.LastBootUpTime0 AS [Last Boot Time], CS.Manufacturer0 AS 'Manufacturer', CS.Model0 AS Model, BIOS.SerialNumber0 AS [Serial Number], Pro.Name0 AS Processor, RAM.TotalPhysicalMemory0 AS [Total Physical Menory], RAM.TotalVirtualMemory0 AS [Total Virtual Menory], Pro.AddressWidth0 AS [OS 32/64BIT], OS.Version0 AS [OS Version], OS.CSDVersion0 AS [Service Pack], ARP.DisplayName0 AS [Office Version], SF.FileVersion AS [IE Version] FROM dbo.v_R_System AS R INNER JOIN dbo.v_GS_OPERATING_SYSTEM AS OS ON OS.ResourceID = R.ResourceID INNER JOIN dbo.v_GS_WORKSTATION_STATUS AS WS ON WS.ResourceID = R.ResourceID INNER JOIN dbo.v_GS_PC_BIOS AS BIOS ON BIOS.ResourceID = R.ResourceID INNER JOIN dbo.v_GS_COMPUTER_SYSTEM AS CS ON CS.ResourceID = R.ResourceID INNER JOIN dbo.v_GS_PROCESSOR AS Pro ON Pro.ResourceID = R.ResourceID INNER JOIN dbo.v_GS_X86_PC_MEMORY AS RAM ON RAM.ResourceID = R.ResourceID INNER JOIN dbo.v_GS_SoftwareFile AS SF ON SF.ResourceID = R.ResourceID INNER JOIN dbo.v_Add_Remove_Programs AS ARP ON ARP.ResourceID = R.ResourceID WHERE (ARP.DisplayName0 LIKE 'Microsoft Office Standard %2010') AND (SF.FileName = 'iexplore.exe') AND (SF.FileVersion LIKE '[456789].%') AND (SF.FilePath LIKE 'C:\Program Files\Internet Explorer\') OR (ARP.DisplayName0 LIKE 'Microsoft Office Professional %2010') AND (SF.FileName = 'iexplore.exe') AND (SF.FileVersion LIKE '[456789].%') AND (SF.FilePath LIKE 'C:\Program Files\Internet Explorer\') OR (ARP.DisplayName0 LIKE 'Microsoft Office Standard %2007') AND (SF.FileName = 'iexplore.exe') AND (SF.FileVersion LIKE '[456789].%') AND (SF.FilePath LIKE 'C:\Program Files\Internet Explorer\') OR (ARP.DisplayName0 LIKE 'Microsoft Office Professional %2007') AND (SF.FileName = 'iexplore.exe') AND (SF.FileVersion LIKE '[456789].%') AND (SF.FilePath LIKE 'C:\Program Files\Internet Explorer\') OR (ARP.DisplayName0 LIKE 'Microsoft Office Standard %2003') AND (SF.FileName = 'iexplore.exe') AND (SF.FileVersion LIKE '[456789].%') AND (SF.FilePath LIKE 'C:\Program Files\Internet Explorer\') OR (ARP.DisplayName0 LIKE 'Microsoft Office Professional %2003') AND (SF.FileName = 'iexplore.exe') AND (SF.FileVersion LIKE '[456789].%') AND (SF.FilePath LIKE 'C:\Program Files\Internet Explorer\') OR (ARP.DisplayName0 LIKE 'Microsoft Office %2000 [PS]%') AND (SF.FileName = 'iexplore.exe') AND (SF.FileVersion LIKE '[456789].%') AND (SF.FilePath LIKE 'C:\Program Files\Internet Explorer\') ORDER BY 'System Name'

    One problem with this that I just discovered, however, is that not every system has Office installed (but all systems should have IE installed). Is there a proper way to display both those that do and don't have Office based on the query above? I tried doing something along the lines of:

     (ARP.DisplayName0 LIKE 'Microsoft Office Standard %2010' OR
                          ARP.DisplayName0 IS NULL) AND (SF.FileName = 'iexplore.exe') AND (SF.FileVersion LIKE '[456789].%') AND 
                          (SF.FilePath LIKE 'C:\Program Files\Internet Explorer\')

    But it occasionally returns results where a client has two rows associated with it, and the only difference is that one row is NULL and the other row is Microsoft Office Standard 2010 for the Office Version column.

    Tuesday, July 31, 2012 4:57 PM
  • I re-wrote you query.. Try this. the Null for Offcie will be because they don't have it.

    SELECT distinct
    	R.Name0 AS 'System Name', 
    	OS.Description0 AS 'Primary User', 
    	R.User_Name0 AS 'Last Logon', 
    	R.AD_Site_Name0 AS 'AD Site Name', 
    	WS.LastHWScan AS 'Last HW Scan', 
    	OS.LastBootUpTime0 AS 'Last Boot Time', 
    	CS.Manufacturer0 AS 'Manufacturer', 
    	CS.Model0 AS 'Model', 
    	BIOS.SerialNumber0 AS 'Serial Number', 
    	Pro.Name0 AS 'Processor', 
    	RAM.TotalPhysicalMemory0 AS 'Total Physical Menory', 
    	RAM.TotalVirtualMemory0 AS 'Total Virtual Menory', 
    	Pro.AddressWidth0 AS 'OS 32/64BIT', 
    	OS.Version0 AS 'OS Version', 
    	OS.CSDVersion0 AS 'Service Pack', 
    	ARP.DisplayName0 AS 'Office Version', 
    	SF.FileVersion AS 'IE Version'
    FROM
    	dbo.v_GS_COMPUTER_SYSTEM as CS
    	left outer join dbo.v_R_System as R ON CS.ResourceID = R.ResourceID 
    	left outer join dbo.v_GS_OPERATING_SYSTEM AS OS ON OS.ResourceID = R.ResourceID 
    	left outer join dbo.v_GS_WORKSTATION_STATUS AS WS ON WS.ResourceID = R.ResourceID 
    	left outer join dbo.v_GS_PC_BIOS AS BIOS ON BIOS.ResourceID = R.ResourceID 
    	left outer join dbo.v_GS_PROCESSOR AS Pro ON Pro.ResourceID = R.ResourceID 
    	left outer join dbo.v_GS_X86_PC_MEMORY AS RAM ON RAM.ResourceID = R.ResourceID 
    	left outer join dbo.v_GS_SoftwareFile AS SF ON SF.ResourceID = R.ResourceID and SF.FileName = 'iexplore.exe' and SF.FilePath LIKE 'C:\Program Files\Internet Explorer\' and SF.FileVersion LIKE '[456789].%'
    	left outer join dbo.v_Add_Remove_Programs AS ARP ON ARP.ResourceID = R.ResourceID and ARP.DisplayName0 LIKE 'Microsoft Office Standard %2010'
    ORDER BY 
    	R.Name0


    http://www.enhansoft.com/

    • Marked as answer by skissel Friday, August 24, 2012 4:06 PM
    Monday, August 20, 2012 6:41 PM
  • Perfect! Thank you!

    Here's the final SQL code, in case anyone is interested:

    SELECT DISTINCT 
                          TOP (100) PERCENT R.Name0 AS 'System Name', OS.Description0 AS 'Primary User', R.User_Name0 AS 'Last Logon', R.AD_Site_Name0 AS 'AD Site Name', 
                          WS.LastHWScan AS 'Last HW Scan', OS.LastBootUpTime0 AS 'Last Boot Time', CS.Manufacturer0 AS 'Manufacturer', CS.Model0 AS 'Model', 
                          BIOS.SerialNumber0 AS 'Serial Number', Pro.Name0 AS 'Processor', RAM.TotalPhysicalMemory0 AS 'Total Physical Menory', 
                          RAM.TotalVirtualMemory0 AS 'Total Virtual Menory', Pro.AddressWidth0 AS 'OS 32/64BIT', OS.Version0 AS 'OS Version', OS.CSDVersion0 AS 'Service Pack', 
                          ARP.DisplayName0 AS 'Office Version', SF.FileVersion AS 'IE Version'
    FROM         dbo.v_GS_COMPUTER_SYSTEM AS CS LEFT OUTER JOIN
                          dbo.v_R_System AS R ON CS.ResourceID = R.ResourceID LEFT OUTER JOIN
                          dbo.v_GS_OPERATING_SYSTEM AS OS ON OS.ResourceID = R.ResourceID LEFT OUTER JOIN
                          dbo.v_GS_WORKSTATION_STATUS AS WS ON WS.ResourceID = R.ResourceID LEFT OUTER JOIN
                          dbo.v_GS_PC_BIOS AS BIOS ON BIOS.ResourceID = R.ResourceID LEFT OUTER JOIN
                          dbo.v_GS_PROCESSOR AS Pro ON Pro.ResourceID = R.ResourceID LEFT OUTER JOIN
                          dbo.v_GS_X86_PC_MEMORY AS RAM ON RAM.ResourceID = R.ResourceID LEFT OUTER JOIN
                          dbo.v_GS_SoftwareFile AS SF ON SF.ResourceID = R.ResourceID AND SF.FileName = 'iexplore.exe' AND SF.FilePath LIKE 'C:\Program Files\Internet Explorer\' AND 
                          SF.FileVersion LIKE '[456789].%' LEFT OUTER JOIN
                          dbo.v_Add_Remove_Programs AS ARP ON ARP.ResourceID = R.ResourceID AND (ARP.DisplayName0 LIKE 'Microsoft Office Standard %2010' OR
                          ARP.DisplayName0 LIKE 'Microsoft Office Professional %2010' OR
                          ARP.DisplayName0 LIKE 'Microsoft Office Standard %2007' OR
                          ARP.DisplayName0 LIKE 'Microsoft Office Professional %2007' OR
                          ARP.DisplayName0 LIKE 'Microsoft Office Standard %2003' OR
                          ARP.DisplayName0 LIKE 'Microsoft Office Professional %2003' OR
                          ARP.DisplayName0 LIKE 'Microsoft Office %2000 [PS]%')
    ORDER BY 'System Name'

    • Marked as answer by skissel Friday, August 24, 2012 4:06 PM
    Friday, August 24, 2012 4:06 PM
  • I keep getting syntax error when i input these into the sccm query.

    I tried on few environments, and most of the query posted into TechNet is not working too.

    I believe I'm putting in the queries wrongly.

    Can you advise me how to use these query properly?

    All I did is create new query as per below:

    Friday, August 02, 2013 3:37 PM
  • Its due to SQL vs WQL syntax.  You are using the New Query Wizard when you need to use the New Report Wizard.  Paste the code in there and it should function without issue.  
    Saturday, August 03, 2013 12:09 AM
  • You mean I should use this query in "Reporting" instead of "Query"?
    Monday, August 05, 2013 8:54 PM
  • yes you should be using reporting...

    http://www.enhansoft.com/

    Monday, August 05, 2013 11:28 PM