Symantec Management Platform (Notification Server)

Hope you had a Merry Christmas :)

I am trying to create a report which lists the following:

Primary User, Machine Name, Make, Model, RAM, HDD capacity, Processor Count, Processor Speed.

I have tried the Report Wizard and the results are inaccurate - in that there are multiple entries for one computers and the Model seems to list the application names. The SQL for the Altiris report is below.

Also I found a few reports people have posted on the forums which I thought I could ament to suit my needs - although im having a bit of difficulty doing that. The SQL for that report is further below.

Please can someone help me out with the Altiris report as id like to make sure I can use the inbuilt funtionality rather than rely on my poor SQL skills. :(

DECLARE @v1_TrusteeScope nvarchar(max)
   SET @v1_TrusteeScope = N'%TrusteeScope%'
DECLARE @v2_Filter nvarchar(max)
   SET @v2_Filter = N'%Filter%'
SELECT
   [vri3_Computer].[Guid] AS [_ItemGuid],
   [vri3_Computer].[Name],
   [dca6_AeX AC Primary User].[User],
   [dca10_HW Processor].[Number Of Logical Processors],
   [dca10_HW Processor].[Number Of Cores],
   [dca10_HW Processor].[Max Clock Speed (Mega-hertz)],
   [dca8_HW Physical Memory].[Capacity (Bytes)],
   [dca9_Hardware Summary].[Number of processor cores],
   [dca9_Hardware Summary].[Number of processors],
   [dca9_Hardware Summary].[Total RAM],
   [dca9_Hardware Summary].[Vendor Name],
   [dca4_HW Logical Device].[Manufacturer],
   [dca4_HW Logical Device].[Model]
FROM
   [vRM_Computer_Item] AS [vri3_Computer]
      LEFT OUTER JOIN [Inv_HW_Logical_Device] AS [dca4_HW Logical Device]
         ON ([vri3_Computer].[Guid] = [dca4_HW Logical Device].[_ResourceGuid])
      LEFT OUTER JOIN [Inv_AeX_AC_Identification] AS [dca5_AeX AC Identification]
         ON ([vri3_Computer].[Guid] = [dca5_AeX AC Identification].[_ResourceGuid])
      LEFT OUTER JOIN [Inv_AeX_AC_Primary_User] AS [dca6_AeX AC Primary User]
         ON ([vri3_Computer].[Guid] = [dca6_AeX AC Primary User].[_ResourceGuid])
      LEFT OUTER JOIN [Inv_HW_Computer_System] AS [dca7_HW Computer System]
         ON ([vri3_Computer].[Guid] = [dca7_HW Computer System].[_ResourceGuid])
      LEFT OUTER JOIN [Inv_HW_Physical_Memory] AS [dca8_HW Physical Memory]
         ON ([vri3_Computer].[Guid] = [dca8_HW Physical Memory].[_ResourceGuid])
      LEFT OUTER JOIN [Inv_Hardware_Summary] AS [dca9_Hardware Summary]
         ON ([vri3_Computer].[Guid] = [dca9_Hardware Summary].[_ResourceGuid])
      LEFT OUTER JOIN [Inv_HW_Processor] AS [dca10_HW Processor]
         ON ([vri3_Computer].[Guid] = [dca10_HW Processor].[_ResourceGuid])
WHERE
   (
      ([vri3_Computer].[Guid] IN (SELECT [ResourceGuid] FROM [ScopeMembership] WHERE [ScopeCollectionGuid] IN (SELECT [ScopeCollectionGuid] FROM dbo.fnGetTrusteeScopeCollections(@v1_TrusteeScope))))
   )
 

The non Altiris Wizard report:

SELECT vCom.[Name] as [Computer Name]
 ,vCom.[User]
 ,cs.[Model]
 ,cs.[Manufacturer]
 ,cs.[Identifying Number] AS [Serial Number]
 ,vCom.[OS Name] AS [Operating System]
 ,vCom.[OS Revision] AS [Service Pack]
FROM
 CollectionMembership cm
  INNER JOIN
   vComputer vCom
   ON cm.[ResourceGuid] = vCom.[Guid]
  INNER JOIN
   vCollection vColl
   ON cm.[CollectionGuid] = vColl.[Guid]
  LEFT JOIN
   vHWComputerSystem cs
   ON cs.[_ResourceGuid] = vCom.[Guid]
WHERE
 vColl.[Name] = 'Windows workstations' AND
 vCom.[IsManaged] = 1
ORDER BY
 vCom.[Name]

OK, here's what you do:

1. Open SQL Server Management Studio and attach to a SQL Server with a copy of your production database with an account with dbowner rights
2. In the left hand pane expand the copy of the database, right-click on "Views" and select "New View"
3. Cut and paste the text from your first query above from "Select" to just before "where" into the third pane on the right and click the red exclamation mark icon above the left hand pane.
4. In the top pane on the right right click and "Remove" the tables and views not in the SELECT statement; dca5_AeX AC Identification, dca7_HW Computer System, dca8_HW Physical Memory. I'd remove dca10_HW Processor too as it contains information you already have in other ables and I think is causing duplicate rows - you get one row for each entry in this table, a computer can have more than one processor so will appear in two rows.
5. In the top pane on the right, in dca4_HW Logical Device, select the check box for "Description"
6. Click the red exclamation mark icon above the left hand pane.
7. You can see the information you're after has an entry in the "Description" column containing "AT/AT COMPATIBLE". Cut and paste this into the Filter column of the Description row as:

• Like 'AT/AT COMPATIBLE' (You can remove the tick under the "Output" column for this row.)

1. Click the red exclamation mark icon above the left hand pane again.
2. If this is the output you need cut and paste the SQL back in to the query.

Hi Andy!

Hope your well, not seen you for a while.

Ill give this a go right now and let you know how I get on, many thanks for the help.

Minesh

Ok Done all the above,

Looks tidier, but for dca9_Hardware Summary, Number of Processors, Number of Processor Cores, Total RAM is NULL.

Any Ideas?

Full inventory has been running sucessfully

I think dca9_Hardware Summary uses the Inv_Hardware_Summary Table that's supposed to be populated by an overnight scheduled task which I think runs a stored procedure.

You may need this hotfix:

"Unable to generate the data class summary information (Name: Inv_Hardware_Summary, Guid: Hardware Summary"

http://www.symantec.com/docs/TECH175729

Thanks, FYI I am running 7.1 SP2 MP1.

The error logs mentioned in the KB dont seem to be something I have, but more importantly I dont seem to be able to see a table called 'dbo.Inv_Hardware_Summary'

Is there other tables I can use  to get the information I want, for now? If so how would I add these in?

IE dbo.Inv_HW_Computer_System gives the processor count

Many thanks

 Look in the relevant Stored Procedure that generates the summary table - it's got a similar name. That will tell you which tables it interrogates to generate the summary table. Then follow the instructions in my earlier post and add those tables in and select the appropriate fields.

You might find a problem if you have more then one entry in the relevant table per PC, that'll take a bit more tricky SQL to solve, similar to what's in the Stored Procedure - you might need to create another view from that.