Client Management Suite

 View Only
  • 1.  Help with Query

    Posted Apr 30, 2013 09:13 PM

    Hi,

    Can someone see the problem with the query below? I suspect it has something to do with the JOINS. We have had issues with getting solid counts and a suggestion was made to use the Inv_HW_Chassis table since each system should have a stamp to indicate if it is a desktop or laptop. I do need to bring data from the other tables so the report looks more complete, but the trigger for the query should be the INV_HW_Chassis table, not vComputer.

    So in addition to a problem with the JOINS do I have the SELECT statement backwards?

    SELECT vComputer.Name AS 'Computer Name', vComputer.[OS Name], vComputer.[OS Revision], vHWComputerSystem.Manufacturer,
    vHWComputerSystem.Model, vHWComputerSystem.[Identifying Number] AS 'Serial Number', vHWProcessor.Model AS 'Processor',
    vHWComputerSystem.[Total Physical Memory (Bytes)] / 1048576 AS 'RAM (MB)', I.Name AS 'Location', vComputer.[User] AS 'Primary Owner'

    FROM vComputer

    LEFT OUTER JOIN ResourceAssociation AS loc ON (loc.ChildResourceGuid = vComputer.Guid OR
    loc.ParentResourceGuid = vComputer.Guid) AND loc.ResourceAssociationTypeGuid = '05DE450F-39EA-4AAE-8C5F-77817889C27C'

    Inv_HW_Chassis AS ch LEFT OUTER JOIN vComputer ON ch._ResourceGuid = vComputer.Guid

    LEFT OUTER JOIN vComputer ON ch._ResourceGuid = vComputer.Guid

    LEFT OUTER JOIN vComputer ON ch._ResourceGuid = vComputer.Guid vItem AS I ON loc.ChildResourceGuid = I.Guid

    LEFT OUTER JOIN vHWProcessor ON vComputer.Guid = vHWProcessor._ResourceGuid

    LEFT OUTER JOIN vHWComputerSystem ON vComputer.Guid = vHWComputerSystem._ResourceGuid

    WHERE (ch.[Chassis Package Type] IS NULL) OR (ch.[Chassis Package Type] = 3) OR (ch.[Chassis Package Type] = 4) OR (ch.[Chassis Package Type] = 5) OR
    (ch.[Chassis Package Type] = 6) OR (ch.[Chassis Package Type] = 7)

    ORDER BY 'Computer Name'


    The query below is what I am trying to incorporate into the one above:

    SELECT ch._ResourceGuid, vComputer.Name, vComputer.[IP Address], vHWComputerSystem.Model, ch.[Chassis Package Type]
    FROM Inv_HW_Chassis AS ch LEFT OUTER JOIN vComputer ON ch._ResourceGuid = vComputer.Guid LEFT OUTER JOIN vHWComputerSystem ON vComputer.Guid = vHWComputerSystem._ResourceGuid
    WHERE (ch.[Chassis Package Type] IS NULL) OR (ch.[Chassis Package Type] = 3) OR (ch.[Chassis Package Type] = 4) OR (ch.[Chassis Package Type] = 5) OR (ch.[Chassis Package Type] = 6) OR (ch.[Chassis Package Type] = 7)



  • 2.  RE: Help with Query

    Posted May 01, 2013 08:29 AM

    The query was a bit mixed up. I cleaned it up and added a few aliases:

        SELECT    vc.Name AS 'Computer Name'
                , vc.[OS Name]
                , vc.[OS Revision]
                , cs.Manufacturer
                , cs.Model
                , cs.[Identifying Number] AS 'Serial Number'
                , pr.Model AS 'Processor'
                , cs.[Total Physical Memory (Bytes)] / 1048576 AS 'RAM (MB)'
                , I.Name AS 'Location'
                , vc.[User] AS 'Primary Owner'
    
    FROM vComputer AS vc
    
    LEFT OUTER JOIN ResourceAssociation AS loc ON (loc.ChildResourceGuid = vc.Guid OR
    loc.ParentResourceGuid = vc.Guid) AND loc.ResourceAssociationTypeGuid = '05DE450F-39EA-4AAE-8C5F-77817889C27C'
    
    LEFT OUTER JOIN Inv_HW_Chassis AS ch ON ch._ResourceGuid = vc.Guid
    
    LEFT OUTER JOIN vItem AS I ON loc.ChildResourceGuid = I.Guid
    
    LEFT OUTER JOIN vHWProcessor AS pr ON vc.Guid = pr._ResourceGuid
    
    LEFT OUTER JOIN vHWComputerSystem AS cs ON vc.Guid = cs._ResourceGuid
    
    WHERE (ch.[Chassis Package Type] IS NULL) OR (ch.[Chassis Package Type] = 3) OR (ch.[Chassis Package Type] = 4) OR (ch.[Chassis Package Type] = 5) OR
    (ch.[Chassis Package Type] = 6) OR (ch.[Chassis Package Type] = 7)


  • 3.  RE: Help with Query

    Posted May 01, 2013 07:49 PM

    Hi,

    Thanks. One thing I have noticed once I had a chance to run this in SQL Management Studio is I am getting NULL for manufacturer, model, serial number, and ram. With regards to Processor I am getting some data on the rows returned, but I am also getting NULL on some rows as well.

    Here is a query similiar to the one you revised which I know works (by itself) in an attempt to integrate it with yours.

    I don't know if the code will test out clean. What is messing me up is with the needed JOINS. I am not too great with queries so knowing which JOINS are needed can be a pain in the butt.

    Can you tell me what you think?

    SELECT DISTINCT

    vCom.[Name] as [Computer Name]


    ,vCom.[Domain] AS [Domain Name]

    ,vCom.[OS Name] AS [Operating System]

    
