Redwood Server allows you to query the data model in reports, query filters, RedwoodScript is well as REL expressions. In query filters you only specify what comes after the where clause.

Functions

The following functions have been implemented:

AVG(<expression> | DISTINCT column-name) - calculates the average
COUNT( DISTINCT column-name | * ) - returns the number of items in a group
FORMAT_EXPRESSION(<expression>, <format>[, <timezone>]) - <expression> conforms to Time.expressionNow(), <format> conforms to SimpleDateFormat.
FORMAT_NOW(<format>[, <timezone>]) - <format> conforms to SimpleDateFormat.
MAX(<expression>) - calculate the maximum value from <expression>
MIN() - calculate the minimum value from <expression>
NOW([<expression>]) - <expression> conforms to Time.expressionNow() REL function expression.
SUM([ DISTINCT | ALL ] expression ) - returns a sum

See Extended Time Functions for more information on syntax.

note

Depending on the underlying database, these functions may not always return the expected value. Please see the documentation of your database vendor for implementation specifications.

Example

Credentials

You want to query credentials for soap credentials

=Query.getString('select Credential.* from Credential where Credential.CredentialProtocol in
(select CredentialProtocol.UniqueId from CredentialProtocol where CredentialProtocol.Name = ?'), 'soap')

Processes

Create a report of a list of processes that have a requested start time between 11:00 in morning today or yesterday and 11:00 tomorrow morning or today, depending on what time it currently is.

=Query.getString('select Job.* from Job where
Job.RequestedStartTimeInternal > now(\'subtract 11 hours set hour 11 truncate hour\')
and Job.RequestedStartTimeInternal < now(\'add 13 hours set hour 11 truncate hour\')')

Functions

AVG - used as average runtime, here: long runners of the last 7 days

=Query.getString('Select Job.* from Job where
Job.RunTime > (select avg(Job.RunTime) from Job where Job.RequestedStartTimeInternal > now(\'set hour 0 subtract 7 days\'))
and Job.RequestedStartTimeInternal > now(\'set hour 0 subtract 7 days\')')

Writing your own Custom SQL

To write your own custom SQL, you need to inspect the data model available in the API documentation. In this example, you query the Credential table and you want filter by credential protocol; in the datamodel, you see the CredentialProtocol field of the Credential table is of type BIGINT which means that it will contain the UniqueID of the credential protocol. You thus have to select the UniqueID of the credential protocol from the CredentialProtocol table.

Product Documentation

Using SQL to Query the Data Model

Functions

note

Example

Credentials

Processes

Functions

Writing your own Custom SQL

See Also