SQL query to prepare a list of devices with installed software

[maheshpatil]

Hello,

Requesting help or guidance to build a SQL query to prepare a view/report based on FlexNet Manager Suite's software and device data which include below columns,

1. Name
2. Product
3. Installed
4. Publisher
5. Classification
6. Application Category
7. Status
8. Category
9. End of Support
10. End of Extended Support
11. End of Life
12. Device Name
13. Calculated User (Device Owner)

Thanks!

[mfranz]

You can just combine most of these attributes in the Report Builder:

Of you start with "Application" the report will contain the whole catalog. If you just want applications actually installed, you can start with "Installation" or apply a filter in the 2nd step.

[maheshpatil]

Thanks for the reply but our requirement is to create a SQL view with the sepcified columns. So any help with table names would be appriciated.

[mfranz]

That should get you started:

SELECT
	cc.ComputerName
	,cu.UserName
	,st.FullName
FROM InstalledApplications ia
JOIN ComplianceComputer cc
	ON ia.ComplianceComputerID = cc.ComplianceComputerID
LEFT JOIN ComplianceUser cu
	ON cc.CalculatedUserID = cc.ComplianceUserID
JOIN SoftwareTitle st
	ON ia.SoftwareTitleID = st.SoftwareTitleID

[ppyrzynski]

Or something like this:

select i.SoftwareTitleName as Name
,i.ProductName as Product
,i.Publisher
,sti.ClassificationDefaultValue as [Classification]
,sti.Category as [Application Category]
,sta.ActionDefaultValue as [Application Status]
,i.ComputerName
,ccs.DefaultValue as [Computer Status]
,c.Path as [Computer Category]
,st.CustomEndOfSupportLifeDate as [End of support]
,st.ExtendedSupportUntil as [End of Extended Support]
,st.EndOfLifeDate as [End of Life]
,cu.UserName as [Calculated User (Device Owner)]
from InstalledApplicationInfo i inner join 
SoftwareTitleInfo sti on i.SoftwareTitleID=sti.SoftwareTitleID inner join
SoftwareTitle_S st on i.SoftwareTitleID=st.SoftwareTitleID inner join
ComplianceComputer cc on i.ComplianceComputerId=cc.ComplianceComputerID left join
ComplianceComputerStatusI18N ccs on cc.ComplianceComputerStatusID=ccs.ComplianceComputerStatusID left join
ComplianceUser cu on cc.ComplianceUserID=cu.ComplianceUserID left join
SoftwareTitleActionI18N sta on st.SoftwareTitleActionID=sta.SoftwareTitleActionID left join
Category c on cc.CategoryID=c.GroupExID
--where i.ComputerName like 'sample' --uncomment to limit results
order by i.ComputerName 

but please verify the results. Note that, at least in my case, the query takes about 4 minutes to complete and returns 2 mln+ rows. 
If it's not showing expected data you will at least have table names and columns to look into.