Symantec Management Platform (Notification Server)

Hello,

I am trying to create a somewhat custom Altiris report. It is based off of the "General Assets" report that comes with the software. We want to track all assets entered into Altiris and do not want to focus on certain types of assets, e.g. computers, network resources, cell phones etc so this report is perfect. I cloned the report, deleted the last barcode scan because it is irrelevant for our needs, and would like to add the comment field for all assets. Is there a way to do this? I have been scouring the internet and seem to be the only one who would like to do this. Pasted below is the SQL code from the current iteration of the report. Any help or insight would be greatly appreciated.

Thanks,

Ross

             declare @_culture varchar(20)
             set @_culture = '%_culture%'

             declare @BaseResourceTypeGuid uniqueidentifier
             set @BaseResourceTypeGuid = '%BaseResourceTypeGuid%'
             DECLARE @TrusteeScope nvarchar(max)
             SET @TrusteeScope = '%TrusteeScope%'

             declare @ActiveString nvarchar(20)
             select @ActiveString = String from vStringCache where BaseGuid = '0A0203A5-D2B6-49F1-A53B-5EC31A89437C' and StringRef = 'item.name' and Culture = @_culture

             select distinct
              i.Guid as _ItemGuid,
              strAssetType.[String] as [Asset Type],
              i.Name,
              isnull(strAssetStatus.[String], @ActiveString) [Asset Status],
              va.Manufacturer,
              va.Model,
              va.[Serial Number],
              va.[System Number],
              va.[Barcode]
             from vAsset va
              join vRM_Asset_Item i on va._ResourceGuid = i.Guid
              join ResourceTypeHierarchy rh on rh.ResourceTypeGuid = va._ResourceTypeGuid
                            outer apply
                            (
                select top 1 String
                              from StringCache where BaseGuid = va._AssetStatusGuid and Culture = @_culture and StringRef = 'item.name'
                            ) strAssetStatus
                            outer apply
                            (
                select top 1 String
                              from StringCache where StringRef='item.name' and Culture=@_culture and BaseGuid = va._ResourceTypeGuid
                            ) strAssetType
             where
              1 = 1
               and ((@BaseResourceTypeGuid = '00000000-0000-0000-0000-000000000000' AND rh.BaseResourceTypeGuid = rh.ResourceTypeGuid) OR (@BaseResourceTypeGuid = rh.BaseResourceTypeGuid))
               and (i.[Guid] IN (SELECT [ResourceGuid] FROM [ScopeMembership] WHERE [ScopeCollectionGuid] IN (SELECT [ScopeCollectionGuid] FROM dbo.fnGetTrusteeScopeCollections(@TrusteeScope))))
             Order by
              [Asset Type],
              [Asset Status]                           
           

Also, if it were possible to query the asset location as well, it would be fantastic.

Are you talking the Comment column in Inv_Comment?

Does this query return the results you would expect to see for comment? 

SELECT va.*,ic.Comment
FROM vAsset va
JOIN Inv_Comment ic ON ic._ResourceGuid=va._ResourceGuid

If so, in your query, simply add ic.Comment to the select statement, and then this join:
JOIN Inv_Comment ic ON ic._ResourceGuid=va._ResourceGuid

Does this help?

Mike,

Thank you very much for the assistance! The added code to the query does pull the desired information from the query but it was only pulling entries that had a comment. I was looking to query all the assets in Management Console whether or not the comment field was populated.

What I changed the code a bit to get what was I was looking for, so it now looks like this in the join statement. "left outer join Inv_Comment ic on ic._ResourceGuid=va._ResourceGuid" This will add the comment field to the query while still pulling all assets from the database, even if the comment field is left blank.

If possible, could you help me with the code to also get the asset location as well?

Thanks,

Ross