registerDigest()

This function registers a named history digest and creates history digest database tables and views with the specified columns. A digest also defines, via a range of filters and operations, the histories and events that will later be digested into the tables using digestHistories().

Registering a digest adds an entry to iCM's security log.

Parameters

These parameters define top-level information about your digest table and the histories that will be digested when you run digestHistories().

Name	Type	Description
name	String, required	The name of the digest. May only contain alphanumeric characters and underscores, and must not be an SQL reserved word. Digest table names and views are built from this name
description	String, optional	An optional description for the digest
filter	Object, optional	A history filter object. Only histories matching this filter will be considered for this digest. The filter can only access top level history properties (ie it cannot access subject or event values)
eventFilter	Object, optional	A history filter object. Only consider histories that have an event that matches this filter and only digest data from that event and those that follow it (eg only consider a history once this event has occurred)
columns	Array, required	An array of objects. Each defines a column in your digest table. See the column schema below
The following parameters store scheduling information that could be used to calculate when a digest should next be run. The history worker doesn't perform any scheduling itself, but these values could be read and updated by a scheduler, before deciding whether to call digestHistories()
frequency	Integer, optional	By convention this integer represents seconds. Scheduling information that can be returned via listDigests() and updated using updateDigest()
enabled	Boolean, optional	Scheduling information that can be returned via listDigests() and updated using updateDigest()
started	Long <unix timestamp>, optional	Scheduling information that can be returned via listDigests() and updated using updateDigest(). This value is automatically set when digestHistories() runs
finished	Long <unix timestamp>, optional	Scheduling information that can be returned via listDigests() and updated using updateDigest(). This value is automatically set when digestHistories() runs

Column Schema

These parameters define each column in your digest table. They are the properties of each object in the columns array above.

Name	Type	Description
name	String, required	The name the column will have on the digest table, if the column type is table this name forms part of the child table name. Must not be an SQL reserved word
type	String, required	One of: varchar (must also set a size) int float datetime boolean point (only available on firstvalue and lastvalue column operations) table (creates child tables)
size	Integer, optional	The column size, required if the type is varchar (do not set a size if you are using the string_value directive)
operation	String, required	One of: label firstvalue lastvalue firsttimestamp lasttimestamp countevents issealed all distinct See column operations below for details
key	String, optional	Only relevant to the label, firstvalue and lastvalue operations, and also used when creating columns in child tables. See the column operations below for examples
eventFilter	Object, optional	A column level filter object that can be used with all operations. Only events that match this filter will be used to populate the digest
index	String, optional	The name of an index that the column will be added to when the digest tables are created. May only contain alphanumeric and underscores, the name "hid" is reserved. If multiple columns contain the same value for index then all of those columns will be added to the same index. Cannot be used with table column types
directive	String, optional	Directives provide additional instruction to control how data is digested
		empty_string_as_null - used with column type datetime and the firstvalue and lastvalue operations. Empty strings will be inserted into the digest as null
		string_value - used with column type varchar and operations of firstvalue, lastvalue, all.value and distinct.value Instead of a JSON string literal, the value inserted into the digest will be the string value used by the history service for comparisons and sorting. This value is uppercased and truncated at 127 characters. For example, "GOSS Interactive, 24 Darklake View, Plymouth" would be inserted as GOSS INTERACTIVE, 24 DARKLAKE VIEW, PLYMOUTH When used with the label operation the first and last characters of strings (ie the quotes) are simply removed
columns	Array, optional	An array of objects. If the column type is table, these will be the columns of that child table. Child columns have a different set of operations depending on the operation specified on the parent table. See column operations below

Column Operations

The following operations are used to populate the columns of your digest table.

