When you build a custom report within FlexNet Manager Suite, you create SQL queries based on defined building blocks within the product database. Not all fields are available, as these building blocks don't exist for every table or column.
When testing database reporting or identifying the cause of discrepancies between the web UI and custom report results, it's helpful to understand the SQL queries that the Report Builder uses.
All saved views and custom reports created using the FlexNet Manager Suite Report Builder are SQL-based. You can retrieve them from the compliance database by querying the ComplianceSavedSearch table.
The SearchName column of the associated record matches the saved name of the report in your web UI.
NOTE: The report's SQL code is returned on one line. This format usually can't be run in SQL Server Management Studio. Using "Replace All" for large groups of whitespace characters and replacing them with a line return/new line creates a report in a runnable format that requires minimal or no manual configuration to run.
For more information on the ComplianceSavedSearch table, see the schema guide ComplianceSavedSearch Table.
A SQL script like the following can be executed against a FlexNet Manager Suite On-premises compliance database to retrieve the SQL query used to run a report. Then, execute it to load the data into a temporary table for subsequent manipulation.
DECLARE @SearchSQL NVARCHAR(MAX)
SELECT @SearchSQL = SearchSQL
FROM dbo.ComplianceSavedSearch
WHERE ComplianceSavedSearch = 12345 -- Replace with appropriate condition(s) to identify the report to be executed
IF @SearchSQL IS NULL
BEGIN
RAISERROR('ERROR: Report has a NULL query - a user must refresh the report in the web UI to generate the query before this script can be run', 16, 1)
RETURN
END
IF @SearchSQL NOT LIKE '%SELECT TOP(@RowLimit) results.*%'
BEGIN
-- If this condition occurs, the structure of the report query
-- being produced by FNMS may have changed since this script
-- was developed. In that case this script will need to be
-- changed to cope with the latest report query structure.
RAISERROR('ERROR: Report query is not structured as expected', 16, 1)
RETURN
END
-- Instead of returning the report results as a result set,
-- change the query to populate a temporary table so that
-- data can be subsequently manipulated.
SET @SearchSQL = REPLACE(@SearchSQL, 'SELECT TOP(@RowLimit) results.*', 'SELECT results.* INTO #ReportData')
EXECUTE sp_executesql @SearchSQL, N'@SearchText NVARCHAR(1), @RowLimit INT', @SearchText = '', @RowLimit = -1
SELECT * FROM #ReportData
-- Or do whatever else you want to with the data here
NOTE: The SQL query used to generate the report data is not generated until the report has been run at least once in the web UI. The script contains a check to verify that this has been done.
The web UI results depend on tables that are updated on a staggered basis for optimal performance (such as the Grid_ComputersListModel table), while the reports depend on tables that are regularly updated (such as the ComplianceComputer table). This can lead to the web UI results not matching the report data.
If you continue to see discrepancies in the returned data or notice many differences between the report data and web UI, there may be an issue with the FNMSPreCalcUpdate process. Follow the diagnostic steps in the FNMSPreCalcUpdate - Initial Diagnosis.
on
Sep 15, 2022
09:52 AM
- edited on
Apr 03, 2023
09:12 AM
by
HollyM