Is there any way to access, execute and collect, through the SQL queries, resutls from reports created in webui - Report builder (custom or built in ones alike)?
I failed to find a table responsible for this. I'm running on-prem 2019 R2, db used is MS SQL.
Aug 03, 2020 03:50 AM
The ComplianceSavedSearch table in the Compliance DB may be of interest to you - particularly the SearchSQL column.
Aug 03, 2020 04:00 AM
Aug 03, 2020 05:43 AM
Hi @jjensen I get query details from SearchSQL but not able to execute the same. Can you please guide me how to execute and extract the report from backend?
Aug 05, 2020 09:12 AM
The SearchSQL column contains the SQL code, so if you declare and set the required variables you should be able to execute it from SQL Server Management Studio's query editor. The query editor will highlight the missing variables that need to be declared.
If you share its content in the forum other community members may be able to provide specific guidance.
Aug 11, 2020 02:18 AM
I have attached SQL query. I am also able to execute the query and get output in result window. but it was not executing while creating store procedure. It shows blank records. can someone help me on this to create store procedure for auto download this report on particular schedule?
Sep 16, 2020 05:39 AM
The data you are querying is scoped to specific operator context. You can use the stored procedure 'SetSessionContext' to set to context under which the query is run. The stored procedure takes 2 parameters with the first being an OperatorID and the second being a TenantID. Find the TenantID in the 'Tenant' table and find the OperatorID by querying the 'ComplianceOperator' table looking for the OperatorLogin of the operator you need to use for the SQL.
The use of the table functions like 'TablePurchaseOrderDetailCurrentUser', 'TableComplianceUserCurrentUser' and 'TableAssetCurrentUser' near the top of the SQL script give away the use of operator scoping.
So, put something like:
EXEC SetSessionContext 123, 1
at the very top of the script once you have the necessary information.
Sep 16, 2020 12:57 PM
This is an old thread, but for people finding it in the future, check out the following thread which also contains an answer to this:
Can a FNMS report be executed from SQL Management Studio?
Mar 10, 2022 01:23 AM