Retrieve the SQL query for a saved view or custom report

[#1]

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.

Identify the SQL query

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.

1. Open the FlexNet Manager Suite web UI and copy the report name.
        

   

2. Run the following query on the compliance database:
   
   SELECT * FROM ComplianceSavedSearch WHERE SearchName LIKE '%EXAMPLE REPORT%'

The SearchName column of the associated record matches the saved name of the report in your web UI.

3. The SearchSQL column of the associated record shows the complete SQL query.

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.

Execute a report created from the FNMS-Report option in SQL

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.

What can lead to differences between my report and the UI?

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.