Video Screencast Help
Symantec to Separate Into Two Focused, Industry-Leading Technology Companies. Learn more.

Problem with Inventory Hardware Computer

Created: 02 Jun 2011 • Updated: 02 Jun 2011 | 1 comment
Nut_recoba's picture

Hey All Guys

I have problem with My customize report. In my enviroment use Altiris CMS7.0+ Dell Client Manager + HpClient Manager and about my problem When I Query SQL  in Table  Inv_HW_Computer_System

The Result of query was Shown me Miss something about new client( after install agent ,all plugin and run full inventory job and Dell Hw Inventory Completed )  was not show in this query.(Please see The resul attached in my picture )

How ever I Try to query on Table " Inv_OMCA_System_Summary " . I found my new client machine such as Filed _ResourceGuid ,Memory, Processor ,

Last , this is my problem and my question ,  2-3 Month Ago I Query my report in Table Inv_HW_Computer_System, I found evrything With HP and Dell Product 

In my understood  I dont know Why This Inv_OMCA_System_Summary  not  forword data to center table Inv_HW_Computer_System  like  2-3 Month ago ??

 and about

and about my sql quey HW report

*********************************************************************************************************************

DECLARE

@v1_TrusteeScope nvarchar(max)

SET @v1_TrusteeScope = N'%TrusteeScope%'

SELECT

Distinct

[vri2_Computer]

.[Guid] AS [_ItemGuid],

[vri2_Computer]

.[Name] AS [Computer Name],

[dca3_AeX AC Identification]

.[Domain],

[dca15_Puser]

.[User],

log_on

.[Time],

[dca13_vHWComputer]

.Manufacturer,

[dca13_vHWComputer]

.Model,

[dca13_vHWComputer]

.[Identifying Number]AS [Serail No],

[dca14_vbios]

.Name as [Bios Name],

[dca8_OMCA System Summary]

.[BIOS Version],

[dca5_HW Computer System]

.[Total Physical Memory (Bytes)] / (1024 * 1024) as [Total Memory (MB)],

m

.[Memory Devices] as [Memory Total Slots],

temp

.[Used Slots][Used Slots] ,

m

.[Memory Devices]-temp.[Used Slots] as [Memory Free Slots],

m

.[Max Capacity (Kilobytes)] / (1024) as [Max Available memory (MB)],

[dca12_vHW ]

.Model,

[dca12_vHW ]

.[Max Clock Speed (Mega-hertz)],

[dca7_HW Logical Disk]

.[Mount Point],

[dca7_HW Logical Disk]

.[Size (Bytes)] / (1024 * 1024* 1024) as [Total Disk Space(GB)],

[dca3_AeX AC Identification]

.[OS Name],

[dca3_AeX AC Identification]

.[system Type],

[dca3_AeX AC Identification]

.[OS Revision],

[dca11_SW Antivirus]

.[Name] AS [AntiVirus Product],

[dca11_SW Antivirus]

.[Version]

FROM

[vRM_Computer_Item]

AS [vri2_Computer]

 

LEFT OUTER JOIN [Inv_AeX_AC_Identification] AS [dca3_AeX AC Identification]

 

ON ([vri2_Computer].[Guid] = [dca3_AeX AC Identification].[_ResourceGuid])

 

LEFT OUTER JOIN [Inv_HW_Processor_Name_Windows] AS [dca4_HW Processor Name Windows]

 

ON ([vri2_Computer].[Guid] = [dca4_HW Processor Name Windows].[_ResourceGuid])

 

LEFT OUTER JOIN [Inv_HW_Computer_System] AS [dca5_HW Computer System]

 

ON ([vri2_Computer].[Guid] = [dca5_HW Computer System].[_ResourceGuid])

 

LEFT OUTER JOIN [Inv_HW_Logical_Disk] AS [dca7_HW Logical Disk]

 

ON ([vri2_Computer].[Guid] = [dca7_HW Logical Disk].[_ResourceGuid])

 

LEFT OUTER JOIN [Inv_OMCA_System_Summary] AS [dca8_OMCA System Summary]

 

ON ([vri2_Computer].[Guid] = [dca8_OMCA System Summary].[_ResourceGuid])

 

LEFT OUTER JOIN [Inv_HW_Desktop_Monitor] AS [dca9_HW Desktop Monitor]

 

ON ([vri2_Computer].[Guid] = [dca9_HW Desktop Monitor].[_ResourceGuid])

 

LEFT OUTER JOIN [Inv_HW_Chassis] AS [dca10_HW Chassis]

 

