Asset Management Suite

 View Only

 Asset reports

Greg Thomas's profile image
Greg Thomas posted May 11, 2021 01:44 PM
We are moving, not upgrading, from 8.1 to 8.6 in the near future. Since there is no way to bulk export asset data I was hoping to create a report in 8.1 that I could export the data then be able to import using an ODBC connector. I've got most of the report done using the builder, I am not an SQL guy so any help would be appreciated. First off the report I built I need the Asset status. In the builder it shows Retired, Active, etc., but in the report output it only shows Active machines. Next I am trying to get the Comment field and Stockroom Details (which only shows if asset is In Stock). Thanks in advance for any help. 


Code:

SELECT DISTINCT
[vri1_Computer].[Guid] AS [Guid],
[vri1_Computer].[Name] AS [Name],
[dca5_HP Order Details].[Invoice Number] AS [Invoice Number],
[dca5_HP Order Details].[Lease Expiration] AS [Lease Expiration],
[dca5_HP Order Details].[Location] AS [Location],
[dca5_HP Order Details].[Mouse deployed?] AS [Mouse deployed?],
[dca4_Accounting Information].[Purchase or Lease] AS [Purchase or Lease],
[dca3_AeX AC Identification].[Last Logon User] AS [Last Logon User],
[dca2_AeX AC TCPIP].[IP Address] AS [IP Address],
[ajs9_vAC_ResourceOwner].[Owner] AS [Owner],
[ajs13_vHWChassis].[Serial Number] AS [Serial Number],
[ajs11_vAsset].[Status] AS [Status]
FROM
[vRM_Computer_Item] AS [vri1_Computer]
LEFT OUTER JOIN [Inv_AeX_AC_TCPIP] AS [dca2_AeX AC TCPIP]
ON ([vri1_Computer].[Guid] = [dca2_AeX AC TCPIP].[_ResourceGuid])
LEFT OUTER JOIN [Inv_AeX_AC_Identification] AS [dca3_AeX AC Identification]
ON ([vri1_Computer].[Guid] = [dca3_AeX AC Identification].[_ResourceGuid])
LEFT OUTER JOIN [Inv_Accounting_Information] AS [dca4_Accounting Information]
ON ([vri1_Computer].[Guid] = [dca4_Accounting Information].[_ResourceGuid])
LEFT OUTER JOIN [Inv_HP_Order_Details] AS [dca5_HP Order Details]
ON ([vri1_Computer].[Guid] = [dca5_HP Order Details].[_ResourceGuid])
LEFT OUTER JOIN ([ScopeMembership] AS [ajs6_ScopeMembership]
LEFT OUTER JOIN [FolderBaseFolder] AS [ajs7_FolderBaseFolder]
ON ([ajs6_ScopeMembership].[ScopeCollectionGuid] = [ajs7_FolderBaseFolder].[FolderGuid]))
ON ([vri1_Computer].[Guid] = [ajs6_ScopeMembership].[ResourceGuid])
LEFT OUTER JOIN [vAC_AssetStatus] AS [ajs8_vAC_AssetStatus]
ON ([vri1_Computer].[Guid] = [ajs8_vAC_AssetStatus].[Guid])
LEFT OUTER JOIN [vAC_ResourceOwner] AS [ajs9_vAC_ResourceOwner]
ON ([vri1_Computer].[Guid] = [ajs9_vAC_ResourceOwner].[Guid])
LEFT OUTER JOIN [vAllComputerInfo] AS [ajs10_vAllComputerInfo]
ON ([vri1_Computer].[Guid] = [ajs10_vAllComputerInfo].[Guid])
LEFT OUTER JOIN [vAsset] AS [ajs11_vAsset]
ON ([vri1_Computer].[Guid] = [ajs11_vAsset].[_ResourceGuid])
LEFT OUTER JOIN [vCMDBComputer] AS [ajs12_vCMDBComputer]
ON ([vri1_Computer].[Guid] = [ajs12_vCMDBComputer].[Guid])
LEFT OUTER JOIN [vHWChassis] AS [ajs13_vHWChassis]
ON ([vri1_Computer].[Guid] = [ajs13_vHWChassis].[_ResourceGuid])
WHERE [vri1_Computer].[Guid] IN (

SELECT computer.[Guid] FROM
(
SELECT
[vri1_Computer].[Guid]
FROM
[vRM_Computer_Item] AS [vri1_Computer]
LEFT OUTER JOIN ([ScopeMembership] AS [ajs2_ScopeMembership]
LEFT OUTER JOIN [FolderBaseFolder] AS [ajs3_FolderBaseFolder]
ON ([ajs2_ScopeMembership].[ScopeCollectionGuid] = [ajs3_FolderBaseFolder].[FolderGuid]))
ON ([vri1_Computer].[Guid] = [ajs2_ScopeMembership].[ResourceGuid])
WHERE
(
(
([ajs3_FolderBaseFolder].[ParentFolderGuid] = N'91c68fcb-1822-e793-b59c-2684e99a64cd')
)
)

) AS computer
INNER JOIN fnGetTrusteeScopedResourcesByType('493435f7-3b17-4c4c-b07f-c23e7ab7781f','%TrusteeScope%',1) AS [fnGTSR_3] ON computer.[Guid] = [fnGTSR_3].[ResourceGuid]
INNER JOIN vComputer AS [vCMP_4] ON computer.[Guid] = [vCMP_4].[Guid]

)
GROUP BY
[dca2_AeX AC TCPIP].[IP Address],
[dca3_AeX AC Identification].[Last Logon User],
[dca4_Accounting Information].[Purchase or Lease],
[dca5_HP Order Details].[Invoice Number],
[dca5_HP Order Details].[Lease Expiration],
[dca5_HP Order Details].[Mouse deployed?],
[dca5_HP Order Details].[Location],
[ajs9_vAC_ResourceOwner].[Owner],
[ajs11_vAsset].[Status],
[ajs13_vHWChassis].[Serial Number],
[vri1_Computer].[Guid],
[vri1_Computer].[Name]
ORDER BY
[IP Address],
[Last Logon User],
[Purchase or Lease],
[Invoice Number],
[Lease Expiration],
[Mouse deployed?],
[Location],
[Owner],
[Status],
[Serial Number]
andykn101's profile image
andykn101
Your problem may be around the INNER JOIN vComputer AS [vCMP_4] ON computer.[Guid] = [vCMP_4].[Guid] section, I think vComputer is Active computers only.

