Queries

Queries is a standard configuration interface for adding, editing, and removing stored queries from Assure1.

Refer to the Standard Configuration Interface guide for details on interacting with the grid and form. The additional control is explained in the Grid Details section.

This user interface calls REST methods from api/database/queries.

The UI path for this interface is Configuration -> Databases -> Queries.

Grid Details

The interface displays the standard Assure1 buttons as well as the following:

• Execute - After an existing query has been selected, click Execute to open a new browser tab with the query being executed.

Form Fields

• Query Name - The name for the stored query.

• Description - The description of the stored query.

• Schema - The schema to be used when executing the stored query.

• ShardID - The shard to be used when executing the stored query.

• Query User Owner - The user owner of the query.

• Query Group Owner - The user group owner of the query.

• Query - The query to be executed.

• Tokens - The optional tokens used for queries with placeholders. The Token is a reference for the value replacement that can be done when using the query via a dashboard and in other places. The Default Value is what will be used if no other value is supplied to the query when executed.

• Viewers - Selection of user groups that have read-only access to the query.

Query Toolbar

• Line Numbers - Toggle on/off line numbers.

• Search - Search code.

• Previous - Previous result for current search.

• Next - Next result for current search.

• Replace - Search and replace code.

• Replace All - Search and replace all in code.

Best Practices

• Caution should be exercised when using this interface as it is possible to delete data with malformed queries.

Token Value Replacement Example

1. Create a database query named Devices in Zone with placeholders.

   • Query Name => Devices in Zone

   • Schema => Assure1

   • ShardID => 1

   • Query User Owner => [Public to All Users In Group]

   • Query Group Owner => [Public to All Groups]

   • Query

     SELECT DeviceID,
            DeviceName,
            INET_NTOA(IPAddress)    AS IPAddress,
            INET6_NTOA(IPv6Address) AS IPv6Address
       FROM Devices 
      WHERE DeviceZoneID = ?
     

   • Tokens => Click the "Add" button, then set the following:

     Token	Default Value
     Zone	1

   • Click on the "Submit" button.

2. Go to the Dashboards UI.

   Configuration -> Dashboards -> Dashboards

3. Click the Add button, then set the following:

   • Dashboard Name => Devices in Zone

   • Dashboard User Owner => [Public to All Users In Group]

   • Dashboard Group Owner => [Public to All Groups]

4. Add a Database Grid panel to the dashboard.

5. Click the Configure Panel button, then set the following:

   • Panel Name => Device List in Zone

   • Query => Devices in Zone

   • Tokens =>

   • Click on the "View" button.

6. Click on the "Submit" button.

   Note

   The following reserved words cannot be used as values:

   • PortalID

   • QueryID

   • Tokens

   • page

   • start

   • limit

7. Query: Example Query Tokens Name Value ------------------- Query Database Shard 1

8. Load the dynamic query in a portlet. You can load it directly via a URL as shown below, or have a tool load the URL and provide the QueryID and DB via the tool. In this example, we assume the portal created has an ID of 1001.

9. https://assure1.example.com/#/corePortal/portals?PortalID=1001&QueryID=1000&DB=Assure1