Query Reports

Query Reports

Query Reports are reports that can be generated using an SQL query.

Role Required: SDAdmin; Technicians who have Complete Access to reports and Create Query Report permission.

To create a new query report,
  1. Go to the Reports tab and click New Query Report in the left pane.
  2. In the Query Editor, select the required module from the Table Schema drop-down.
  3. Specify the Report Title.
  4. Specify the query to be executed in the Query field. Any errors in the query will be shown in the Logs field.
  5. Click Run Report to create the query report.

  

Read-Only User  

A read-only user has exclusive permission to view data in the database and execute secure query reports.

Role Required: SDAdmin; Technicians with Create Query Report permissions 

Create a Read-Only User 

Create a read-only user only if you have configured an external Postgres or MSSQL database in your application. For bundled Postgres, a read-only user is built-in by default. 
  1. To create a read-only user, execute the following queries:
For External Postgres Database
For MSSQL Database
CREATE USER <username> WITH LOGIN PASSWORD <password>;

GRANT CONNECT ON DATABASE <databaseName> TO <username>;

GRANT USAGE ON SCHEMA public TO <username>;

GRANT SELECT ON ALL TABLES IN SCHEMA public TO <username>;
CREATE LOGIN <username> WITH PASSWORD <password>;

CREATE USER <username> FOR LOGIN <username>;
  1. Grant relevant permissions for all tables.
  2. Revoke access for tables that contain sensitive or unnecessary data with this query:
  1. REVOKE SELECT ON <tableName> FROM <username> 
  1. Obtain the encrypted key of the password.
  2. Go to [AssetExplorer]\bin in the command prompt.
  3. Execute the script encrypt.bat <read-only user password>.
  4. Copy the password encryption key displayed in the command prompt and store it in a secure location.
  5. Go to {AssetExplorer_Home}/AssetExplorer/conf.
  6. Open the database_params.conf file.
  7. Configure the username in the relevant tag. For example,
  1. rodatasource.username=<username> 
  1. Fetch the encrypted password key and configure it in the relevant tag. For example,
  1. rodatasource.password=<password> 


Update Database Flag    

  1. After the user is created, connect the application to the database and execute the following query. This will allow the read-only user to generate secure query reports without accessing data from restricted tables.
  1. UPDATE ReportModuleConfiguration SET PARAMVALUE = 'true' WHERE CATEGORY LIKE 'ROUser' AND PARAMETER LIKE 'Use_ROUser' 
  1. Restart the application for the changes to take effect.
 

Restore AssetExplorer 

After restoring AssetExplorer from the backup data,
  1. Update the database flag if the database setup is unchanged.
  2. If the database or application setup is modified, create a read-only user again.
 

Restrict Execution of Database Functions  

Administrative functions that interfere with or slow down query report generation must be restricted.

Restrict PostgREST Database Functions 
To restrict Postgres functions, remove Execute permission from the public role using Data Control Language (DCL) commands.
  1. Copy the fetch functions script from this page.
  2. Execute the query present in the file.
  3. The functions that interfere with query reports will be listed in the query result.
  4. Copy the query result and execute the following queries to revoke the Execute permission.
    1. To revoke the Execute permission for public users:
  1. REVOKE EXECUTE ON FUNCTION FROM public;
    1. To revoke the Execute permission for the read-only user:
  1. REVOKE EXECUTE ON FUNCTION <insert query result> FROM <Read-Only User name>; 
  1. To grant the Execute permission to all users except for public users and read-only user:
  1. GRANT EXECUTE ON FUNCTION <insert query result> TO <users except read-only user and public>; 
 

Restrict MSSQL Database Functions 

To restrict the MSSQL function,
  1. Fetch the functions to be restricted manually.
  2. Revoke the Execute permission with the following queries.
    1. To revoke Execute permission for the read-only user:
  1. DENY EXECUTE ON [dbo].<FunctionName> TO <Read-only User Name>; 
    1. To revoke Execute permission for public users:
  1. DENY EXECUTE ON [dbo].<FunctionName> TO public; 
  1. To grant the Execute permission to all users except for public users and read-only user:
  1. GRANT EXECUTE ON [dbo].<FunctionName> TO <user name comma sperated>; 
Administrative functions that interfere with query reports must be restricted for public users and read-only user every time the AssetExplorer build is updated. 

