none
How to bifurcate clients having MS Office 2010/2007/2003 and to put further bifurcate on Office products

    Question

  • Hi,

    I want to create a report which may shows List of clients having :

    MS Office 2010                MS Office 2007              MS Office 2003

    - MS Access 2010           - MS Access 2007           - MS Access 2003

    - MS Excel 2010              - MS Excel 2007             - MS Access 2003 

    and similarly for other Office products as well.

    Thursday, October 17, 2013 4:52 AM

Answers

  • Oops there is a mistake in the previous query. You should use 'or' instead of 'and'

    SELECT b.Netbios_Name0, 
    b.User_Name0, 
    CASE WHEN a.FileVersion LIKE '11.%' THEN 'Office 2003' 
    WHEN a.FileVersion LIKE '12.%' THEN 'Office 2007' 
    WHEN a.FileVersion LIKE '14.%' THEN 'Office 2010' 
    ELSE 'NULL' 
    END AS 'Office Version', 
    a.FileName,
    a.FileVersion
    FROM v_GS_SoftwareFile a 
    JOIN v_R_System b ON a.ResourceID = b.ResourceID 
    WHERE (a.FileName = 'excel.exe' or a.FileName = 'msaccess.exe') 
    GROUP BY b.Netbios_Name0, b.User_Name0, a.FileName, a.FileVersion 
    ORDER BY b.Netbios_Name0
    Use this.. Note that you may get repeated machine names if there are multiple updates of a single version is installed on the machine.

    Link to the query: http://scorpits.blogspot.com/2013/10/custom-sql-query-to-report-ms-excel-and.html


    ~ Rajeesh M | My Tech Blog: ScorpITs | Please remember to 'Mark as Answer' or 'Vote as Helpful' on the post that helps you. It helps others reading the thread; recognizes useful contributions.


    • Marked as answer by Ananya.S Thursday, October 17, 2013 10:49 AM
    • Edited by ucrajee Thursday, October 17, 2013 12:57 PM link to query
    Thursday, October 17, 2013 9:37 AM

All replies

  • Hi,

    Check this one and let us know if it helps.

    SELECT b.Netbios_Name0, 
    b.User_Name0, 
    CASE WHEN a.FileVersion LIKE '11.%' THEN 'Office 2003' 
    WHEN a.FileVersion LIKE '12.%' THEN 'Office 2007' 
    WHEN a.FileVersion LIKE '14.%' THEN 'Office 2010' 
    ELSE 'NULL' 
    END AS 'Office Version', 
    a.FileName, 
    a.FileVersion, 
    a.FilePath 
    FROM v_GS_SoftwareFile a 
    JOIN v_R_System b ON a.ResourceID = b.ResourceID 
    WHERE (FileName = 'excel.exe' and FileName = 'msaccess.exe') 
    GROUP BY b.Netbios_Name0, b.User_Name0, a.FileName, a.FileVersion, a.FilePath 
    ORDER BY b.Netbios_Name0 



    ~ Rajeesh M | My Tech Blog: ScorpITs | Please remember to 'Mark as Answer' or 'Vote as Helpful' on the post that helps you. It helps others reading the thread; recognizes useful contributions.





    • Edited by ucrajee Thursday, October 17, 2013 6:12 AM updated the query
    Thursday, October 17, 2013 5:49 AM
  • Updated the above query with 'msaccess.exe' instead of 'access.exe'.

    ~ Rajeesh M | My Tech Blog: ScorpITs | Please remember to 'Mark as Answer' or 'Vote as Helpful' on the post that helps you. It helps others reading the thread; recognizes useful contributions.

    Thursday, October 17, 2013 6:14 AM
  • Hi Rajeesh,

    Dnt know why i am getting " No matching records could be found."

    Pls help


    • Edited by Ananya.S Thursday, October 17, 2013 6:58 AM
    Thursday, October 17, 2013 6:58 AM
  • Hi ANanya,

    Were you able to run any default reports? Check if the Hardware Inventory and Software Inventory Client Agent is enabled for the SCCM server.


    ~ Rajeesh M | My Tech Blog: ScorpITs | Please remember to 'Mark as Answer' or 'Vote as Helpful' on the post that helps you. It helps others reading the thread; recognizes useful contributions.

    Thursday, October 17, 2013 7:54 AM
  • Hi,

    Hardware Inventory and Software Inventory are enabled.

    I was able to create a report usng this link

    http://www.myitforum.com/forums/SCCM-sql-queries-for-reporting-Microsoft-Office-products-m223552.aspx

    but it doesn't shows me the other Office products.

    I want to show all the Office products.


    • Edited by Ananya.S Thursday, October 17, 2013 8:01 AM
    Thursday, October 17, 2013 8:00 AM
  • Oops there is a mistake in the previous query. You should use 'or' instead of 'and'

    SELECT b.Netbios_Name0, 
    b.User_Name0, 
    CASE WHEN a.FileVersion LIKE '11.%' THEN 'Office 2003' 
    WHEN a.FileVersion LIKE '12.%' THEN 'Office 2007' 
    WHEN a.FileVersion LIKE '14.%' THEN 'Office 2010' 
    ELSE 'NULL' 
    END AS 'Office Version', 
    a.FileName,
    a.FileVersion
    FROM v_GS_SoftwareFile a 
    JOIN v_R_System b ON a.ResourceID = b.ResourceID 
    WHERE (a.FileName = 'excel.exe' or a.FileName = 'msaccess.exe') 
    GROUP BY b.Netbios_Name0, b.User_Name0, a.FileName, a.FileVersion 
    ORDER BY b.Netbios_Name0
    Use this.. Note that you may get repeated machine names if there are multiple updates of a single version is installed on the machine.

    Link to the query: http://scorpits.blogspot.com/2013/10/custom-sql-query-to-report-ms-excel-and.html


    ~ Rajeesh M | My Tech Blog: ScorpITs | Please remember to 'Mark as Answer' or 'Vote as Helpful' on the post that helps you. It helps others reading the thread; recognizes useful contributions.


    • Marked as answer by Ananya.S Thursday, October 17, 2013 10:49 AM
    • Edited by ucrajee Thursday, October 17, 2013 12:57 PM link to query
    Thursday, October 17, 2013 9:37 AM
  • Thanks a ton Rajeesh !!
    Thursday, October 17, 2013 10:48 AM