Custom Queries
The Custom Queries menu provides a location to create and view any custom queries that have been generated.
Custom Queries are written in the HQL language which is similar to SQL, comprehensive reports can be created that join data from different tables/objects (https://docs.jboss.org/hibernate/orm/3.3/reference/en-US/html/queryhql.html).
Custom Queries can be created, edited and deleted by PAM System Administrators only.
Users with the global role Auditor may only view Custom Queries.
The Custom Query has report Examples and also the code needed to return all fields for different Objects.
Creating a New Custom Query
- Login to PAM with a System Administrator account.
- Navigate to Reports > Custom Queries and click the Create button.
- Enter a Name and optional Description in the appropriate fields.
- Enter your custom HQL statement into the HQL field.
- Click the Save button to save your new custom report.
- Search is only available if the functionality was included in your HQL statement. See the example report Example Report with Enabled Search to see how Search is enabled.
- Export is available to export the available on-screen data to either a CSV or PDF file.
Use the Example menu to select a pre-built report example that will load a sample custom report. You may customize these example reports as necessary and use the Save button to make it available to your users.
To enable a column to display a link to a record page, select record ID in this column and name the column record_id.
Click the API Documentation link to open the interactive REST API documentation to further help with the construction of your HQL statements.
To View the results of a custom report, simply click on the report’s Name or its View button. When viewed, the results will be queried and displayed as found. From within a custom report, the following options are available:
Columns will be displayed as defined in your HQL statement. The Columns selector will allow you to show or hide each column in the report.
PAM includes the option to delegate custom queries execution to folder owners.
The option allows involving more users in the system management and audit.
The option allows delegating custom queries execution to vault or folder owners by enabling Custom Queries that reference record IDs using record_id HQL alias on the folder level in the reports menu.
An example of the custom report that could be delegated to the folder level is given by the following HQL producing all record update audit log events from the selected folder with search and export (PDF, CSV, Encrypted PDF or CSV) options (note record_id alias returned by the HQL as the last column in a select clause that enables the option to Enable custom report on the folder level):
select
- r.name as recordName,
- a.user.name as User,
- a.created as created,
- a.event as Event,
- r.id as record_id
from
- AuditLog a,
- Record r
where
- a.modelId=r.id
- and a.event = 'Update'
- and r.name like :search
Record View screen
The Record View screen enables system administrators to create action-able custom queries that provide immediate drill-down access to the reported assets to make changes or to further investigate the case using record-based reporting.
To enable a column to display a link to a record page, select record ID in this column and name the column record_id.
Example of report displaying archived records with record name, record type and a link to a record:
select
m.name as name,
m.description as description,
m.recordType as recordType,
m.created as created,
m.author as author,
m.id as record_id
from
Record m
where m.archived is true
Example of Custom Query
Example of custom query with Id filter
When attempting to filter by Secure Id's , use the below format:
where r.id = '${i-xxx}'
where r.id in ('${i-xxx}', ${i-yyy})
where r.id = '${:search}'
