Database table which has list of applications for an license

[csganesh]

Hi Team,

I am looking for an DB table, which holds the information of list of applications which comes under the license. I have looked at the table SoftwareLicenseByApplication but it has fewer applications listed than what we see in the UI. Also the table which has the entitlement details (consumed, available and total) which is showed on the license properties compliance page.

Let me know if any one know which table holds the information.

Thanks,

Ganesh

[kclausen]

@csganesh  - The name of the View you want to use is SoftwareTitleLicense.  This joins the Software License to a Software Title.  Here is an example SQL Query:

select l.name as 'License',pu.PublisherName,s.FullName as 'Software Title'
from SoftwareTitleLicense t
join SoftwareLicense l on l.SoftwareLicenseID=t.SoftwareLicenseID
JOIN SoftwareTitle s on s.SoftwareTitleID=t.SoftwareTitleID
JOIN SoftwareTitleProduct pr on pr.SoftwareTitleProductID=s.SoftwareTitleProductID
JOIN SoftwareTitlePublisher pu on pu.SoftwareTitlePublisherID=pr.SoftwareTitlePublisherID
order by l.name,s.FullName

[csganesh]

Hi Kclausen,

The query lists all the possible applications which can be mapped to that license, but on the UI when we search for applications under each license it has two sections one as "Applications" and another one "Applications covered by upgrade/downgrade rights" which value in DB differentiate between these two sections. 

Attached an sample page for license with two sections on the applications tab. (highlighted two sections)

[kclausen]

@csganesh - The query that I provided returns all of the applications that are entitled by the license.  This includes both the main applications and all of the additional applications based on Upgrade and Downgrade Rights.

[csganesh]

Is there a value with i can differentiate between both these at the DB on the softwaretitlelicense table or any other table in complicane DB holds this information.  I am working an to extract only the main applications for the license, so need an value to differentiate between these two.  

[kclausen · Accepted solution]

Here is a modified query that contains a "Type" column.  Any software title where the type has a value of "CURRENT" is listed in the top portion of the Applications tab.  If remove the comment on the WHERE Clause, then only the main applications are included.

select l.name as 'License',pu.PublisherName,s.FullName as 'Software Title',
REPLACE(REPLACE(r.ReasonDefaultValue,'[',''),']','') as 'Type'
from SoftwareTitleLicense t
join SoftwareLicense l on l.SoftwareLicenseID=t.SoftwareLicenseID
JOIN SoftwareTitle s on s.SoftwareTitleID=t.SoftwareTitleID
JOIN SoftwareTitleProduct pr on pr.SoftwareTitleProductID=s.SoftwareTitleProductID
JOIN SoftwareTitlePublisher pu on pu.SoftwareTitlePublisherID=pr.SoftwareTitlePublisherID
JOIN softwaretitlelicensereason r on r.SoftwareTitleLicenseReasonID=t.SoftwareTitleLicenseReasonID
--WHERE r.ReasonDefaultValue like '%current%'
order by l.name,s.FullName

[csganesh]

the value for software title reason has helped on identifying the applications only corresponding to the license 

[Ralph_Crowley]

Hi Kclausen, are you aware of any limitations for running this type of query for a service account? We have tried to setup an alteryx workflow to grab software license data directly from SQL, & even though we granted the alteryx service accout admin rights, it doesn't natively have access to the SQL data. However once we launch the FlexNet UI (using the alteryx service account), then it has access to the data for about 2 weeks. We then need to repeat the process of launching the UI with the alteryx service account. I opened a support ticket, & the response was this was working as designed.. and that direct access to the SQL data is not supported. 

[kclausen]

@Ralph_Crowley 

You shouldn't need an FNMS account.  You only need an account that your SQL Server DBA has set up to have access to your FNMSCompliance Database.  You are not logging into FNMS with this account, you are only connecting to the SQL Server database that is hosting the FNMS data.

[Ralph_Crowley]

I would have thought so too, but granting DB_Owner permissions to the FNMSCompliance provides access to all objects (tables, views, function), but not the actual data. All queries just load a blank result unless launching the UI with the same account