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
‎Jul 16, 2019 10:58 PM
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
‎Jul 16, 2019 06:20 AM
‎Jul 16, 2019 06:25 AM
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.
‎Jul 16, 2019 06:34 AM
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
‎Jul 16, 2019 06:30 AM
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
‎Jul 16, 2019 10:58 PM
‎Jul 16, 2019 11:12 PM
‎Jun 28, 2021 08:13 AM
That would be a select statement that lists some of the columns from the Grid_ApplicationsListModel view...
Thanks,
‎Jun 30, 2021 05:53 AM
@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.
‎Jun 30, 2021 06:02 AM
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.
‎Jun 30, 2021 06:49 AM
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,
‎Jun 30, 2021 08:20 AM