Asset Management Suite

 View Only

  • 1.  CMDB Tables - "Computer Type"

    Posted Nov 01, 2010 01:45 PM

    Hi everyone!

    I would like to create a custom reports, but I can't find the correct table name for "Computer Type" (Server,workstation,etc).

    I'd try with vresource,vcomputertype,resourcetype, vrm_computer_item, but non of this are the correct one.

    This is the SQL code that I made with the Report Wizard:

    DECLARE @v1_TrusteeScope nvarchar(max)
       SET @v1_TrusteeScope = N'%TrusteeScope%'
    SELECT
       [vri2_Computer].[Guid] AS [_ItemGuid],
       [vri2_Computer].[Name],
       [dca3_AeX AC TCPIP].[IP Address],
       [ajs8_vAssetLocation].[Location],
       [ajs9_vFixedAssetResourceStatus].[Status],
       [dca5_Identity].[System Number],
       [dca7_Manufacturer].[Manufacturer],
       [dca7_Manufacturer].[Model],
       [dca4_AeX AC Identification].[Last Logon User],
       [dca6_Serial Number].[Serial Number]
    FROM
       [vRM_Computer_Item] AS [vri2_Computer]
          LEFT OUTER JOIN [Inv_AeX_AC_TCPIP] AS [dca3_AeX AC TCPIP]
             ON ([vri2_Computer].[Guid] = [dca3_AeX AC TCPIP].[_ResourceGuid])
          LEFT OUTER JOIN [Inv_AeX_AC_Identification] AS [dca4_AeX AC Identification]
             ON ([vri2_Computer].[Guid] = [dca4_AeX AC Identification].[_ResourceGuid])
          LEFT OUTER JOIN [Inv_Identity] AS [dca5_Identity]
             ON ([vri2_Computer].[Guid] = [dca5_Identity].[_ResourceGuid])
          LEFT OUTER JOIN [Inv_Serial_Number] AS [dca6_Serial Number]
             ON ([vri2_Computer].[Guid] = [dca6_Serial Number].[_ResourceGuid])
          LEFT OUTER JOIN [Inv_Manufacturer] AS [dca7_Manufacturer]
             ON ([vri2_Computer].[Guid] = [dca7_Manufacturer].[_ResourceGuid])
          LEFT OUTER JOIN [vAssetLocation] AS [ajs8_vAssetLocation]
             ON ([vri2_Computer].[Guid] = [ajs8_vAssetLocation].[_AssetGuid])
          LEFT OUTER JOIN [vFixedAssetResourceStatus] AS [ajs9_vFixedAssetResourceStatus]
             ON ([vri2_Computer].[Guid] = [ajs9_vFixedAssetResourceStatus].[Guid])
    WHERE
       (
          ([vri2_Computer].[Guid] IN (SELECT [ResourceGuid] FROM [ScopeMembership] WHERE [ScopeCollectionGuid] IN (SELECT [ScopeCollectionGuid] FROM dbo.fnGetTrusteeScopeCollections(@v1_TrusteeScope))))
       )

    Thx for the help!
     



  • 2.  RE: CMDB Tables - "Computer Type"

    Posted Nov 01, 2010 04:12 PM

    Use vRM_Computer_Type_Item instead



  • 3.  RE: CMDB Tables - "Computer Type"

    Posted Nov 02, 2010 09:55 AM

    DECLARE @v1_TrusteeScope nvarchar(max)
       SET @v1_TrusteeScope = N'%TrusteeScope%'
    SELECT
       [vri2_Computer].[Guid] AS [_ItemGuid],
       [vri2_Computer].[Name],
       [dca3_AeX AC TCPIP].[IP Address],
       [ajs8_vAssetLocation].[Location],
       [ajs9_vFixedAssetResourceStatus].[Status],
       [dca5_Identity].[System Number],
       [dca7_Manufacturer].[Manufacturer],
       [dca7_Manufacturer].[Model],
       [dca4_AeX AC Identification].[Last Logon User],
       [dca6_Serial Number].[Serial Number],
       [ajs10_vRM_Computer_Type_Item].[Name] AS [Computer Type]
    FROM
       [vRM_Computer_Item] AS [vri2_Computer]
          LEFT OUTER JOIN [Inv_AeX_AC_TCPIP] AS [dca3_AeX AC TCPIP]
             ON ([vri2_Computer].[Guid] = [dca3_AeX AC TCPIP].[_ResourceGuid])
          LEFT OUTER JOIN [Inv_AeX_AC_Identification] AS [dca4_AeX AC Identification]
             ON ([vri2_Computer].[Guid] = [dca4_AeX AC Identification].[_ResourceGuid])
          LEFT OUTER JOIN [Inv_Identity] AS [dca5_Identity]
             ON ([vri2_Computer].[Guid] = [dca5_Identity].[_ResourceGuid])
          LEFT OUTER JOIN [Inv_Serial_Number] AS [dca6_Serial Number]
             ON ([vri2_Computer].[Guid] = [dca6_Serial Number].[_ResourceGuid])
          LEFT OUTER JOIN [Inv_Manufacturer] AS [dca7_Manufacturer]
             ON ([vri2_Computer].[Guid] = [dca7_Manufacturer].[_ResourceGuid])
          LEFT OUTER JOIN [vAssetLocation] AS [ajs8_vAssetLocation]
             ON ([vri2_Computer].[Guid] = [ajs8_vAssetLocation].[_AssetGuid])
          LEFT OUTER JOIN [vFixedAssetResourceStatus] AS [ajs9_vFixedAssetResourceStatus]
             ON ([vri2_Computer].[Guid] = [ajs9_vFixedAssetResourceStatus].[Guid])
          LEFT OUTER JOIN [vRM_Computer_Type_Item] AS [ajs10_vRM_Computer_Type_Item]
             ON ([vri2_Computer].[Guid] = [ajs10_vRM_Computer_Type_Item].[Guid])
    WHERE
       (
          ([vri2_Computer].[Guid] IN (SELECT [ResourceGuid] FROM [ScopeMembership] WHERE [ScopeCollectionGuid] IN (SELECT [ScopeCollectionGuid] FROM dbo.fnGetTrusteeScopeCollections(@v1_TrusteeScope))))
       )
     

    But the result is this:

     



  • 4.  RE: CMDB Tables - "Computer Type"

    Posted Nov 02, 2010 04:39 PM

     

    It should be something like this:
    DECLARE @v1_TrusteeScope nvarchar(max)
       SET @v1_TrusteeScope = N'%TrusteeScope%'
    SELECT
       pc.[Guid] AS [_ItemGuid],
       pc.[Name],
       tcp.[IP Address],
       loc.[Location],
       va.[Status],
       va.[System Number],
       va.[Manufacturer],
       va.[Model],
       id.[Last Logon User],
       va.[Serial Number],
       pcType.[Name] AS [Computer Type]
    FROM
       [vRM_Computer_Item] AS pc
       LEFT JOIN [Inv_AeX_AC_TCPIP] AS tcp ON pc.[Guid] = tcp.[_ResourceGuid]
       LEFT JOIN [Inv_AeX_AC_Identification] AS id ON pc.[Guid] = id.[_ResourceGuid]
       LEFT JOIN vAsset va on pc.Guid = va._ResourceGuid
       LEFT JOIN [vAssetLocation] AS loc ON pc.[Guid] = loc.[_AssetGuid]
       LEFT JOIN ResourceAssociation ra on pc.Guid = ra.ParentResourceGuid
       LEFT JOIN vRM_Computer_Type_Item AS pcType ON ra.ChildResourceGuid = pcType.[Guid]
    WHERE
       (
          (pc.[Guid] IN (SELECT [ResourceGuid] FROM [ScopeMembership] WHERE [ScopeCollectionGuid] IN (SELECT [ScopeCollectionGuid] FROM dbo.fnGetTrusteeScopeCollections(@v1_TrusteeScope))))
       )
     
    I don't have access to an Altiris solution at the moment, the query is pure a reference for you, some column names may not be correct. But you can fine tune it to suit your need.


  • 5.  RE: CMDB Tables - "Computer Type"

    Posted Nov 04, 2010 08:36 AM

    I think that the source of the problem is te name of the table.

    There are severals tables, and I need to know the correct one. Thx for the help! I will keep looking.



  • 6.  RE: CMDB Tables - "Computer Type"
    Best Answer

    Posted Nov 09, 2010 10:05 AM

    If you whant to create a custom report about computer, the sql code is the following:

    DECLARE @v1_TrusteeScope nvarchar(max)

       SET @v1_TrusteeScope = N'%TrusteeScope%'

    DECLARE @g8_ComputerType uniqueidentifier

       SET @g8_ComputerType = '74c9ad4a-e384-477e-bd06-132a3368e58d'

    SELECT

       [vri2_Computer].[Guid] AS [_ItemGuid],

       [dca5_Identity].[System Number],

       [vri2_Computer].[Name],

       [dca7_Manufacturer].[Manufacturer],

       [dca7_Manufacturer].[Model],

       [dca6_Serial Number].[Serial Number],

       [ajs11_vAssetLocation].[Location],

       [dca4_AeX AC Identification].[Last Logon User],

       [ajs12_vFixedAssetResourceStatus].[Status],

       [dca3_AeX AC TCPIP].[IP Address],

       [vri10_Computer Type].[Name] AS [Computer Type],

       [vri10_Computer Type].[Guid] AS [_itemguid]

    FROM

       [vRM_Computer_Item] AS [vri2_Computer]

          LEFT OUTER JOIN [Inv_AeX_AC_TCPIP] AS [dca3_AeX AC TCPIP]

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

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

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

          LEFT OUTER JOIN [Inv_Identity] AS [dca5_Identity]

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

          LEFT OUTER JOIN [Inv_Serial_Number] AS [dca6_Serial Number]

             ON ([vri2_Computer].[Guid] = [dca6_Serial Number].[_ResourceGuid])

          LEFT OUTER JOIN [Inv_Manufacturer] AS [dca7_Manufacturer]

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

          LEFT OUTER JOIN ([ResourceAssociation] AS [ra9_Computer Type]

             LEFT OUTER JOIN [vRM_Computer_Type_Item] AS [vri10_Computer Type]

                ON ([ra9_Computer Type].[ChildResourceGuid] = [vri10_Computer Type].[Guid]))

             ON ([vri2_Computer].[Guid] = [ra9_Computer Type].[ParentResourceGuid])

          LEFT OUTER JOIN [vAssetLocation] AS [ajs11_vAssetLocation]

             ON ([vri2_Computer].[Guid] = [ajs11_vAssetLocation].[_AssetGuid])

          LEFT OUTER JOIN [vFixedAssetResourceStatus] AS [ajs12_vFixedAssetResourceStatus]

             ON ([vri2_Computer].[Guid] = [ajs12_vFixedAssetResourceStatus].[Guid])

    WHERE

       (

          (

             ([ra9_Computer Type].[ResourceAssociationTypeGuid] = @g8_ComputerType)

             OR

             ([ra9_Computer Type].[ResourceAssociationTypeGuid] IS NULL)

          )

          AND

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

          AND

          (

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

             OR

             ([vri10_Computer Type].[Guid] IS NULL)

          )

       )

     

    For computer type, we must enter this information in the edit option computer. Simple as that... :P