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
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].
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.
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.
@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.
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.