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!
‎Sep 09, 2024 07:33 AM
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.
‎Sep 10, 2024 03:31 AM
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.
‎Sep 11, 2024 02:17 AM
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
‎Sep 11, 2024 04:20 AM
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.
‎Sep 11, 2024 04:59 AM - edited ‎Sep 11, 2024 06:21 AM