Extract report through backend query

[msutharh]

Is there any way to extract customized Asset Report through query or crone job ?

[marius]

Hi, if you use on premise FNMS, you can extract asset list through direct database query.

You can start building your query from the view [FNMSCompliance].[dbo].[Asset].

Marius

[msutharh]

Thanks Marius, We are using on premise only... Yes, I am aware for that query which you have mentioned, but i don't have much idea about to build the query to extract custom fields. Can you please guide me how to do that ?

[marius]

If it is a custom report in your print screen, you can try to analyse information you have in [FNMSCompliance].[dbo].[ComplianceSavedSearch_MT] table. It might give you SQL running in background, check SearchSQL column.  ComplianceSavedSearchID is the same integer you have in report URL.

[mfranz]

Hi,

I do have a PowerShell script GetCustomView.ps1 from Chris Grinton (@ChrisG). It uses the FNMS API to call any reports registered in your FNMS reporting area. With PowerShell you can store is however you like (e.g. Export-CSV) and trigger it via a scheduled task.

I am not sure if I am allowed to share it, so maybe Chris can step in here.

Best regards,

Markward

[ChrisG · Accepted solution]

See the following post that discusses the GetCustomView.ps1 script that @mfranz has referred to: Re: Access or Update FNMS Cloud Data using PowerShell

Another common approach for doing a regular automated extract of data from an on-premises FlexNet system is to configure a scheduled task to run a script that runs a SQL query and saves the extracted data to CSV format (or whatever destination is desired). For example, here is a simple PowerShell statement to export raw data from the Asset table into a CSV file named AssetList.csv:

Invoke-Sqlcmd -ServerInstance sqlsvr01 -Database FNMSCompliance -Query "SELECT * FROM dbo.Asset" |
Export-Csv AssetList.csv -NoTypeInformation

Here is an example of one approach to writing a SQL query that will return asset details including values for custom properties named Prop1 and Prop2:

SELECT
	AssetID,
	ShortDescription,
	Prop1,
	Prop2
FROM (
	SELECT a.AssetID, a.ShortDescription, atp.PropertyName, apv.PropertyValue
	FROM
		dbo.Asset AS a
		INNER JOIN dbo.AssetTypeProperty AS atp ON atp.AssetTypeID = a.AssetTypeID
		INNER JOIN dbo.AssetPropertyValue AS apv ON apv.AssetTypePropertyID = atp.AssetTypePropertyID AND apv.AssetID = a.AssetID
	WHERE
 		atp.PropertyName IN (
			'Prop1',
			'Prop2'
		)
	) AS src
PIVOT (
	MAX(PropertyValue)
	FOR PropertyName IN (
		Prop1,
		Prop2
	)
) AS pvt

[msutharh]

Chris,

Appreciate the response...

[raghuvaran_ram]

@ChrisG

I want to extract all application UI view from DB can you please suggest a query.

[JohnSorensenDK]

@raghuvaran_ram 

That would be a select statement that lists some of the columns from the Grid_ApplicationsListModel view...

Thanks,

[raghuvaran_ram]

@JohnSorensenDK  Thanks for your reply

My actual requirement is to extract all application details that are used in the Cloud environment using a DB query, do you have any suggestions for that, please.

[raghuvaran_ram]

@JohnSorensenDK 

We have setup a beacon in the cloud for azure VM and they are transmitting the details to our on-prem application server. Under all inventories tab, I can select a cloud VM and by navigating into the applications tab I can view the list of applications installed within that VM and I can also extract the same information for all the cloud VM's from the reports section through a custom report creation.

How do I extract the installed application information from all the cloud VM's using DB query.

[JohnSorensenDK]

@raghuvaran_ram 

I think that you would have to reverse engineer how to do this using the schema reference, but not sure why you would want to do this when it's so easy to do with classic reporting:

Thanks,