Asset Management Suite

 View Only

  • 1.  Adding Asset Owner to asset reports

    Posted Feb 07, 2013 03:08 PM

    I'm trying to have a report for any selected Resource Type also include the Asset Owner, including records that do not have an Asset Owner (i.e. Instock status assets).

     

    I used the Query Builder and adding the built-in Resource Type Association: [Asset User Owners] to [User] and then adding the field [User].[Name].

    This succeeds in giving me the Asset Owner for each asset record, but it fiters out all asset records with NO Asset Owner. I've tried every variation of SQL Joins available via the drop down (except Cross since that just makes the console explode) and I either get no results or the same filtered results.

    I converted the report to raw SQL then changed all the inner/outer/left/right joins to just JOIN and still not getting the results I want.

    I just want a report of ALL assets under that resource type that includes the Asset Owner field, blank or not.

    Suggestions? Symantec Enterprise Technical Support via our Advanced Access support contract hasn't been able to figure it out and honestly I find this need to be a very simple and likely common one. Having an asset database isn't just for inventory but also for accountability. Why wouldn't most want to have the Asset Owner showing up in a report? The Resource Type Association related to Asset Owners is already baked into Altiris 7.1 so I'd like to think there's a simple step I'm missing that prevents the report results from filtering out any asset records.

    Thanks,

    Chad



  • 2.  RE: Adding Asset Owner to asset reports

    Posted Feb 07, 2013 04:15 PM

    Post the raw SQL before you've edited it.

    You could try what I'll do, in SQL Management Studio, connect to a test Symantec_CMDB, right click on Views > New View, paste the SQL into the SQL pane and select Execute at the top. Then you can see the tables and joins and just change one to the type you need.



  • 3.  RE: Adding Asset Owner to asset reports

    Posted Feb 27, 2013 03:22 PM

    My apologies regarding the late reply.

    Here is the raw SQL generated by the Query Builder tool that returns only assets that have an Asset Owner assigned.

    Please note that I'm trying to get all assets in a category to return regardless of Asser Owner being populated (but I still need that field to show in the report).

    I'm trying to get away with building reports using the query builder rather than doing raw SQL queries as I don't really know much about SQL and the Query Builder is supposed to be the tool for those who want to build reports w/o SQL experience.

     

    DECLARE @v1_TrusteeScope nvarchar(155)
       SET @v1_TrusteeScope = N'{2E1F478A-4986-4223-9D1E-B5920A63AB41},{582029E2-FC5B-4717-8808-B80D6EF0FD67},{B760E9A9-E4DB-404C-A93F-AEA51754AA4F},{E42CD697-8344-4FF2-A8E3-A652ED55E539}'
    DECLARE @g7_AssetUserOwners uniqueidentifier
       SET @g7_AssetUserOwners = 'ed35a8d1-bf60-4771-9dde-092c146c485a'
    SELECT
       [vri2_Docking Station].[Guid] AS [_ItemGuid],
       [vri2_Docking Station].[Name],
       [vri9_User].[Name] AS [Owner],
       [dca5_Manufacturer].[Model],
       [dca5_Manufacturer].[Manufacturer],
       [dca4_Serial Number].[Serial Number],
       [dca6_Barcode].[Barcode],
       [dca3_Procurement].[Life Cycle],
       [vri9_User].[Guid] AS [_UserGuid]
    FROM
       [vRM_Docking_Station_Item] AS [vri2_Docking Station]
          LEFT OUTER JOIN [Inv_Procurement] AS [dca3_Procurement]
             ON ([vri2_Docking Station].[Guid] = [dca3_Procurement].[_ResourceGuid])
          LEFT OUTER JOIN [Inv_Serial_Number] AS [dca4_Serial Number]
             ON ([vri2_Docking Station].[Guid] = [dca4_Serial Number].[_ResourceGuid])
          LEFT OUTER JOIN [Inv_Manufacturer] AS [dca5_Manufacturer]
             ON ([vri2_Docking Station].[Guid] = [dca5_Manufacturer].[_ResourceGuid])
          LEFT OUTER JOIN [Inv_Barcode] AS [dca6_Barcode]
             ON ([vri2_Docking Station].[Guid] = [dca6_Barcode].[_ResourceGuid])
          LEFT OUTER JOIN ([ResourceAssociation] AS [ra8_Asset User Owners]
             LEFT OUTER JOIN [vRM_User_Item] AS [vri9_User]
                ON ([ra8_Asset User Owners].[ChildResourceGuid] = [vri9_User].[Guid]))
             ON ([vri2_Docking Station].[Guid] = [ra8_Asset User Owners].[ParentResourceGuid])
    WHERE
       (
          (
             ([ra8_Asset User Owners].[ResourceAssociationTypeGuid] = @g7_AssetUserOwners)
             OR
             ([ra8_Asset User Owners].[ResourceAssociationTypeGuid] IS NULL)
          )
          AND
          ([vri2_Docking Station].[Guid] IN (SELECT [ResourceGuid] FROM [ScopeMembership] WHERE [ScopeCollectionGuid] IN (SELECT [ScopeCollectionGuid] FROM dbo.fnGetTrusteeScopeCollections(@v1_TrusteeScope))))
          AND
          (
             ([vri9_User].[Guid] IN (SELECT [ResourceGuid] FROM [ScopeMembership] WHERE [ScopeCollectionGuid] IN (SELECT [ScopeCollectionGuid] FROM dbo.fnGetTrusteeScopeCollections(@v1_TrusteeScope))))
             OR
             ([vri9_User].[Guid] IS NULL)
          )
       )
     


  • 4.  RE: Adding Asset Owner to asset reports
    Best Answer

    Posted Feb 27, 2013 03:59 PM

    Looks like I was able to solve the issue. The mistake I made was joining the asset owner table via the User GUID rather than the Asset GUID. The former only returns rows with a User assigned, the other returns both (since the common link is simply requiring a matching asset GUID).