Could you not use Connector Solution to export resources and import?
Greg Thomas's profile image
Greg Thomas
Thanks but I don't know how to fix this. When I do the report builder it shows other Status types (Retired, In Stock, etc), but when I generate the report it only shows active computers. And yes, I meant data connector (brain fart), but I need an accurate report in order to export.

SELECT DISTINCT
[vri1_Computer].[Guid] AS [Guid],
[vri1_Computer].[Name] AS [Name],
[ajs11_vHWChassis].[Serial Number] AS [Serial Number],
[dca5_Accounting Information].[Purchase or Lease] AS [Purchase or Lease],
[dca4_AeX AC Identification].[Last Logon User] AS [Last Logon User],
[ajs9_vAC_ResourceOwner].[Owner] AS [Owner],
[dca6_HP Order Details].[Lease Expiration] AS [Lease Expiration],
[dca6_HP Order Details].[Invoice Number] AS [Invoice Number],
[dca6_HP Order Details].[Location] AS [Location],
[dca6_HP Order Details].[Mouse deployed?] AS [Mouse deployed?],
[ajs10_vAsset].[Status] AS [Status],
[dca3_AeX AC TCPIP].[IP Address] AS [IP Address],
[dca2_Comment].[Comment] AS [Comment],
[dca5_Accounting Information].[Stock Room Details] AS [Stock Room Details]
FROM
[vRM_Computer_Item] AS [vri1_Computer]
LEFT OUTER JOIN [Inv_Comment] AS [dca2_Comment]
ON ([vri1_Computer].[Guid] = [dca2_Comment].[_ResourceGuid])
LEFT OUTER JOIN [Inv_AeX_AC_TCPIP] AS [dca3_AeX AC TCPIP]
ON ([vri1_Computer].[Guid] = [dca3_AeX AC TCPIP].[_ResourceGuid])
LEFT OUTER JOIN [Inv_AeX_AC_Identification] AS [dca4_AeX AC Identification]
ON ([vri1_Computer].[Guid] = [dca4_AeX AC Identification].[_ResourceGuid])
LEFT OUTER JOIN [Inv_Accounting_Information] AS [dca5_Accounting Information]
ON ([vri1_Computer].[Guid] = [dca5_Accounting Information].[_ResourceGuid])
LEFT OUTER JOIN [Inv_HP_Order_Details] AS [dca6_HP Order Details]
ON ([vri1_Computer].[Guid] = [dca6_HP Order Details].[_ResourceGuid])
LEFT OUTER JOIN ([ScopeMembership] AS [ajs7_ScopeMembership]
LEFT OUTER JOIN [FolderBaseFolder] AS [ajs8_FolderBaseFolder]
ON ([ajs7_ScopeMembership].[ScopeCollectionGuid] = [ajs8_FolderBaseFolder].[FolderGuid]))
ON ([vri1_Computer].[Guid] = [ajs7_ScopeMembership].[ResourceGuid])
LEFT OUTER JOIN [vAC_ResourceOwner] AS [ajs9_vAC_ResourceOwner]
ON ([vri1_Computer].[Guid] = [ajs9_vAC_ResourceOwner].[Guid])
LEFT OUTER JOIN [vAsset] AS [ajs10_vAsset]
ON ([vri1_Computer].[Guid] = [ajs10_vAsset].[_ResourceGuid])
LEFT OUTER JOIN [vHWChassis] AS [ajs11_vHWChassis]
ON ([vri1_Computer].[Guid] = [ajs11_vHWChassis].[_ResourceGuid])
WHERE [vri1_Computer].[Guid] IN (

SELECT computer.[Guid] FROM
(
SELECT
[vri1_Computer].[Guid]
FROM
[vRM_Computer_Item] AS [vri1_Computer]
LEFT OUTER JOIN ([ScopeMembership] AS [ajs2_ScopeMembership]
LEFT OUTER JOIN [FolderBaseFolder] AS [ajs3_FolderBaseFolder]
ON ([ajs2_ScopeMembership].[ScopeCollectionGuid] = [ajs3_FolderBaseFolder].[FolderGuid]))
ON ([vri1_Computer].[Guid] = [ajs2_ScopeMembership].[ResourceGuid])
WHERE
(
(
([ajs3_FolderBaseFolder].[ParentFolderGuid] = N'91c68fcb-1822-e793-b59c-2684e99a64cd')
)
)

) AS computer
INNER JOIN fnGetTrusteeScopedResourcesByType('493435f7-3b17-4c4c-b07f-c23e7ab7781f','%TrusteeScope%',1) AS [fnGTSR_3] ON computer.[Guid] = [fnGTSR_3].[ResourceGuid]
INNER JOIN vComputer AS [vCMP_4] ON computer.[Guid] = [vCMP_4].[Guid]

)
GROUP BY
[dca2_Comment].[Comment],
[dca3_AeX AC TCPIP].[IP Address],
[dca4_AeX AC Identification].[Last Logon User],
[dca5_Accounting Information].[Purchase or Lease],
[dca5_Accounting Information].[Stock Room Details],
[dca6_HP Order Details].[Invoice Number],
[dca6_HP Order Details].[Lease Expiration],
[dca6_HP Order Details].[Mouse deployed?],
[dca6_HP Order Details].[Location],
[ajs9_vAC_ResourceOwner].[Owner],
[ajs10_vAsset].[Status],
[ajs11_vHWChassis].[Serial Number],
[vri1_Computer].[Guid],
[vri1_Computer].[Name]
ORDER BY
[Comment],
[IP Address],
[Last Logon User],
[Purchase or Lease],
[Stock Room Details],
[Invoice Number],
[Lease Expiration],
[Mouse deployed?],
[Location],
[Owner],
[Status],
[Serial Number]
andykn101's profile image
andykn101
I think you can just delete that line from the query:
INNER JOIN vComputer AS [vCMP_4] ON computer.[Guid] = [vCMP_4].[Guid]

It doesn't seem to be used anywhere else
Greg Thomas's profile image
Greg Thomas
Thank you, thank you, thank you!
That did the trick!
andykn101's profile image
andykn101
Happy to help and thanks for letting us know that worked.