myITforum and Windows IT Pro Forums

 SQL Assistance

Author Message
gwenger547

  • Total Posts : 4
  • Scores: 0
  • Reward points : 29470
  • Joined: 8/23/2006
  • Status: offline
SQL Assistance Friday, July 25, 2014 12:06 PM (permalink)
0
We are attempting to gather Autodesk information from our computers. Currently we have modified the mof to gather just two of their products and will expand our data collection once we can get a valid output report.
 
This is what we find using Resource Explorer on a single computer (would have been nice to post the pic here). This data is also what we expected to find. i.e. Nothing for the x86 installation (as product is not installed) and all required data on the x64 installation.
 
 CName
     Hardware
        Autodesk            [KeyName]        Autodesk Inventor 2012
                                                          AutoCAD Mechanical 2012
        Autodesk_64       [KeyName]        Autodesk Inventor 2012
                                                          AutoCAD Mechanical 2012
                                  [ProductName]  Autodesk Inventor 2012
                                                          AutoCAD Mechanical 2012
                                  [Release]           16.1.19000.0000
                                                           16.0.49.0
                                  [SerialNumber]   392-xxx
                                                           392-xxx
                                  [Type]                1
                                                            1
 
However our SQL query is providing duplicate entries on every Autodesk product and seem to be filling in the Type column with the last known value, even when we haven’t gathered the inventory for that product yet.  
 
resourceid      Computer Name        Computer Description ARP Display Name                Release          Product Name                Serial Number          Type        LastHWScan
16777648        CName                0P64 - Precision T3500 Autodesk Inventor 2012          16.1.19000.0000  Autodesk Inventor 2012      392-xxx                Network        03:23.0
16777648        CName                0P64 - Precision T3500 AutoCAD Mechanical 2012         16.0.49.0  AutoCAD Mechanical 2012     392-xxx                Network        03:23.0
16777648        CName                0P64 - Precision T3500 AutoCAD Mechanical 2012                                                                                NULL        03:23.0
16777648        CName                0P64 - Precision T3500 AutoCAD Mechanical 2013                                                                                NULL        03:23.0
16777648        CName                0P64 - Precision T3500 AutoCAD Mechanical 2013                                                                             Network        03:23.0
16777648        CName                0P64 - Precision T3500 Autodesk Backburner 2012.0.0                                                                           NULL        03:23.0
16777648        CName                0P64 - Precision T3500 Autodesk Backburner 2012.0.0                                                                        Network        03:23.0
16777648        CName                0P64 - Precision T3500 Autodesk Design Review 2013                                                                            NULL        03:23.0
16777648        CName                0P64 - Precision T3500 Autodesk Design Review 2013                                                                         Network        03:23.0
16777648        CName                0P64 - Precision T3500 Autodesk Inventor 2012                                                                                 NULL        03:23.0
 
Here is the SQL Query used
SELECT DISTINCT
  sys.resourceid,
  sys.Netbios_Name0 [Computer Name],
  OS.Description0 [Computer Description],
  ARP.DisplayName0 [ARP Display Name],
  [Release] = IsNull((SELECT
                        Release0
                      FROM
                        v_GS_LEG_Autodesk0 AA
                      WHERE
                        SYS.ResourceID = AA.ResourceID AND
                        ARP.DisplayName0 = AA.ProductName0),''),
  [Product Name] = IsNull((SELECT
                             ProductName0
                           FROM
                             v_GS_LEG_Autodesk0 AA
                           WHERE
                             SYS.ResourceID = AA.ResourceID AND
                             ARP.DisplayName0 = AA.ProductName0),''),
  [Serial Number] = IsNull((SELECT
                              SerialNumber0
                            FROM
                              v_GS_LEG_Autodesk0 AA
                            WHERE
                              SYS.ResourceID = AA.ResourceID AND
                              ARP.DisplayName0 = AA.ProductName0),''),
  case AD.Type0
    when 1 then 'Network'
    when 2 then 'Standalone'
    when 3 then 'Multiseat'
  end [Type],
  WS.LastHWScan
FROM
  v_r_system SYS
  left join v_GS_OPERATING_SYSTEM OS on  SYS.ResourceID = OS.ResourceID
  left join v_Add_Remove_Programs ARP on SYS.ResourceID = ARP.ResourceID
  left join v_GS_WORKSTATION_STATUS WS on SYS.ResourceID = WS.ResourceID
  left join v_GS_LEG_Autodesk0 AD on SYS.ResourceID = AD.ResourceID
WHERE
  Publisher0 LIKE 'Autodesk%'
UNION
SELECT
  sys.resourceid,
  sys.Netbios_Name0 [Computer Name],
  OS.Description0 [Computer Description],
  ARP.DisplayName0 [ARP Display Name],
  [Release] = IsNull((SELECT
                        Release0
                      FROM
                        v_GS_LEG_Autodesk_640 AA
                      WHERE
                        SYS.ResourceID = AA.ResourceID AND
                        ARP.DisplayName0 = AA.ProductName0),''),
  [Product Name] = IsNull((SELECT
                             ProductName0
                           FROM
                             v_GS_LEG_Autodesk_640 AA
                           WHERE
                             SYS.ResourceID = AA.ResourceID AND
                             ARP.DisplayName0 = AA.ProductName0),''),
  [Serial Number] = IsNull((SELECT
                              SerialNumber0
                            FROM
                              v_GS_LEG_Autodesk_640 AA
                            WHERE
                              SYS.ResourceID = AA.ResourceID AND
                              ARP.DisplayName0 = AA.ProductName0),''),
   case AD.Type0
     when 1 then 'Network'
     when 2 then 'Standalone'
     when 3 then 'Multiseat'
   end [Type],
   WS.LastHWScan
