When you build a custom report within FlexNet Manager Suite, SQL queries are generated based on the selected reported building blocks.

This article describes an approach to accessing and executing the generated queries directly in the compliance database.

Identify the SQL query

All saved custom reports created using the FlexNet Manager Suite report builder are based on SQL queries. You can retrieve the query details 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: SQL Server Management Studio may hide line breaks that are included in the the SearchSQL value, making the raw value difficult to read.

For more information on the ComplianceSavedSearch table, see the schema guide ComplianceSavedSearch Table.

Execute the SQL batch used for a custom report

A SQL script similar to the following can be executed against a FlexNet Manager Suite compliance database to retrieve the SQL batch used to run a report and then execute it. This script loads the data into a global temporary table named ##ReportData for subsequent manipulation.

-- Set the operator context to a user with appropriate role memberships
-- that grant access to data to be included in the report.
EXEC dbo.CurrentOperatorEnsureDefined 'DOMAIN\operator_name' -- Replace with appropriate operator login name

DECLARE @SearchSQL NVARCHAR(MAX)

SELECT @SearchSQL = SearchSQL
FROM dbo.ComplianceSavedSearch
WHERE ComplianceSavedSearchID = 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')

IF OBJECT_ID('tempdb..##ReportData') IS NOT NULL
    DROP TABLE ##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

-- Output the query SearchSQL in an XML element to avoid truncating long values:
-- SELECT SearchSQL = @SearchSQL FOR XML PATH(''),TYPE 

NOTES:

• 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 above SQL script stores report data in a global temporary table named ##ReportData. If you are running this script in multiple SQL Server sessions at the same time, you may need to use different global temporary table names to avoid conflicts.

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

Data showed on many grid pages in the web UI depend on data in tables that contain cached data updated on a staggered basis for optimal performance (such as the Grid_ComputersListModel view and underlying tables). On the other hand, data shown in reports is based on tables that are regularly updated but not cached (such as tables underlying the ComplianceComputer view). This can lead to details shown on web UI grid pages differing some details shown in reports at different moments in time. Any discrepancies should stabilize over time as cached data is refreshed.

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. The following article contains some diagnostic steps that may help investigate this scenario: FlexNet Manager Suite web UI view not updating when changes are made.