Video Screencast Help

Help with Query

Created: 30 Apr 2013 | 9 comments

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)

Operating Systems:

Comments 9 CommentsJump to latest comment

The Gaffer's picture

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)
Briandr88's picture

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]

 

 

 

 

 

 

The Gaffer's picture

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]

 

Briandr88's picture

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?

 

 

 

 

 

The Gaffer's picture

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
 

 

The Gaffer's picture

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.

The Gaffer's picture

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

Briandr88's picture

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.

 

 

The Gaffer's picture

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