Is there a SQL Query that will list all solutions installed and their version?
Answer
You can use the following query:
select scu.name as [Installed Solutions], substring ( i.state, charindex('<msiversion>', i.state) + 12, (charindex('</msiversion>',i.state) - charindex('<msiversion>',i.state) - 12)) as [Product Version]
from item i inner join solutioncenterupdate scu on i.guid = scu.guid
where scu.installed = 1
and i.state like '%</msiversion>%'
order by [Installed Solutions]
Note: The solution "Altiris Application Metering Language Pack" does not conform to the same XML structure as the other solutions, so it will not return a result. For that reason, the filter and i.state like '%</msiversion>%' was added the query so as not to return an "Invalid length parameter passed to the SUBSTRING function" error.
Please refer to KB article 49694 for a report that works with Symantec Management Platform 7.