Report on Contract by Location

[Amrit]

Looking for an urgent assistance in my query below:

My customer is using Symantec Asset Management Suite 7.5. When I link an asset to a new contract and add a location to the contract, I can view this contract details under Manage>Organization Views and Groups>Contracts by Location. However, I see the source NS that this contract is applied to. Is this the default option? Is there any report that can be generated which will show the individual asset name associated with a contract. I am looking for a report which will look like:

 

Contract Type	Asset	Asset Type	Location	User
AMC	COM1234	Computer	Pune	ABC

 

Is this possible? I did not find any out of box report to achieve this. Any way I can create a custom report?

[Alex Hedley]

You could turn the following SQL into a Report

DECLARE @ComputerName AS nvarchar(50) = ''
DECLARE @ContractGuid AS UniqueIdentifier = ''

SELECT 
	vc.[Contract Type]
	,va.Name AS [AssetName]
	,rt.[Name] AS [AssetType]
	,vl.Name AS [Location]
	--,ru.Name AS [Owner]
	,ru2.Name AS [ContractOwner]
FROM vContract vc
	INNER JOIN [Inv_Contract_Details] icd ON icd._ResourceGuid = vc._ResourceGuid
	INNER JOIN CollectionMembership cm ON cm.CollectionGuid = icd.[Applies To]
	INNER JOIN vRM_Asset_Item va ON cm.ResourceGuid = va.Guid
	INNER JOIN ResourceType rt ON rt.Guid = va.ResourceTypeGuid
	LEFT JOIN ResourceAssociation ra ON ra.ParentResourceGuid = vc._ResourceGuid AND ra.ResourceAssociationTypeGuid = '989AF046-7279-43C6-9C63-B2CEEA631AE9' --Contract's Location
	LEFT JOIN vLocation vl ON ra.ChildResourceGuid = vl._ResourceGuid
	--LEFT JOIN ResourceAssociation ra2 ON ra2.ParentResourceGuid = va.Guid and ra2.ResourceAssociationTypeGuid = 'ed35a8d1-bf60-4771-9dde-092c146c485a' --Asset User Owners
	--LEFT JOIN RM_ResourceUser ru on ra2.ChildResourceGuid = ru.Guid
	LEFT JOIN ResourceAssociation ra3 ON ra3.ParentResourceGuid = vc._ResourceGuid AND ra3.ResourceAssociationTypeGuid = 'E58BA278-A69A-4C65-8D20-BE5771FB1CF1' --Contract's Assigned User
	LEFT JOIN RM_ResourceUser ru2 on ra3.ChildResourceGuid = ru2.Guid
WHERE 
	vc._ResourceGuid = @ContractGuid
	AND va.Name = @ComputerName

 

[Amrit]

Thanks for providing this query. While running this query and selecting Object explorer as vContract, it gives the error: "This datasource is not in a runnable state".

 

Please find attached acreenshot.Am I missing something here?

 

 

[Alex Hedley]

If you create a New | Report | SQL Report

Copy in the SQL, update the parameters

DECLARE @ComputerName AS nvarchar(50) = N'%ComputerName%'
DECLARE @ContractGuid AS UniqueIdentifier = N'%ContractGuid%'

Add these parameters in Query Parameters and then Report Parameters and set a default value.

In the value Provider set "Basic Parameter Value Edit Control" for 'ComputerName', add a Label Text if you'd like.

In the value Provider set "Resource Selector Parameter Value Edit Control" for 'ContractGuid'