Dear All,
I am new to Altiris and trying to create a customize query for reports.
My requirement is to create a report which gives me software and its count and when I drill down further (i.e., when I right click on the software) it should provide me details of that particular software with IP address, Hostname and the OU to which that system belongs.
For acheiving the desired results I wrote the below query :-
DECLARE @v1_TrusteeScope nvarchar(max)
SET @v1_TrusteeScope = N'%TrusteeScope%'
IF ('%Type%' = 'Managed Software')
SELECT spi.Guid
,spi.Name
,spv.[Version]
,company.Name AS Company
,cnt.cnt AS [Count]
,gad.[Distinguished Name]
FROM vRM_Software_Product_Item spi
JOIN
(SELECT COUNT(DISTINCT inst._ResourceGuid) AS cnt,ra.ParentResourceGuid
FROM ResourceAssociation ra
JOIN Inv_InstalledSoftware inst
ON inst._SoftwareComponentGuid = ra.ChildResourceGuid and InstallFlag = 1
WHERE ra.ResourceAssociationTypeGuid = '9D67B0C6-BEFF-4FCD-86C1-4A40028FE483' --product contains component
AND inst._ResourceGuid IN (SELECT ResourceGuid from ScopeMembership WHERE ScopeCollectionGuid IN(SELECT ScopeCollectionGuid FROM fnGetTrusteeScopeCollections (@v1_TrusteeScope)))
GROUP BY ra.ParentResourceGuid
)cnt
ON cnt.ParentResourceGuid = spi.[Guid]
JOIN Inv_Software_Product_State sps
ON sps._ResourceGuid = spi.[Guid]
AND sps.IsManaged = 1
LEFT JOIN Inv_Software_Product_Version spv
ON spv._ResourceGuid = spi.[Guid]
LEFT JOIN Inv_Global_Active_Directory_Details gad
ON gad._ResourceGuid = spi.[Guid]
LEFT JOIN (SELECT rc.Name, ra.ParentResourceGuid AS softProdGuid
FROM RM_ResourceCompany rc
JOIN ResourceAssociation ra
ON ra.ChildResourceGuid = rc.[Guid]
AND ra.ResourceAssociationTypeGuid = 'D5C66D5A-7686-4CA2-B7C1-AC980576CE1D')company
ON company.softProdGuid = spi.[Guid]
WHERE ('%Name%' = '%' OR LOWER (spi.Name) LIKE LOWER ('%Name%'))
AND ('%Version%' = '%' OR spv.[Version] LIKE '%Version%')
AND ('%Company%' = '%' OR LOWER (company.Name) LIKE LOWER ('%Company%'))
AND spi.[CreatedDate] >= '%Discovered since%'
ORDER BY spi.Name
ELSE
IF ('%Type%' = 'All Software')
SELECT sci.[Guid]
,sci.Name
,sc.[Version]
,company.Name AS [Company]
,inst.cnt AS [Count]
,agad.[Distinguished Name]
FROM vRM_Software_Component_Item sci
JOIN Inv_Software_Component sc
ON sci.Guid = sc._ResourceGuid
JOIN (SELECT COUNT(DISTINCT _ResourceGuid) AS cnt, _SoftwareComponentGuid
FROM Inv_InstalledSoftware
WHERE InstallFlag = 1
AND _ResourceGuid IN (SELECT ResourceGuid from ScopeMembership WHERE ScopeCollectionGuid IN(SELECT ScopeCollectionGuid FROM fnGetTrusteeScopeCollections (@v1_TrusteeScope)))
GROUP BY _SoftwareComponentGuid)inst
ON inst._SoftwareComponentGuid = sci.Guid
LEFT JOIN (SELECT ra.ParentResourceGuid AS sofCompGuid, rc.Name
FROM RM_ResourceCompany rc
JOIN ResourceAssociation ra
ON ra.ChildResourceGuid = rc.Guid
AND ra.ResourceAssociationTypeGuid = '292DBD81-1526-423A-AE6D-F44EB46C5B16')company
ON company.sofCompGuid = sci.Guid
LEFT JOIN Inv_Global_Active_Directory_Details agad
ON agad._ResourceGuid = sci.[Guid]
WHERE ('%Name%' = '%' OR LOWER (sci.Name) LIKE LOWER ('%Name%'))
AND ('%Version%' = '%' OR sc.[Version] LIKE '%Version%')
AND ('%Company%' = '%' OR LOWER (company.Name) LIKE LOWER ('%Company%'))
AND sci.[CreatedDate] >= '%Discovered since%'
ORDER BY sci.Name
The above query when run in database gives the result command completed successsfully, but when we run the same query to generate reports it fails.
Any suggestions..???
Thanks in advance