,vCom.[OS Revision] AS [Service Pack]

    ,cs.[Model]


    ,cs.[Total Physical Memory (Bytes)]

    

,cs.[Identifying Number] AS [Serial Number]

    ,ac.[Last Logon User]


    ,pr.Model AS 'Processor'
, I.Name AS 'Location'


    ,vc.[User] AS 'Primary Owner'


    FROM
    CollectionMembership cm

    INNER JOIN
    vCollection vColl
    ON cm.[CollectionGuid] = vColl.[Guid]

    INNER JOIN ResourceAssociation AS loc ON (loc.ChildResourceGuid = vc.Guid OR
    loc.ParentResourceGuid = vc.Guid) AND loc.ResourceAssociationTypeGuid = '05DE450F-39EA-4AAE-8C5F-77817889C27C'

    INNER JOIN Inv_HW_Chassis AS ch
    ON ch._ResourceGuid = vc.Guid



    INNER JOIN vItem AS I
    ON loc.ChildResourceGuid = I.Guid

    INNER JOIN vHWProcessor AS pr
    ON vc.Guid = pr._ResourceGuid

    INNER JOIN
    vComputer vCom
    ON cm.[ResourceGuid] = vCom.[Guid]

    INNER JOIN
    Inv_AeX_AC_Identification ac
    on ac.[_ResourceGuid] = vCom.[Guid]

    LEFT JOIN
    vHWComputerSystem cs
    ON cs.[_ResourceGuid] = vCom.[Guid]

    WHERE (ch.[Chassis Package Type] IS NULL) OR (ch.[Chassis Package Type] = 3) OR (ch.[Chassis Package Type] = 4) OR (ch.[Chassis Package Type] = 5) OR (ch.[Chassis Package Type] = 6) OR (ch.[Chassis Package Type] = 7) AND vColl.[Name] = 'Windows 2000/XP/2003/Vista/2008/7 Computers' and [Identifying Number] is Not Null AND vCom.[IsManaged] = 1

    ORDER BY
    vCom.[Name]

     

     

     


     

     

     



  • 4.  RE: Help with Query

    Posted May 02, 2013 10:42 AM

    I tried your query but it threw a few errors, so I rewrote it a bit:

    SELECT DISTINCT  vCom.[Name] as [Computer Name]
                    ,vCom.[Domain] AS [Domain Name]
                    ,vCom.[OS Name] AS [Operating System]
                    ,vCom.[OS Revision] AS [Service Pack]
                    ,cs.[Model]
                    ,cs.[Total Physical Memory (Bytes)]
                    ,cs.[Identifying Number] AS [Serial Number]
                    ,ac.[Last Logon User]
                    ,pr.Model AS 'Processor', I.Name AS 'Location'
                    ,vCom.[User] AS 'Primary Owner'
    FROM vComputer vCom
    INNER JOIN ResourceAssociation loc
        ON (loc.ChildResourceGuid = vCom.Guid OR loc.ParentResourceGuid = vCom.Guid)
        AND loc.ResourceAssociationTypeGuid = '05DE450F-39EA-4AAE-8C5F-77817889C27C'
    INNER JOIN Inv_HW_Chassis AS ch
        ON ch._ResourceGuid = vCom.Guid
    INNER JOIN vItem AS I
        ON loc.ChildResourceGuid = I.Guid
    INNER JOIN vHWProcessor AS pr
        ON vCom.Guid = pr._ResourceGuid
    INNER JOIN Inv_AeX_AC_Identification ac
        on ac.[_ResourceGuid] = vCom.[Guid]
    LEFT JOIN vHWComputerSystem cs
        ON cs.[_ResourceGuid] = vCom.[Guid]
    WHERE ((ch.[Chassis Package Type] IS NULL)
            OR (ch.[Chassis Package Type] in (3,4,5,6,7)))
        AND [Identifying Number] IS NOT NULL
        AND vCom.[IsManaged] = 1
        AND ac.[System Type] in ('Win32','Win64')
    ORDER BY vCom.[Name]

    I removed the reference to the collection tables and views as it seems you were just trying to limit the output to Windows computers and using the System Type from the Inv_AeX_AC_Identification table or vComputer view is a simpler way.

    Note that the query above will only show computers which have been assigned to a location. This is because you have used INNER JOIN to join the ResourceAssociation table and vItem view to the query.

    If you are just after the processor name for windows machines, I find that the Inv_HW_Processor_Name_Windows is a better source of information than the vHWProcessor view. Querying the former is also an awful lot faster.

    SELECT DISTINCT  vCom.[Name] as [Computer Name]
                    ,vCom.[Domain] AS [Domain Name]
                    ,vCom.[OS Name] AS [Operating System]
                    ,vCom.[OS Revision] AS [Service Pack]
                    ,cs.[Model]
                    ,cs.[Total Physical Memory (Bytes)]
                    ,cs.[Identifying Number] AS [Serial Number]
                    ,ac.[Last Logon User]
                    ,pr.[Processor Name] AS 'Processor', I.Name AS 'Location'
                    ,vCom.[User] AS 'Primary Owner'
    FROM vComputer vCom
    INNER JOIN ResourceAssociation loc
        ON (loc.ChildResourceGuid = vCom.Guid OR loc.ParentResourceGuid = vCom.Guid)
        AND loc.ResourceAssociationTypeGuid = '05DE450F-39EA-4AAE-8C5F-77817889C27C'
    INNER JOIN Inv_HW_Chassis AS ch
        ON ch._ResourceGuid = vCom.Guid
    INNER JOIN vItem AS I
        ON loc.ChildResourceGuid = I.Guid
    INNER JOIN Inv_HW_Processor_Name_Windows AS pr
        ON vCom.Guid = pr._ResourceGuid
    INNER JOIN Inv_AeX_AC_Identification ac
        on ac.[_ResourceGuid] = vCom.[Guid]
    LEFT JOIN vHWComputerSystem cs
        ON cs.[_ResourceGuid] = vCom.[Guid]
    WHERE ((ch.[Chassis Package Type] IS NULL)
            OR (ch.[Chassis Package Type] in (3,4,5,6,7)))
        AND [Identifying Number] IS NOT NULL
        AND vCom.[IsManaged] = 1
        AND ac.[System Type] in ('Win32','Win64')
    ORDER BY vCom.[Name]

     



  • 5.  RE: Help with Query

    Posted May 02, 2013 12:06 PM

    Hi,

    I made some very minor tweaks 2607 devices (laptops) are returned

     

    SELECT DISTINCT  vCom.[Name] as [Computer Name]
                    ,vCom.[Domain] AS [Domain Name]
                    ,vCom.[OS Name] AS [Operating System]
                    ,vCom.[OS Revision] AS [Service Pack]
                    ,cs.[Model]
                    ,cs.[Total Physical Memory (Bytes)]
                    ,cs.[Identifying Number] AS [Serial Number]
                    ,ac.[Last Logon User]
                    ,pr.Model AS 'Processor', I.Name AS 'Location'
                    ,vCom.[User] AS 'Primary Owner'
    FROM vComputer vCom
    INNER JOIN ResourceAssociation loc
        ON (loc.ChildResourceGuid = vCom.Guid OR loc.ParentResourceGuid = vCom.Guid)
        AND loc.ResourceAssociationTypeGuid = '05DE450F-39EA-4AAE-8C5F-77817889C27C'
    INNER JOIN Inv_HW_Chassis AS ch
        ON ch._ResourceGuid = vCom.Guid
    INNER JOIN vItem AS I
        ON loc.ChildResourceGuid = I.Guid
    INNER JOIN vHWProcessor AS pr
        ON vCom.Guid = pr._ResourceGuid
    INNER JOIN Inv_AeX_AC_Identification ac
        on ac.[_ResourceGuid] = vCom.[Guid]
    LEFT JOIN vHWComputerSystem cs
        ON cs.[_ResourceGuid] = vCom.[Guid]
    WHERE ((ch.[Chassis Package Type] IS NULL)
            OR (ch.[Chassis Package Type] in (9,10)))
        AND [Identifying Number] IS NOT NULL
        AND vCom.[IsManaged] = 1
        AND ac.[System Type] in ('Win32','Win64')
        AND vCom.[OS Name]  not like '%server%'
        AND cs.[Identifying Number] <> 'IS NULL'
    ORDER BY vCom.[Name]

     

    A colleague suggested the following query and 2711 devices (laptops) are returned

    SELECT     vComputer.[GUID]
    , vComputer.Name AS 'Computer Name'
    , vComputer.[IP Address]
    , vComputer.[OS Name]
    , vComputer.[OS Revision]
    , vHWComputerSystem.Manufacturer
    , vHWComputerSystem.Model
    , vHWComputerSystem.[Identifying Number] AS 'Serial Number'
    , vHWProcessor.Model AS 'Processor'
    , vHWComputerSystem.[Total Physical Memory (Bytes)] / 1048576 AS 'RAM (MB)'
    , I.Name AS 'Location'
    , vComputer.[User] AS 'Primary Owner'
    , "type" =
                    CASE Inv_HW_Chassis.[Chassis Package Type]
                                    When 1 Then 'Other'
                                    When 2 Then 'Unknown'
                                    When 3 Then 'Desktop'
                                    When 4 Then 'Low Profile Desktop'
                                    When 5 Then 'Pizza Box'
                                    When 6 Then 'Mini Tower'
                                    When 7 Then 'Tower'
                                    When 8 Then 'Portable'
                                    When 9 Then 'Laptop'
                                    When 10 Then 'Notebook'
                                    When 11 Then 'Hand Held'
                                    When 12 Then 'Docking Station'
                                    When 13 Then 'All in One'
                                    When 14 Then 'Sub Notebook'
                                    When 15 Then 'Space-Saving'
                                    When 16 Then 'Lunch Box'
                                    When 17 Then 'Main System Chassis'
                                    When 18 Then 'Expansion Chassis'
                                    When 19 Then 'Sub Chassis'
                                    When 20 Then 'Bus Expansion Chassis'
                                    When 21 Then 'Peripheral Chassis'
                                    When 22 Then 'Storage Chassis'
                                    When 23 Then 'Rack Mount Chassis'
                                    When 24 Then 'Sealed Case PC'
                                    ELSE 'Other'
                    END
    FROM         vComputer LEFT OUTER JOIN
                          ResourceAssociation AS loc ON (loc.ChildResourceGuid = vComputer.Guid OR
                          loc.ParentResourceGuid = vComputer.Guid) AND loc.ResourceAssociationTypeGuid = '05DE450F-39EA-4AAE-8C5F-77817889C27C' LEFT OUTER JOIN
                          vItem AS I ON loc.ChildResourceGuid = I.Guid LEFT OUTER JOIN
                          vHWProcessor ON vComputer.Guid = vHWProcessor._ResourceGuid LEFT OUTER JOIN
                          vHWComputerSystem ON vComputer.Guid = vHWComputerSystem._ResourceGuid LEFT OUTER JOIN
                          Inv_HW_Chassis ON vComputer.Guid = Inv_HW_Chassis._ResourceGuid
    WHERE                 (vComputer.IsManaged = 1)and (vHWComputerSystem.[Identifying Number] <> 'IS NULL') and (Inv_HW_Chassis.[Chassis Package Type] = 9)
                          or (Inv_HW_Chassis.[Chassis Package Type] = 10)
                          and vComputer.[OS Name] not like '%server%'
    ORDER BY 'Computer Name'


     

    Both queries work well, but why am I off by 100 or so devices depending on the one used. I have had this issue before with other reports not agreeing with each other.

     

    Thoughts?

     

     

     

     

     



  • 6.  RE: Help with Query

    Posted May 02, 2013 05:17 PM

    When there are a number of logical operators in a WHERE clause, SQL evaluates NOT first, then AND and finally OR.

    Your colleague's WHERE clause is:

    WHERE    (vComputer.IsManaged = 1)
    AND (vHWComputerSystem.[Identifying Number] <> 'IS NULL')
    AND (Inv_HW_Chassis.[Chassis Package Type] = 9)
    OR (Inv_HW_Chassis.[Chassis Package Type] = 10)
    AND vComputer.[OS Name] not like '%server%'

    SQL is interpreting this as:

    WHERE    (vComputer.IsManaged = 1 AND vHWComputerSystem.[Identifying Number] <> 'IS NULL'
    AND Inv_HW_Chassis.[Chassis Package Type] = 9)
    OR
    (Inv_HW_Chassis.[Chassis Package Type] = 10 AND vComputer.[OS Name] not like '%server%')
    

    The 1000 extra machines could be any machines where:

    • The chassis type is 9 and the machine is running a server OS.
    • The chassis type is 10 and the machine is not managed
    • The chassis type is 10 and the Identifying Number is NULL.

    By the way, 

    vHWComputerSystem.[Identifying Number] <> 'IS NULL'

    is filtering out the NULL values, but you could have written 

    vHWComputerSystem.[Identifying Number] <> 'IS A BLACK CAT'

    and that would have the same effect. The normal syntax is:

    vHWComputerSystem.[Identifying Number] IS NOT NULL

    I hope that makes sense to you.

    John
     

     



  • 7.  RE: Help with Query

    Posted May 02, 2013 05:47 PM

    I just noticed that your colleague is using LEFT OUTER JOINS on most of the tables. This will mean that computers that do not have entries in the joined tables/views will still display in the results unless they do not match the filters.

    Note that these expressions:

    AND (vHWComputerSystem.[Identifying Number] <> 'IS NULL')
    AND (Inv_HW_Chassis.[Chassis Package Type] = 9)
    OR (Inv_HW_Chassis.[Chassis Package Type] = 10)

    mean that though he has specified LEFT OUTER JOINs on the vHWComputerSystem view and the Inv_HW_Chassis table, they will actually be INNER JOINs.

    So some of the extra 1000 computers could also be computers not assigned to a location or the processor name is null.



  • 8.  RE: Help with Query

    Posted May 02, 2013 06:00 PM

    This should give you results similar to the ones you got from your colleague's query:

    SELECT    vc.[GUID]
            , vc.Name AS 'Computer Name'
            , vc.[IP Address]
            , vc.[OS Name]
            , vc.[OS Revision]
            , cs.Manufacturer
            , cs.Model
            , cs.[Identifying Number] AS 'Serial Number'
            , p.Model AS 'Processor'
            , cs.[Total Physical Memory (Bytes)] / 1048576 AS 'RAM (MB)'
            , vi.Name AS 'Location'
            , vc.[User] AS 'Primary Owner'
            , c.[Chassis Package Type]
            , ISNULL(s.[String],'Other') [Type]
    FROM vComputer vc
    LEFT OUTER JOIN ResourceAssociation  loc
        ON (loc.ChildResourceGuid = vc.Guid OR loc.ParentResourceGuid = vc.Guid)
        AND loc.ResourceAssociationTypeGuid = '05DE450F-39EA-4AAE-8C5F-77817889C27C'
    LEFT OUTER JOIN vItem vi
        ON loc.ChildResourceGuid = vi.Guid
    LEFT OUTER JOIN vHWProcessor p
        ON vc.Guid = p._ResourceGuid
    INNER JOIN vHWComputerSystem cs
        ON vc.Guid = cs._ResourceGuid
    INNER JOIN Inv_HW_Chassis c
        ON vc.Guid = c._ResourceGuid
    LEFT OUTER JOIN String s
        ON s.StringRef = 'listitem.chassis.chassistypes.' + CAST(c.[Chassis Package Type] as nvarchar(2))
        AND s.Culture=''
    WHERE vc.IsManaged = 1
        AND    cs.[Identifying Number] IS NOT NULL
        AND c.[Chassis Package Type] IN (9,10)
        and vc.[OS Name] NOT LIKE '%server%'
    ORDER BY vc.Name

    John



  • 9.  RE: Help with Query

    Posted May 02, 2013 06:01 PM

    Hi John,

    We were off by 100, not 1000 when comparing the two queries. I do appreciate taking the time to explain how the SQL commands are evaluated when used with a WHERE command.

    Most of the null records seem to be the result of a failed inventory for whatever reason. We just recently we discovered all software deltas seem to be fine, the hardware deltas seem to have issues and the data hash has become stale.

    I have another headache with a query. Hopefully you or someone else could be of assistance.

    Thanks.

     

     



  • 10.  RE: Help with Query

    Posted May 02, 2013 06:46 PM

    Hi Brian,

    If the data hashes in the ResourceUpdateSummary table are out of synch with the actual contents of the various HW Inventory tables, the data loader can end up discarding incoming data and leaving incorrect data in the tables.

    If you open the Windows Task Scheduler on the server, you will find a task called NS.Refresh Resource Update Summary. If you launch this manually, it will check all the row counts in the various tables and update the ResourceUpdateSummary table if the row counts do not match.

    Having said that, some of the views, like the vHWProcessor view that you reference in your query, could be defined better. How long does it take to run this report on your system?

    By all means, post your query to this forum and someone is sure to help. Don't forget to mark threads as answered if you get the information you are looking for.

    John