FROM
  v_r_system SYS
  left join v_GS_OPERATING_SYSTEM OS on  SYS.ResourceID = OS.ResourceID
  left join v_Add_Remove_Programs ARP on SYS.ResourceID = ARP.ResourceID
  left join v_GS_WORKSTATION_STATUS WS on SYS.ResourceID = WS.ResourceID
  left join v_GS_LEG_Autodesk_640 AD on SYS.ResourceID = AD.ResourceID
WHERE
  Publisher0 LIKE 'Autodesk%'
ORDER BY
  [Product Name] desc
<message edited by gwenger547 on Friday, July 25, 2014 12:19 PM>
 
#1
    gjones

    • Total Posts : 2532
    • Scores: 142
    • Reward points : 150160
    • Joined: 6/5/2001
    • Location: Ottawa, Ontario, Canada
    • Status: offline
    Re:SQL Assistance Friday, July 25, 2014 12:29 PM (permalink)
    0
    Is there a reason why you use select statement within the main query vs. using the view that you already have joined in the main query?
     
    #2
      gjones

      • Total Posts : 2532
      • Scores: 142
      • Reward points : 150160
      • Joined: 6/5/2001
      • Location: Ottawa, Ontario, Canada
      • Status: offline
      Re:SQL Assistance Friday, July 25, 2014 12:43 PM (permalink)
      0
      Try this. Keep in mind that I wrote this free hand, so that might typo in it.
       
          
       SELECT DISTINCT 
       sys.resourceid, 
       sys.Netbios_Name0 [Computer Name], 
       OS.Description0 [Computer Description], 
       ARP.DisplayName0 [ARP Display Name], 
       LAD.Release0 as 'Release',
       LAD.ProductName0 as 'Product Name',
       LAD.ProductName0 as 'Serial Number',
       case LAD.Type0 
        when 1 then 'Network' 
        when 2 then 'Standalone' 
        when 3 then 'Multiseat' 
       end as 'Type', 
       WS.LastHWScan 
      FROM 
       dbo.v_R_System SYS 
       left join dbo.v_GS_OPERATING_SYSTEM OS on  SYS.ResourceID = OS.ResourceID 
       left join dbo.v_Add_Remove_Programs ARP on SYS.ResourceID = ARP.ResourceID 
       left join dbo.v_GS_WORKSTATION_STATUS WS on SYS.ResourceID = WS.ResourceID 
       left join dbo.v_GS_LEG_Autodesk0 LAD on SYS.ResourceID = LAD.ResourceID and ARP.DisplayName0 = LAD.ProductName0
      WHERE 
       Publisher0 LIKE 'Autodesk%'
       
       

       
      #3
        gwenger547

        • Total Posts : 4
        • Scores: 0
        • Reward points : 29470
        • Joined: 8/23/2006
        • Status: offline
        Re:SQL Assistance Friday, July 25, 2014 2:39 PM (permalink)
        0
        Thank-you. I'm not sure if Autodesk ARP name and Program Name always match so we will find out as more data is entered.  
        Your query only gathers the x86 data. Can we gather the x64 data ( left join dbo.v_GS_LEG_Autodesk_640 LAD on SYS.ResourceID = LAD.ResourceID and ARP.DisplayName0 = LAD.ProductName0 ) in the same query without duplicating rows?
        [/style]
        <message edited by gwenger547 on Friday, July 25, 2014 2:40 PM>
         
        #4
          gjones

          • Total Posts : 2532
          • Scores: 142
          • Reward points : 150160
          • Joined: 6/5/2001
          • Location: Ottawa, Ontario, Canada
          • Status: offline
          Re:SQL Assistance Friday, July 25, 2014 3:43 PM (permalink)
          0
          I only made my query directly on what you had listed, nothing more. I assumed that you had check that ARP.DisplayName0 = LAD.ProductName0.  I don’t have the views so there is no way for me to test this.
           
          You will still need to do the union between the two Autodesk queries. The first trick is to get rid of the dups, sorry I didn’t make that clear.  
           
          #5
            Online Bookmarks Sharing: Share/Bookmark

            Jump to:

            Current active users

            There are 0 members and 2 guests.

            Icon Legend and Permission

            • New Messages
            • No New Messages
            • Hot Topic w/ New Messages
            • Hot Topic w/o New Messages
            • Locked w/ New Messages
            • Locked w/o New Messages
            • Read Message
            • Post New Thread
            • Reply to message
            • Post New Poll
            • Submit Vote
            • Post reward post
            • Delete my own posts
            • Delete my own threads
            • Rate post

            2000-2014 ASPPlayground.NET Forum Version 3.9