WoodWing Help Center

Elvis 4 REST API - query stats

Elvis 4 REST API - query stats

GET/POST

http://yourserver.com/services/queryStats
    ?queryFile=<path/to/query.sql>
    &num=<max rows>
    &<queryParam>=<value>

What does it do?

Query stats database for usage statistics.

Parameters

queryFile

The path to the SQL file with the query you want to run. The file must reside in a your plug-in folder inside the Elvis plugins/active folder.

For example if you place a SQL file in the <Elvis Config>/plugins/active/my_stats_plugin/sql/query.sql

You should use the complete path after '/active/' as queryFile parameter: queryFile=my_stats_plugin/sql/query.sql

In most situations you make the queryStats call from an HTML page in your plug-in folder. To prevent problems with renaming your plug-in, you can use the pluginId variable in your HTML or JS file: ${pluginId}/sql/query.sql

Make sure the URL is properly URL-encoded, for example: spaces should be represented as %20.

Required.

num

Number of rows to return. Specify 0 to return all rows. This parameter is also passed as named parameter to your SQL query so you can use it to limit results yourself.

Optional. Default is a maximum of 1000 rows.

*

All other parameters are passed to your SQL query as named parameters.

Optional.

Return value

An array with the result of the SQL query in JSON format. Each item (row) in the array is an object with properties for each of the result columns.

SQL Query

You can execute SQL SELECT queries on the various tables in the stats database. Only SELECT statements can be performed, other SQL statements are not allowed.

The underlying database runs Postgres 8.4, a powerful database which includes many useful SQL functions.

The following tables are available:

TABLE usage_log
(
  log_date timestamp without time zone NOT NULL,
  user_name character varying(100) NOT NULL,
  user_groups character varying(4096),
  client_type character varying(100),
  remote_addr character varying(100),
  remote_host character varying(4096),
  action_type character varying(100) NOT NULL,
  asset_id character varying(100),
  asset_path character varying(4096),
  asset_type character varying(100),
  asset_domain character varying(100),
  source_asset_id character varying(100),
  source_asset_path character varying(4096),
  changed_metadata text,
  details text,
  id bigserial NOT NULL PRIMARY KEY
)
TABLE search_log
(
  log_date timestamp without time zone NOT NULL,
  user_name character varying(100) NOT NULL,
  user_groups character varying(4096),
  client_type character varying(100),
  remote_addr character varying(100),
  remote_host character varying(4096),
  user_query character varying(4096),
  lucene_query character varying(8192) NOT NULL,
  facets text,
  total_results integer NOT NULL,
  first_result integer NOT NULL,
  max_results integer NOT NULL,
  query_duration integer NOT NULL,
  total_duration integer NOT NULL,
  id bigserial NOT NULL PRIMARY KEY
)
TABLE activity_log
(
  log_date timestamp without time zone NOT NULL,
  user_name character varying(100) NOT NULL,
  user_groups character varying(4096),
  client_type character varying(100),
  remote_addr character varying(100),
  remote_host character varying(4096),
  activity_type character varying(100) NOT NULL,
  details text,
  id bigserial NOT NULL PRIMARY KEY
)

Examples

queryStats

http://demo.elvisdam.com/services/queryStats
    ?queryFile=stats_rawdata/sql/usageLog.sql
    &num=3

usageLog.sql:

select * from usage_log order by log_date DESC limit 100

Returns the following result:

[
    {
      "id" : 1730910,
      "user_name" : "Patricia",
      "action_type" : "CHECKIN",
      "client_type" : "id",
      "user_groups" : "Art Director",
      "log_date" : 1353237821000,
      "asset_domain" : "image"
    }, {
      "id" : 1622588,
      "user_name" : "Ethan",
      "action_type" : "REMOVE",
      "client_type" : "air",
      "user_groups" : "Picture desk",
      "log_date" : 1353237458000,
      "asset_domain" : "audio"
    }, {
      "id" : 1721712,
      "user_name" : "Lucas",
      "action_type" : "PREVIEW",
      "client_type" : "air",
      "user_groups" : "Picture desk",
      "log_date" : 1353236038000,
      "asset_domain" : "document"
    }
]
queryStats
http://demo.elvisdam.com/services/queryStats
    ?queryFile=stats/sql/actionsCount.sql
    &action_types=CREATE,CREATE_VARIATION,COPY,COPY_VERSION
    &period_start=2012-01-01
    &period_end=2012-09-01

actionsCount.sql:

select count(*) as actions_count
from usage_log
where action_type in (:action_types) and 
log_date >= :period_start::TIMESTAMP and 
log_date <= :period_end::TIMESTAMP

Returns the subfolders of the Demo Zone:

[
    {
      "actions_count" : 13746
    }
]
Was this article helpful?
0 out of 0 found this helpful / Created: / Updated:
Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.