Hi SaschaH your query worked and i am ready with report what i needed most.
As always your comments will be solution or will guide towards it ... Impressed and heart full of thanks.
Final Query is below: - which gives all details (Computer name, serial no, Make, Model, IP Address, Subnet, Task server, Site, (Managed) Application name, Version, company)
***********************************************************************
DECLARE @v1_TrusteeScope nvarchar(max)
SET @v1_TrusteeScope = N'%TrusteeScope%'
IF ('%Type%' = 'Managed Software')
SELECT DISTINCT comps.Guid [Guid]
,comps.Name [Computer Name],
s.[Identifying Number] [Serial Number],
s.[Manufacturer] [System Manufacturer],
s.[Model] [Computer Model]
,[tp].[IP Address]
,[tp].[Subnet]
,[cid].[OS Name]
,srv.[Name] [TaskServer]
,site.[Name] [SiteName]
,prods.[Name]
,spv.[Version]
,company.Name [Company]
FROM vRM_Software_Component sc
JOIN Inv_InstalledSoftware inst
ON inst._SoftwareComponentGuid = sc.Guid
AND inst.InstallFlag = 1
JOIN (SELECT ra.ChildResourceGuid, spi.Guid, spi.Name
FROM vRM_Software_Product_Item spi
JOIN ResourceAssociation ra
ON ra.ParentResourceGuid = spi.Guid
AND ra.ResourceAssociationTypeGuid = '9D67B0C6-BEFF-4FCD-86C1-4A40028FE483'
)prods
ON prods.ChildResourceGuid = sc.Guid
JOIN dbo.Inv_Software_Product_State sps
ON sps._ResourceGuid = prods.Guid
AND sps.IsManaged = 1
JOIN dbo.Inv_Software_Product_Version spv
ON spv._ResourceGuid = prods.Guid
JOIN (SELECT vci.Guid, vci.Name
FROM vRM_Computer_Item vci
LEFT JOIN ResourceAssociation resAssoc
ON vci.Guid = resAssoc.ParentResourceGuid
AND resAssoc.ResourceAssociationTypeGuid = '3028166F-C0D6-41D8-9CB7-F64852E0FD01' --Asset Status
WHERE (resAssoc.ChildResourceGuid = '0A0203A5-D2B6-49f1-A53B-5EC31A89437C' OR resAssoc.ChildResourceGuid IS NULL) -- ONLY Active Computers
AND vci.Guid IN (SELECT ResourceGuid from ScopeMembership WHERE ScopeCollectionGuid IN(SELECT ScopeCollectionGuid FROM fnGetTrusteeScopeCollections (@v1_TrusteeScope)))
) comps
ON comps.Guid = inst._ResourceGuid
JOIN dbo.vHWComputerSystem s
ON s.[_ResourceGuid]=comps.Guid
JOIN [Inv_AeX_AC_TCPIP] AS [tp]
ON [tp].[_ResourceGuid]=comps.Guid
JOIN [Inv_AeX_AC_Identification] AS [cid]
ON [cid].[_ResourceGuid]=comps.Guid
JOIN [dbo].[Inv_Client_Task_Resources] ctr on ctr.[_ResourceGuid] = comps.GUID
JOIN [dbo].vComputer srv on srv.Guid = ctr.ClientTaskServerGuid
INNER JOIN (
SELECT ra1.ParentResourceGuid [ComputerGuid]
, ra1.ChildResourceGuid [SiteGuid]
FROM ResourceAssociation ra1
WHERE ra1.ResourceAssociationTypeGuid = '5E8E3C61-A80C-4b0a-A228-DBF97607CEE4'
) t1
ON t1.ComputerGuid = srv.[Guid]
JOIN [dbo].vSite site ON site.Guid = t1.SiteGuid
LEFT JOIN (SELECT ra.ParentResourceGuid AS softProdGuid, rc.Name
FROM RM_ResourceCompany rc
JOIN ResourceAssociation ra
ON ra.ChildResourceGuid = rc.Guid
AND ra.ResourceAssociationTypeGuid = 'D5C66D5A-7686-4CA2-B7C1-AC980576CE1D'
) company
ON company.softProdGuid = spv._ResourceGuid
WHERE ('%Computer%' = '%' OR LOWER (comps.Name) LIKE LOWER ('%Computer%'))
ORDER BY prods.Name, comps.Name
ELSE
IF ('%Type%' = 'All Software')
SELECT DISTINCT comps.Guid [Guid]
,comps.Name [Computer Name]
,sci.[Name]
,isc.[Version]
,company.[Name] [Company]
FROM vRM_Software_Component_Item sci
JOIN Inv_InstalledSoftware inst
ON inst._SoftwareComponentGuid = sci.[Guid]
AND inst.InstallFlag = 1
JOIN (SELECT vci.Guid, vci.Name
FROM vRM_Computer_Item vci
LEFT JOIN ResourceAssociation resAssoc
ON vci.Guid = resAssoc.ParentResourceGuid
AND resAssoc.ResourceAssociationTypeGuid = '3028166F-C0D6-41D8-9CB7-F64852E0FD01' --Asset Status
WHERE (resAssoc.ChildResourceGuid = '0A0203A5-D2B6-49f1-A53B-5EC31A89437C' OR resAssoc.ChildResourceGuid IS NULL) -- ONLY Active Computers
AND vci.Guid IN (SELECT ResourceGuid from ScopeMembership WHERE ScopeCollectionGuid IN(SELECT ScopeCollectionGuid FROM fnGetTrusteeScopeCollections (@v1_TrusteeScope)))
) comps
ON comps.Guid = inst._ResourceGuid
JOIN dbo.Inv_Software_Component isc
ON isc._ResourceGuid = sci.[Guid]
LEFT JOIN (SELECT vc.Name, ra.ParentResourceGuid AS SoftCompGuid
FROM RM_ResourceCompany vc
JOIN ResourceAssociation ra
ON vc.Guid = ra.ChildResourceGuid
AND ra.ResourceAssociationTypeGuid = '292DBD81-1526-423A-AE6D-F44EB46C5B16')company
ON company.SoftCompGuid = sci.[Guid]
WHERE ('%Computer%' = '%' OR LOWER (comps.Name) LIKE LOWER ('%Computer%'))
ORDER BY sci.Name, comps.Name
**********************************************************************************