Reporting what ITMS solutions are installed on SMP Server

Products

• IT Management Suite

Issue/Introduction

Is there a SQL Query that will list all Solutions installed and their version?

Environment

ITMS 8.x

Resolution

You can use the following query:

select vProduct.[Guid],
REPLACE(vProduct.[Name], ', ',' ') as [Product Name],
REPlACE(vProduct.Description,', ',' ') as [Product Description],
substring(vItem.[State],
    charindex('<version>',vItem.[State],1)+9,
    (charindex('</version>',vItem.[State],1) - (charindex('<version>',vItem.[State],1)+9))) [Version],
(case charindex('<msiVersion>',vItem.[State],1) 
    when 0
        then 'No MSI Version available'
    Else substring(vItem.[State],
    charindex('<msiVersion>',vItem.[State],1)+12,
    (charindex('</msiVersion>',vItem.[State],1) - (charindex('<msiVersion>',vItem.[State],1)+12)))
end) as [MSI Version]
from vProduct
join vItem on vProduct.Guid = vItem.Guid
order by vProduct.[Name]

As well, you can use the attached "Installed Solutions.xml" to import it as a Report on your SMP Server.