Symantec Management Platform (Notification Server)

hi all,

I've got an sql query that puts all hardware information into one report. However, I've noticed some of the hardware info, such as model, serial number, hdd info etc. is missing from some machines (approx 10% of machines) The thing is that the inventory agent on this machines does collect and report this information to NS, I can see it under resource manager as well as when I execute another report.

here's the query I use

SELECT     TOP (100) PERCENT r.Guid, item.Name AS Hostname, c.domain, dertbl.Lastconfigrequest AS [Last Contact with NS], DATEDIFF(d, dertbl.Lastconfigrequest, GETDATE()) 
                      AS [Days Since Last Contact], hw.Manufacturer, hw.[Identifying Number] AS [Serial Number], hw.Model, 
                      CASE Chassis.[Chassis Package Type] WHEN 1 THEN 'Unknown' WHEN 2 THEN 'Unknown' WHEN 3 THEN 'Desktop' WHEN 4 THEN 'Desktop' WHEN 5 THEN 'Desktop'
                       WHEN 6 THEN 'Desktop' WHEN 7 THEN 'Desktop' WHEN 8 THEN 'Laptop' WHEN 9 THEN 'Laptop' WHEN 10 THEN 'Laptop' WHEN 11 THEN 'Hand Held' WHEN 12 THEN
                       'Docking Station' WHEN 13 THEN 'All in One' WHEN 14 THEN 'Laptop' WHEN 15 THEN 'Desktop' WHEN 16 THEN 'Desktop' WHEN 17 THEN 'Main  System Chassis' WHEN
                       18 THEN 'Expansion Chassis' WHEN 19 THEN 'SubChassis' 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' END AS [Asset Type], bios.[Release Date] AS [BIOS Release Date], 
                      hw.[Number Of Processors] AS [Number Of Processrs], DATEDIFF(d, bios.[Release Date], GETDATE()) / 365 AS [Age (From BIOS)], 
                      hw.[Total Physical Memory (Bytes)] / 1048576 AS [Asset RAM (MB)], Logicaldisksummary.[Total Disk Size in GB] AS [Total Disk Size (GB)], 
                      Logicaldisksummary.[Free Space in GB] AS [Free Disk Space (GB)], Logicaldisksummary.[Number of LogDisks] AS [Number of Logical Disks], os.Name AS OS, 
                      c.[OS Name], ident.[OS Revision] AS [OS SP], os.[OS Architecture], tcp.[IP Address], tcp.Subnet, usr.[User] AS [Primary User]
FROM         dbo.vComputerResource AS r INNER JOIN
                      dbo.vRM_Computer_Item AS item ON r.Guid = item.Guid LEFT OUTER JOIN
                          (SELECT     ResourceGuid, MAX(_eventTime) AS Lastconfigrequest
                            FROM          dbo.Evt_NS_Client_Config_Request
                            GROUP BY ResourceGuid) AS dertbl ON r.Guid = dertbl.ResourceGuid LEFT OUTER JOIN
                      dbo.vHWComputerSystem AS hw ON r.Guid = hw._ResourceGuid LEFT OUTER JOIN
                      dbo.vHWChassis AS chassis ON r.Guid = chassis._ResourceGuid LEFT OUTER JOIN
                      dbo.Inv_SW_BIOS_Element AS bios ON r.Guid = bios._ResourceGuid LEFT OUTER JOIN
                          (SELECT     _ResourceGuid, SUM([Free Space (Bytes)] / 1073741824) AS [Free Space in GB], SUM([Size (Bytes)] / 1073741824) AS [Total Disk Size in GB], 
                                                   COUNT(Name) AS [Number of LogDisks]
                            FROM          dbo.Inv_HW_Logical_Disk
                            WHERE      ([Logical Disk Type] = 3)
                            GROUP BY _ResourceGuid) AS Logicaldisksummary ON r.Guid = Logicaldisksummary._ResourceGuid LEFT OUTER JOIN
                      dbo.Inv_OS_Operating_System AS os ON r.Guid = os._ResourceGuid LEFT OUTER JOIN
                      dbo.vComputer AS c ON r.Guid = c.Guid LEFT OUTER JOIN
                      dbo.Inv_AeX_AC_Identification AS ident ON r.Guid = ident._ResourceGuid LEFT OUTER JOIN
                      dbo.Inv_AeX_AC_TCPIP AS tcp ON r.Guid = tcp._ResourceGuid AND tcp._id =
                          (SELECT     TOP (1) _id
                            FROM          dbo.Inv_AeX_AC_TCPIP AS t
                            WHERE      (r.Guid = _ResourceGuid) AND ([IP Address] NOT IN ('127.0.0.1', '0.0.0.0', '')) AND (NOT ([IP Address] IS NULL))
                            ORDER BY Routable DESC, DHCPEnabled DESC) LEFT OUTER JOIN
                      dbo.Inv_AeX_AC_Primary_User AS usr ON usr._ResourceGuid = r.Guid AND usr._id =
                          (SELECT     TOP (1) _id
                            FROM          dbo.Inv_AeX_AC_Primary_User AS p
                            WHERE      (r.Guid = _ResourceGuid)
                            ORDER BY _id DESC)
WHERE     (r.IsManaged = 1)
ORDER BY c.Name

however, when I use this report I get all the info

SELECT     vComputerEx.Name, va.Model, va.[Serial Number], va.Manufacturer, va.[Asset Type], va.Status, vComputerEx.Domain,
                      vComputerEx.[User], vComputerEx.[OS Name], vComputerEx.[OS Version], vComputerEx.[IP Address], vComputerEx.[OS Primary Language],
                      vComputerEx.[MAC Address], vComputerEx.[System Type], vComputerEx.Guid
FROM         vAsset AS va INNER JOIN
                      vComputerEx ON va._ResourceGuid = vComputerEx.Guid
WHERE     (va.[Asset Type] LIKE 'Computer') AND (vComputerEx.IsManaged LIKE 1)
ORDER BY Name

Any thoughts?

If you take the 

r.Guid

from the Report which doesn't show information then use that as a criteria in the Inv_HW_* tables does it return anything?

The first report uses LEFT joins so it will show all computers whether there is extra info in the Hardware tables, the second uses an INNER JOIN so will only show computers where information exists in both.