Operation	Description	Example
label	Stores the history label named in the key	`{ "name": "businesskey", "type": "varchar", "size": 50, "operation": "label", "key": "labelb" }`
firstvalue	Stores the value of the first event property that matches the key	`{ "name": "first_user", "type": "varchar", "size": 50, "operation": "firstvalue", "key": "userId" }`
lastvalue	Stores the value of the last event property that matches the key	`{ "name": "last_user", "type": "varchar", "size": 50, "operation": "lastvalue", "key": "userId" }`
firsttimestamp	Stores the timestamp of the first event found	`{ "name": "started", "type": "datetime", "operation": "firsttimestamp" }` Used in combination with a column level eventFilter `{ "name": "first_response_time", "type": "datetime", "eventFilter": { "key": "event", "EQ": "Mail Sent" }, "operation": "firsttimestamp" }`
lasttimestamp	Stores the timestamp of the last event found	`{ "name": "closed", "type": "datetime", "operation": "lasttimestamp" }`
countevents	Counts and stores the running total number of events digested in the column, must be an int type column. Can be used to count all events (in the matching history and since the top-level eventFilter if there is one) or events that match a column-level eventFilter	`{ "name": "purchase_count", "type": "int", "operation": "countevents" }`
issealed	Stores the sealed status of the history in the column, must be a boolean type column	`{ "name": "sealed", "type": "boolean", "operation": "issealed" }`
all	Only applicable to the table column type. Creates a row in the child table for each event of a history that matches the filters. Child tables have their own column array. Columns in child tables support two operations, value (which matches a key) or timestamp	`{ "name": "all_descriptions", "type": "table", "operation": "all", "columns": [{ "name": "eventdescription", "type": "varchar", "size": 250, "operation": "value", "key": "description" }, { "name": "eventtime", "type": "datetime", "operation": "timestamp" }] }`
distinct	Only applicable to the table column type. Creates a row in the child table for each unique value in the events of a history. Child tables have their own column array. Columns in child tables support value, which matches a key. There can only be one column in the columns array	`{ "name": "involved_users", "type": "table", "operation": "distinct", "columns": [{ "name": "involveduser", "type": "varchar", "size": 250, "operation": "value", "key": "userId" }] }`

Returns

If the registerDigest() operation was successful then the "result" structure will contain the following.

Property	Description
digest_registered	The name of the digest
created_tables	An array of tables names. Table names have the format dh_digestname and dh_digestname_columnname (where columnname is the name of column when type is table)
created_views	The views corresponding to the tables, in the format dh_digestname_vw and dh_digestname_columnname_vw

Example 1 - Basic Operations and Filters

This example creates a digest with two child tables. The histories that are to be digested have been generated by a workflow process that involves replying to a customer enquiry. It's designed to provide data for a report on the time it takes for a first response to be sent out, and includes two child tables that record the time and descriptions of each event in the history, and a list of distinct users involved in the history.

The digest will consider all histories with a labela:Customer Enquiry, and create a row for each history. The columns on the table are:

The unique business key held in labelb
The timestamp of the first event in the history (which will be the time the customer got in contact)
The timestamp of the first event called Mail Sent
A child table recording the values of all event descriptions and when they took place
A child table of unique user IDs involved in the history

Request

function(params, credentials) { let resp = this.callWorkerMethod("history", "registerDigest", { "name": "exampledigest", "filter": { "key": "labela", "EQ": "Customer Enquiry" }, "columns": [{ "name": "businesskey", "type": "varchar", "size": 50, "operation": "label", "key": "labelb", "index": "businesskey_index" }, { "name": "started", "type": "datetime", "operation": "firsttimestamp" }, { "name": "first_response_time", "type": "datetime", "eventFilter": { "key": "event", "EQ": "Mail Sent" }, "operation": "firsttimestamp" }, { "name": "all_descriptions", "type": "table", "operation": "all", "columns": [{ "name": "eventdescription", "type": "varchar", "size": 250, "operation": "value", "key": "description" }, { "name": "eventtime", "type": "datetime", "operation": "timestamp" }] }, { "name": "involved_users", "type": "table", "operation": "distinct", "columns": [{ "name": "involveduser", "type": "varchar", "size": 250, "operation": "value", "key": "userId" }] }] }); return resp; }

Response

The response includes the names of the tables and views that have been created. Not that the tables will contain no data until digestHistories() has been run.

{ "jsonrpc": "2.0", "id": 53, "result": { "created_tables": ["dh_exampledigest", "dh_exampledigest_all_descriptions", "dh_exampledigest_involved_users"], "digest_registered": "exampledigest", "created_views": ["dh_exampledigest_vw", "dh_exampledigest_all_descriptions_vw", "dh_exampledigest_involved_users_vw"] } }

Generated Views

The digest above created the following views, the digest tables themselves should not be queried. In all views the PublicID column holds the ID of the history that provided the data for that row.

dh_exampledigest_vw

This view has a row for each history with the three columns set in the registerDigest example above.

dh_exampledigest_all_descriptions_vw

This view was created using the all operation. There are multiple rows per history holding details about each event.

dh_exampledigest_involved_users_vw

This view was created using the distinct operation. Two users were involved in the histories, but each is only listed once per history.

Example 2 - Geographic Points

The point digest type is used to digest GeoJSON data stored in history events (see log() for more information about logging geographic points) and store data in geography columns. It can be used with the firstvalue and lastvalue operators. The key will be the name of the GeoJSON data packet in the history that is being digested.

{ "name": "my_digest", "filter": { ... }, "columns": [ { "name": "first_bin_location", "type": "point", "operation": "firstvalue", "key": "binLocation" }, { "name": "last_bin_location", "type": "point", "operation": "lastvalue", "key": "binLocation" } ] }

Last modified on November 15, 2023

This website uses cookies