Tables to be Restricted for the Read-Only User   

  1. AaaAccHttpSession, AaaPassword, RememberMeDetails, ADSTFAUserEnrollment, IDSAuthnFwFactorTOTPEnrollment, IDSAuthnFwFactorBackupCodeEnrollment, IDSFwState, IDSAuthnFwAuthnToken, IDSAuthnFwAuthnTokenVsResources, IDSAuthnFwEnrollmentSettings, IDSAuthnFwEnrollmentSettingsVsFactorConfig, IDSAuthnFwFactor, IDSAuthnFwFactorBackupCodeConfig, IDSAuthnFwFactorConfig, IDSAuthnFwFactorEmailConfig, IDSAuthnFwFactorEmailEnrollment, IDSAuthnFwFactorTOTPConfig, IDSAuthnFwMFAAttemptAudit, IDSAuthnFwMFAAttemptAuditVsAuthnFactorAttempt, IDSAuthnFwUserEnrollment, IDSAuthnFwUserEnrollmentVsFactorConfig, IDSFwEndpoint, IDSFwEndpointAccessRule, IDSFwEndpointAccessRuleAuthnSettings, IDSFwEndpointAccessRuleVsAuthnFactorConfig, IDSFwEndpointAccessRuleVsResources, IDSFwEndpointModule, CustomFunctionDetails, AdminAuditHistoryJson, MobileAuthKey, COMMONPASSWORD, PasswordInfo, PasswordResetLink, BackupSchedule, DBCredentialsAudit, ChatJson, ThrottleExceedingHistory, UserAdditionalFields_multiselect, UserAdditionalFields_history, UserAdditionalFields_historydiff, CustomPickListValues, CustomModuleInstance, CustomModuleInstanceImages, CustomModuleDescription, CustomModuleHistory, CustomModuleHistoryDiff, CM_Tasks, CM_Comments, CM_Attachments, Custom_001, Custom_MultiSelect_001, OauthToken, OauthCredential, CustomTrustStore, TechnicianKeyDefinition

Revoke Read-Only User Access  in MSSQL 

Use the following query to block the read-only user from executing commands and to revoke the user's access to all tables in the database.
Ignore any error messages about tables or objects not being found while running this script. 

  1. DENY INSERT, UPDATE, DELETE ON SCHEMA :: [dbo] TO <userName>;
  2. REVOKE SELECT ON SCHEMA :: [dbo] FROM <userName>;
  3. declare commands cursor for
  4. SELECT
  5. 'GRANT SELECT ON [dbo].' + QUOTENAME(t.TABLE_NAME) + ' TO <userName>;'
  6. FROM
  7. TableDetails t
  8. WHERE
  9. t.TABLE_NAME NOT LIKE 'AaaAccHttpSession'
  10. AND t.TABLE_NAME NOT LIKE 'AaaPassword'
  11. AND t.TABLE_NAME NOT LIKE 'RememberMeDetails'
  12. AND t.TABLE_NAME NOT LIKE 'ADSTFAUserEnrollment'
  13. AND t.TABLE_NAME NOT LIKE 'IDSAuthnFwFactorTOTPEnrollment'
  14. AND t.TABLE_NAME NOT LIKE 'IDSAuthnFwFactorBackupCodeEnrollment'
  15. AND t.TABLE_NAME NOT LIKE 'IDSFwState'
  16. AND t.TABLE_NAME NOT LIKE 'IDSAuthnFwAuthnToken'
  17. AND t.TABLE_NAME NOT LIKE 'IDSAuthnFwAuthnTokenVsResources'
  18. AND t.TABLE_NAME NOT LIKE 'IDSAuthnFwEnrollmentSettings'
  19. AND t.TABLE_NAME NOT LIKE 'IDSAuthnFwEnrollmentSettingsVsFactorConfig'
  20. AND t.TABLE_NAME NOT LIKE 'IDSAuthnFwFactor'
  21. AND t.TABLE_NAME NOT LIKE 'IDSAuthnFwFactorBackupCodeConfig'
  22. AND t.TABLE_NAME NOT LIKE 'IDSAuthnFwFactorConfig'
  23. AND t.TABLE_NAME NOT LIKE 'IDSAuthnFwFactorEmailConfig'
  24. AND t.TABLE_NAME NOT LIKE 'IDSAuthnFwFactorEmailEnrollment'
  25. AND t.TABLE_NAME NOT LIKE 'IDSAuthnFwFactorTOTPConfig'
  26. AND t.TABLE_NAME NOT LIKE 'IDSAuthnFwMFAAttemptAudit'
  27. AND t.TABLE_NAME NOT LIKE 'IDSAuthnFwMFAAttemptAuditVsAuthnFactorAttempt'
  28. AND t.TABLE_NAME NOT LIKE 'IDSAuthnFwUserEnrollment'
  29. AND t.TABLE_NAME NOT LIKE 'IDSAuthnFwUserEnrollmentVsFactorConfig'
  30. AND t.TABLE_NAME NOT LIKE 'IDSFwEndpoint'
  31. AND t.TABLE_NAME NOT LIKE 'IDSFwEndpointAccessRule'
  32. AND t.TABLE_NAME NOT LIKE 'IDSFwEndpointAccessRuleAuthnSettings'
  33. AND t.TABLE_NAME NOT LIKE 'IDSFwEndpointAccessRuleVsAuthnFactorConfig'
  34. AND t.TABLE_NAME NOT LIKE 'IDSFwEndpointAccessRuleVsResources'
  35. AND t.TABLE_NAME NOT LIKE 'IDSFwEndpointModule'
  36. AND t.TABLE_NAME NOT LIKE 'CustomFunctionDetails'
  37. AND t.TABLE_NAME NOT LIKE 'AdminAuditHistoryJson'
  38. AND t.TABLE_NAME NOT LIKE 'MobileAuthKey'
  39. AND t.TABLE_NAME NOT LIKE 'COMMONPASSWORD'
  40. AND t.TABLE_NAME NOT LIKE 'PasswordInfo'
  41. AND t.TABLE_NAME NOT LIKE 'PasswordResetLink'
  42. AND t.TABLE_NAME NOT LIKE 'BackupSchedule'
  43. AND t.TABLE_NAME NOT LIKE 'DBCredentialsAudit'
  44. AND t.TABLE_NAME NOT LIKE 'ChatJson'
  45. AND t.TABLE_NAME NOT LIKE 'ThrottleExceedingHistory'
  46. AND t.TABLE_NAME NOT LIKE 'UserAdditionalFields_multiselect'
  47. AND t.TABLE_NAME NOT LIKE 'UserAdditionalFields_history'
  48. AND t.TABLE_NAME NOT LIKE 'UserAdditionalFields_historydiff'
  49. AND t.TABLE_NAME NOT LIKE 'CustomPickListValues'
  50. AND t.TABLE_NAME NOT LIKE 'CustomModuleInstance'
  51. AND t.TABLE_NAME NOT LIKE 'CustomModuleInstanceImages'
  52. AND t.TABLE_NAME NOT LIKE 'CustomModuleDescription'
  53. AND t.TABLE_NAME NOT LIKE 'CustomModuleHistory'
  54. AND t.TABLE_NAME NOT LIKE 'CustomModuleHistoryDiff'
  55. AND t.TABLE_NAME NOT LIKE 'CM_Tasks'
  56. AND t.TABLE_NAME NOT LIKE 'CM_Comments'
  57. AND t.TABLE_NAME NOT LIKE 'CM_Attachments'
  58. AND t.TABLE_NAME NOT LIKE 'Custom_001'
  59. AND t.TABLE_NAME NOT LIKE 'Custom_MultiSelect_001'
  60. AND t.TABLE_NAME NOT LIKE 'OauthToken'
  61. AND t.TABLE_NAME NOT LIKE 'OauthCredential'
  62. AND t.TABLE_NAME NOT LIKE 'CustomTrustStore'
  63. AND t.TABLE_NAME NOT LIKE 'TechnicianKeyDefinition';
  64. declare @cmd varchar(max)
  65. open commands
  66. fetch next from commands into @cmd
  67. while @@FETCH_STATUS=0
  68. begin
  69. exec(@cmd)
  70. fetch next from commands into @cmd
  71. end
  72. close commands
  73. deallocate commands

