none
Duplicate rows when finding PC's with installed software

    Question

  • Hi All,

    I am trying to write a report for our organization in preperation for our Windows 7 upgrade that will find all PC's that have software installed that we have not yet verified as compatible with the new OS (unfortunately we have a few!!!). I have identified the applications already through testing but what I want to do now is to produce a report showing me a list of PC's that have incompatible software installed and then for the colums to show the specific incompatible software installed on that system.

    I have written a report that for the most part achieves this, but the issue is that when a PC has multiple pieces of software installed then it produces multiple rows for that PC with an astrix in just one colum per row. What I would like is to just have a single row per system but then all or the incompatible software showing in multiple colums. I was hoping that the SELECT DISTINCT command would solve this but I can see it is just returning each software item rather than each system.

    I hope this makes sense and I have attached my current report below.

    Thank you in advance for any advice given

    SELECT DISTINCT
                          TOP (100) PERCENT sys.Netbios_Name0, sys.User_Domain0, sys.User_Name0, dbo.v_GS_OPERATING_SYSTEM.Caption0 AS [Operating System Caption],
                          CASE WHEN arp.DisplayName0 = 'Application1' THEN '*' ELSE ' ' END AS [Application1],
                          CASE WHEN arp.DisplayName0 = 'Application2' THEN '*' ELSE ' ' END AS [Application2]
    FROM         dbo.v_R_System AS sys INNER JOIN
                          dbo.v_Add_Remove_Programs AS arp ON sys.ResourceID = arp.ResourceID INNER JOIN
                          dbo.v_FullCollectionMembership AS fcm ON sys.ResourceID = fcm.ResourceID INNER JOIN
                          dbo.v_GS_OPERATING_SYSTEM ON sys.ResourceID = dbo.v_GS_OPERATING_SYSTEM.ResourceID
    WHERE     (arp.DisplayName0 = 'Application1' OR
                          arp.DisplayName0 = 'Application2') AND (dbo.v_GS_OPERATING_SYSTEM.Caption0 = 'Microsoft Windows XP Professional')
    ORDER BY sys.Netbios_Name0

    Friday, January 24, 2014 11:18 AM

Answers

All replies

  • This is probably caused by your multiple inner joins. Why are you joining v_FullCollectionMembership in the query? You're never using any information from it but it is certainly going to return multiple results -- one for every collection that the system is a member of.

    Jason | http://blog.configmgrftw.com

    Friday, January 24, 2014 2:56 PM
    Moderator
  • Hi Jason,

    Thanks for pointing that out. I have removed v_FullCollectionMembership from the query as I'd overlooked the fact I wasnt using it anymore (it was in there for another colum I considered adding but decided against).

    I've also removed v_GS_OPERATING_SYSTEM as I was only calling the OS name from that for a filter and could just use Operating_System_Name_and0 instead.

    Unfortunatley though I still get the same issue

    My query is now:

    SELECT DISTINCT
                          TOP (100) PERCENT sys.Netbios_Name0, sys.Operating_System_Name_and0, sys.User_Domain0, sys.User_Name0,
                          CASE WHEN arp.DisplayName0 = 'Application1' THEN '*' ELSE ' ' END AS [Application1],
                          CASE WHEN arp.DisplayName0 = 'Application2' THEN '*' ELSE ' ' END AS [Application2]
    FROM         dbo.v_R_System AS sys INNER JOIN
                          dbo.v_Add_Remove_Programs AS arp ON sys.ResourceID = arp.ResourceID
    WHERE     (arp.DisplayName0 = 'Application1' OR
                          arp.DisplayName0 = 'Application2') AND (sys.Operating_System_Name_and0 = 'Microsoft Windows NT Workstation 5.1')
    ORDER BY sys.Netbios_Name0

    Thanks

    Friday, January 24, 2014 4:54 PM
  • Try this.

    http://tinyurl.com/lllcpnh


    http://www.enhansoft.com/


    Friday, January 24, 2014 7:11 PM
    Moderator
  • Thank you Gareth,

    I took me a while to figure out how it was working to enable me to add application 3,4,5 etc but its done the trick now.

    Thanks again

    Andrew

    Saturday, January 25, 2014 10:51 AM