ON ([vri2_Computer].[Guid] = [dca10_HW Chassis].[_ResourceGuid])

 

LEFT OUTER JOIN [Inv_SW_Antivirus] AS [dca11_SW Antivirus]

 

ON ([vri2_Computer].[Guid] = [dca11_SW Antivirus].[_ResourceGuid])

 

LEFT OUTER JOIN dbo.vHWProcessor AS [dca12_vHW]

 

ON ([vri2_Computer].[Guid] = [dca12_vHW].[_ResourceGuid])

 

LEFT OUTER JOIN vHWComputerSystem AS [dca13_vHWComputer]

 

ON ([vri2_Computer].[Guid] = [dca13_vHWComputer].[_ResourceGuid])

 

LEFT OUTER JOIN vSWBIOSElement AS [dca14_vbios]

 

ON ([vri2_Computer].[Guid] = [dca14_vbios].[_ResourceGuid])

 

LEFT OUTER JOIN Inv_AeX_AC_Primary_User AS [dca15_Puser]

 

ON ([vri2_Computer].[Guid] = [dca15_Puser].[_ResourceGuid])

 

LEFT OUTER JOIN dbo.Inv_Operating_System_Summary AS [dca16_OS]

 

ON ([vri2_Computer].[Name] = [dca16_OS].Name)

 

 

LEFT OUTER JOIN (SELECT SUM ([Memory Devices]) [Memory Devices], _ResourceGuid, SUM ([Max Capacity (Kilobytes)]) [Max Capacity (Kilobytes)]

 

FROM dbo.vHWPhysicalMemoryArray

 

GROUP BY _ResourceGuid) m

 

ON m.[_ResourceGuid] = [vri2_Computer].[Guid]

 

LEFT OUTER JOIN (SELECT COUNT (*) [Used Slots],_ResourceGuid FROM vHWPhysicalMemory GROUP BY _ResourceGuid) temp

 

ON temp._ResourceGuid = [vri2_Computer].[Guid]

 

LEFT OUTER JOIN (Select Distinct _ResourceGuid,max ([Time])[Time] From [Evt_AeX_Client_LogOn] Where [Event] like 'Logon' Group by _ResourceGuid) log_on

 

ON log_on._ResourceGuid = [vri2_Computer].[Guid]

WHERE

 

(

 

([vri2_Computer].[Guid] IN (SELECT [ResourceGuid] FROM [ScopeMembership] WHERE [ScopeCollectionGuid] IN (SELECT [ScopeCollectionGuid] FROM dbo.fnGetTrusteeScopeCollections(@v1_TrusteeScope))))

 

) AND [dca7_HW Logical Disk].[Mount Point] LIKE'%C%' OR [dca7_HW Logical Disk].[Mount Point] LIKE'%D%'

Order

 

by [vri2_Computer].[Name]

 

****************************************************************************************************************

Please Hekp Me and thank in advance for solve my problem.

Comments 1 CommentJump to latest comment

Nut_recoba's picture

DECLARE @v1_TrusteeScope nvarchar(max)
   SET @v1_TrusteeScope = N'%TrusteeScope%'
SELECT Distinct
   [vri2_Computer].[Guid] AS [_ItemGuid],
   [vri2_Computer].[Name] AS [Computer Name],
   [dca3_AeX AC Identification].[Domain],
   [dca15_Puser].[User],
   log_on.[Time],
   [dca13_vHWComputer].Manufacturer,
   [dca13_vHWComputer].Model,
   [dca13_vHWComputer].[Identifying Number]AS [Serail No],
   [dca14_vbios].Name as [Bios Name],
   [dca8_OMCA System Summary].[BIOS Version],
   [dca5_HW Computer System].[Total Physical Memory (Bytes)]  / (1024 * 1024) as [Total Memory (MB)],
   m.[Memory Devices]  as [Memory Total Slots],
     temp.[Used Slots][Used Slots] ,
    m.[Memory Devices]-temp.[Used Slots] as [Memory Free Slots], 
 m.[Max Capacity (Kilobytes)] / (1024) as [Max Available memory (MB)], 
[dca12_vHW ].Model,
[dca12_vHW ].[Max Clock Speed (Mega-hertz)],
 [dca7_HW Logical Disk].[Mount Point],
   [dca7_HW Logical Disk].[Size (Bytes)] / (1024 * 1024* 1024) as [Total Disk Space(GB)],
   [dca3_AeX AC Identification].[OS Name],