Query to Fetch Internal Tables    

By default, internal tables are restricted from being viewed by users to avoid exposing sensitive data.

To view internal tables, administrators can execute the following queries:
Postgres Database
MSSQL Database
SELECT Distinct(table_name) as "Name" FROM information_schema.tables WHERE lower(table_name) NOT IN (SELECT lower(table_name) from TableDetails ) ORDER BY table_name;
SELECT * FROM (SELECT DISTINCT(name) as "Name" FROM sys.objects WHERE type_desc = 'SYSTEM_TABLE' OR type_desc = 'INTERNAL_TABLE' OR type_desc = 'USER_TABLE' OR type_desc = 'VIEW' UNION SELECT DISTINCT(name) FROM sys.tables UNION SELECT DISTINCT(name) FROM sysobjects WHERE sysobjects.xtype = 'U' OR sysobjects.xtype = 'S' UNION SELECT DISTINCT(name) FROM sys.system_views UNION SELECT DISTINCT(TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES ) AS t WHERE t.Name NOT IN (SELECT TableDetails.TABLE_NAME FROM TableDetails );
Queries that include keywords present in internal table names will not be executed.

    • Related Articles

    • Custom Reports

      AssetExplorer enables you to create customized reports to meet the unique needs of your organization. Role Required: SDAdmin; Technicians with Complete Access to reports. To create custom reports, Go to the Reports Tab. Click the New Custom Report ...

    • Scheduling Reports

      Using Schedule Report Settings, you can automate generating multiple reports, in various formats, at scheduled times. These reports can be generated in PDF, XLS, XLSX, HTML, Inline HTML, CSV, DOC, DOCX, or XML formats and shared via email or saved to ...

    • Predefined Reports

      AssetExplorer provides predefined reports that technicians can use to generate reports instantly. Predefined reports can also be edited to fine-tune the data as needed. To access the reports, go to the Reports tab and browse the listed folders. ...

    • Support Policy

      Support will be provided only for the AssetExplorer builds 7000 and above. We recommend you upgrade to the latest version to continue receiving support. Support for scripts, query reports, and customizations is chargeable. Scripts, query reports, and ...

    • Enable SSL Connection in MSSQL

      Secure the AssetExplorer MSSQL database by setting up an SSL connection. To create an SSL connection, you must obtain either a self-signed SSL certificate or purchase a verified SSL certificate from a vendor. To generate a self-signed certificate, go ...