Asset management report

[Greg Thomas]

Looking for a report that will have the following columns: Computer name, Lease Expiration, Location, Mouse Deployed, Lease or Purchase, Last Logion user, Asset's Status and Asset Owners. Lease Expiration, Location, Mouse Deployed are all custom data classes that I have created. I can get everything except the Asset's Staus and Asset Owner (not primary user). This is the query I have started:

DECLARE @v1_TrusteeScope nvarchar(389)
   SET @v1_TrusteeScope = N'{0BFFDE93-E9EF-4398-8DCE-E7C52F2B1852},{11DFCCE3-F402-4C24-A773-F40499576D4A},{2E1F478A-4986-4223-9D1E-B5920A63AB41},{582029E2-FC5B-4717-8808-B80D6EF0FD67},{5B912C8E-8A5B-4979-9A89-5BE1914EF994},{7091A13C-55C3-4E51-B164-8955EE25E1C2},{B760E9A9-E4DB-404C-A93F-AEA51754AA4F},{CC0DBF2B-6DDD-4826-881C-A8CA2ADE4548},{E201E9A5-0A94-4633-88AE-689BD5C1008E},{F4889A82-C83E-4DD0-A179-C18F5D330710}'
SELECT
   [vri2_Computer].[Guid] AS [_ItemGuid],
   [vri2_Computer].[Name],
   [dca5_HP Order Details].[Lease Expiration],
   [dca5_HP Order Details].[Location],
   [dca5_HP Order Details].[Mouse deployed?],
   [dca4_Accounting Information].[Purchase or Lease],
   [dca3_AeX AC Identification].[Last Logon User]
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_Accounting_Information] AS [dca4_Accounting Information]
         ON ([vri2_Computer].[Guid] = [dca4_Accounting Information].[_ResourceGuid])
      LEFT OUTER JOIN [Inv_HP_Order_Details] AS [dca5_HP Order Details]
         ON ([vri2_Computer].[Guid] = [dca5_HP Order Details].[_ResourceGuid])
WHERE
   (
      ([vri2_Computer].[Guid] IN (SELECT [ResourceGuid] FROM [ScopeMembership] WHERE [ScopeCollectionGuid] IN (SELECT [ScopeCollectionGuid] FROM dbo.fnGetTrusteeScopeCollections(@v1_TrusteeScope))))
   )

Thanks in advance for any assistance on this.

[Igor Perevozchikov]

Hi GregST !

Try this query below.

 

DECLARE @v1_TrusteeScope nvarchar(MAX)
   SET @v1_TrusteeScope = N'{0BFFDE93-E9EF-4398-8DCE-E7C52F2B1852},{11DFCCE3-F402-4C24-A773-F40499576D4A},{2E1F478A-4986-4223-9D1E-B5920A63AB41},{582029E2-FC5B-4717-8808-B80D6EF0FD67},{5B912C8E-8A5B-4979-9A89-5BE1914EF994},{7091A13C-55C3-4E51-B164-8955EE25E1C2},{B760E9A9-E4DB-404C-A93F-AEA51754AA4F},{CC0DBF2B-6DDD-4826-881C-A8CA2ADE4548},{E201E9A5-0A94-4633-88AE-689BD5C1008E},{F4889A82-C83E-4DD0-A179-C18F5D330710}'
SELECT
   [vri2_Computer].[Guid] AS [_ItemGuid],
   [vri2_Computer].[Name],
   [dca5_HP Order Details].[Lease Expiration],
   [dca5_HP Order Details].[Location],
   [dca5_HP Order Details].[Mouse deployed?],
   [dca4_Accounting Information].[Purchase or Lease],
   [dca3_AeX AC Identification].[Last Logon User],
   [ajs5_vAC_ResourceOwner].[Owner] AS [Owner],
   vA.[Status] AS [AssetStatus]

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_Accounting_Information] AS [dca4_Accounting Information]
         ON [vri2_Computer].[Guid] = [dca4_Accounting Information].[_ResourceGuid]
      LEFT OUTER JOIN [Inv_HP_Order_Details] AS [dca5_HP Order Details]
         ON [vri2_Computer].[Guid] = [dca5_HP Order Details].[_ResourceGuid]
        LEFT OUTER JOIN [vAC_ResourceOwner] AS [ajs5_vAC_ResourceOwner]
         ON [vri2_Computer].[Guid] = [ajs5_vAC_ResourceOwner].[Guid]
        LEFT OUTER JOIN [vAsset] vA
         ON [vri2_Computer].Guid = vA.[_ResourceGuid]

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

   ORDER BY [vri2_Computer].[Name]
   ASC

 

Thanks,

IP.

[Greg Thomas]

That works exactly as I wanted!!!

 Thanks so much.

-Greg