[dca3_AeX AC Identification].[system Type],
[dca3_AeX AC Identification].[OS Revision],

   [dca11_SW Antivirus].[Name] AS [AntiVirus Product],
[dca11_SW Antivirus].[Version]
FROM
   [vRM_Computer_Item] AS [vri2_Computer]
      LEFT OUTER JOIN [Inv_AeX_AC_Identification] AS [dca3_AeX AC Identification]
         ON ([vri2_Computer].[Guid] = [dca3_AeX AC Identification].[_ResourceGuid])
      LEFT OUTER JOIN [Inv_HW_Processor_Name_Windows] AS [dca4_HW Processor Name Windows]
         ON ([vri2_Computer].[Guid] = [dca4_HW Processor Name Windows].[_ResourceGuid])
      LEFT OUTER JOIN [Inv_HW_Computer_System] AS [dca5_HW Computer System]
         ON ([vri2_Computer].[Guid] = [dca5_HW Computer System].[_ResourceGuid])
      LEFT OUTER JOIN [Inv_HW_Logical_Disk] AS [dca7_HW Logical Disk]
         ON ([vri2_Computer].[Guid] = [dca7_HW Logical Disk].[_ResourceGuid])
      LEFT OUTER JOIN [Inv_OMCA_System_Summary] AS [dca8_OMCA System Summary]
         ON ([vri2_Computer].[Guid] = [dca8_OMCA System Summary].[_ResourceGuid])
      LEFT OUTER JOIN [Inv_HW_Desktop_Monitor] AS [dca9_HW Desktop Monitor]
         ON ([vri2_Computer].[Guid] = [dca9_HW Desktop Monitor].[_ResourceGuid])
      LEFT OUTER JOIN [Inv_HW_Chassis] AS [dca10_HW Chassis]
         ON ([vri2_Computer].[Guid] = [dca10_HW Chassis].[_ResourceGuid])
      LEFT OUTER JOIN [Inv_SW_Antivirus] AS [dca11_SW Antivirus]
         ON ([vri2_Computer].[Guid] = [dca11_SW Antivirus].[_ResourceGuid])
      LEFT OUTER JOIN dbo.vHWProcessor AS [dca12_vHW]
         ON ([vri2_Computer].[Guid] = [dca12_vHW].[_ResourceGuid])
      LEFT OUTER JOIN vHWComputerSystem  AS [dca13_vHWComputer]
         ON ([vri2_Computer].[Guid] = [dca13_vHWComputer].[_ResourceGuid])
 LEFT OUTER JOIN vSWBIOSElement AS [dca14_vbios]
         ON ([vri2_Computer].[Guid] = [dca14_vbios].[_ResourceGuid])
 LEFT OUTER JOIN Inv_AeX_AC_Primary_User AS [dca15_Puser]
         ON ([vri2_Computer].[Guid] = [dca15_Puser].[_ResourceGuid])
 LEFT OUTER JOIN dbo.Inv_Operating_System_Summary AS [dca16_OS]
         ON ([vri2_Computer].[Name] = [dca16_OS].Name)

   LEFT OUTER JOIN  (SELECT SUM ([Memory Devices]) [Memory Devices], _ResourceGuid, SUM ([Max Capacity (Kilobytes)]) [Max Capacity (Kilobytes)]
   FROM dbo.vHWPhysicalMemoryArray
   GROUP BY _ResourceGuid) m  
  ON m.[_ResourceGuid] = [vri2_Computer].[Guid] 

  LEFT OUTER JOIN (SELECT COUNT (*) [Used Slots],_ResourceGuid FROM vHWPhysicalMemory GROUP BY _ResourceGuid) temp
  ON temp._ResourceGuid = [vri2_Computer].[Guid]

 LEFT OUTER JOIN  (Select Distinct _ResourceGuid,max ([Time])[Time]  From [Evt_AeX_Client_LogOn] Where [Event] like 'Logon'  Group by _ResourceGuid) log_on
  ON log_on._ResourceGuid = [vri2_Computer].[Guid]

WHERE
   (
      ([vri2_Computer].[Guid] IN (SELECT [ResourceGuid] FROM [ScopeMembership] WHERE [ScopeCollectionGuid] IN (SELECT [ScopeCollectionGuid] FROM dbo.fnGetTrusteeScopeCollections(@v1_TrusteeScope))))
   )   AND [dca7_HW Logical Disk].[Mount Point] LIKE'%C%' OR [dca7_HW Logical Disk].[Mount Point] LIKE'%D%'

Order by [vri2_Computer].[Name]