cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

How to automatically retrieve FNMS view?

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

(1) 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

View solution in original post

(2) Replies

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
By Community Manager Community Manager
Community Manager

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?

(Did my reply solve the question? Click "ACCEPT AS SOLUTION" to help others find answers faster. Liked something? Click "KUDO". Anything expressed here is my own view and not necessarily that of my employer, Flexera.)