flexeranoob
Frequent contributor

All Inventory Query

Dear All, 

 

Can someone help me with the Query that runs when you make an All Inventory request? 

 

Also, IS there a way for ComplianceComputerID to show up on the All Inventory page? It is not one of the columns at the moment.

0 Kudos
5 Replies
erwinlindemann
Consultant

Re: All Inventory Query

In the FNMS data model, for most views exposed to the FNMS Web UI, there is a corresponding database view in the SQL Server database. Most of these database views have <Grid_> as a prefix in their name.

For the 'Discovery & Inventory > Inventory > All Inventory' view in the FNMS UI, the underlying database view is named [Grid_Inventory]. To retrieve all inventory data from the database, you have to run the following statements from SQL Server Management Studio (SSMS):

EXECUTE AS User='<domain>\<user>'
SELECT * FROM [Grid_Inventory]

The 'EXECUTE AS ..' statement is required because the SELECT query will run using the credentials of the user logged into SQL Server Management Studio. In case this user is a local SQL Server account or a domain user not registered as an account in FNMS, the query will not return any data. Running 'EXECUTE AS ..' with a valid user account from FNMS in SSMS will make sure that subsequent queries will return the same data this user would see in the FNMS Web UI.

The [Grid_Inventory] SQL view has a column for the internal [ComplianceComputerID] value, too. If you want to expose this internal column in the FNMS Web UI, you would need to design a custom report.

flexeranoob
Frequent contributor

Re: All Inventory Query

THank you!  I tried this, but even after using a user who has all privileges, it is giving me an error "The server principal "IN\Username" is not able to access the database "FNMSCompliance" under the current security context.

Not sure what the issue is, because when I logon using this user, I am able to see a view. 

0 Kudos
erwinlindemann
Consultant

Re: All Inventory Query

The user "IN\<Username>" would need to be registered in SQL Server on the [FNMSCompliance] database, either directly or as member of a group.

For testing purposes, I would suggest using another user that is already registered with access to the [FNMSCompliance] database. Good candidates are the FNMS service user account (default name: 'svc-flexnet'), or the standard FNMS Administrator account (default name: 'FNMS-Admin') instead of the actual FNMS user:

EXECUTE AS USER '<domain>\FNMS-Admin'



kclausen
Flexera
Flexera

Re: All Inventory Query

@flexeranoob - The only account that has permissions to access the physical database is your FlexNet Manager Service Account.

When a user logs into the FlexNet Manager user interface, it is actually the Service Account that is accessing the database on that user's behalf.

When you run the query, you should either be running it as the Service Account.

To use a different account, you would need to work with your SQL Server DBA to add that User Account as a logon to the SQL Server database.