How to automatically retrieve FNMS view?

[bleepie]

Hi,

In FNMS when going to Management View Index I can see all of our views. A client of us wants to retrieve a specific view in .csv. format. I can manually download this view and provide it to the client, but we would like to automate this proces. For example, retrieving this view from the database and save it to a log file in a directory the client can access. 

How would I do this (or if there is a alternative way, please inform me) ?

Thanks

[adrian_ritz1 · Accepted solution]

Hello @bleepie ,

As far as I know from management view you can't automatically download, you need to create a report based on the view, and then with powershell for example to export that report

I for exemple I'm using the following querry in powershell

DECLARE @SearchSQL NVARCHAR(MAX)

SELECT @SearchSQL = SearchSQL
FROM dbo.ComplianceSavedSearch
WHERE ComplianceSavedSearchID = 250 -- Replace with appropriate condition(s) to identify the report to be executed

SET @SearchSQL = REPLACE(@SearchSQL, 'SELECT TOP(@RowLimit) results.*', 'SELECT results.* INTO #ReportData')
set @SearchSQL = @SearchSQL +' select
ApplicationName as [Installation - Application name],
ApplicationVersion as [Installation - Application version],
R2_51a71253efbabf8e3d6d91e83e042599_InstallationToApplication_Edition as [Application - Edition],
R2_51a71253efbabf8e3d6d91e83e042599_InstallationToApplication_ProductName as [Application - Product],
ApplicationPublisher as [Installation - Publisher],
R2_51a71253efbabf8e3d6d91e83e042599_InstallationToApplication_Category as [Application - Category],
R2_51a71253efbabf8e3d6d91e83e042599_InstallationToApplication_Classification as [Application - Classification],
R2_5132ea92c263fdba5ec93940f2e3862c_InstallationToComputer_ChassisType as [Inventory device - Chassis type],
R2_5132ea92c263fdba5ec93940f2e3862c_InstallationToComputer_ComputerType as [Inventory device - Inventory device type],
R2_51a71253efbabf8e3d6d91e83e042599_InstallationToApplication_FlexeraID as [Application - Flexera ID],
R2_26bfb4732dd28d64f03f9ddd73a3d3ed_InstallationToLicense_LicenseStatus as [Assigned license - Status],
R2_26bfb4732dd28d64f03f9ddd73a3d3ed_InstallationToLicense_LicenseCompliance as [Assigned license - License compliance],
R2_51a71253efbabf8e3d6d91e83e042599_InstallationToApplication_NumberOfInstallations as [Application - Installations (number)]
from #ReportData
order by ApplicationName'

EXECUTE sp_executesql @SearchSQL, N'@SearchText NVARCHAR(1), @RowLimit INT', @SearchText = '', @RowLimit = -1

You need to format your querry to pass your need

[ChrisG]

Depending on the particular management view that you're interested in, there is likely a stored procedure and/or function in the database that will be used by the view to retrieve data. However these stored procedures/functions are generally not designed or intended to be used directly, so you may not find that is an easy option.

Configuring a report like @adrian_ritz1 has described might provide more flexibility. Here is another thread which talks about SQL scripting that can be helpful for executing a report: Can a FNMS report be executed from SQL Management Studio?

[bdavey]

Is there a way to do this within Flexera One as I do not have access to the SQL database as we did with On Prem?

[ChrisG]

Report data can be downloaded from Flexera One ITAM using the reportsExecute API: reportsExecute

See this for some example usage: The New Generic ITAM REST API for querying any web report is available! A PowerShell example.

[bdavey]

Having issues accessing this within PowerBI as a data source.  Do you have some resources to help with setting that up?