Direct Access to Database Tables
Generally, portal activity report data is displayed in iViews, predefined and delivered with the portal or created from templates and customized by portal administrators. For alternative use of this data, such as displaying the data using an external or proprietary application, it is possible to access the aggregated data directly from the portal database using an external database management system. For those who want direct access to the activity reporting tables, their structure is described in the following tables.
The following tables describe the structure of the portal database reporting tables.
The data contained in these tables is the data that appears in the activity report iViews. Limiting direct access to the tables to advanced users is recommended.
|
Field Name |
Data Type |
Length |
Description |
|---|---|---|---|
|
TIMESTAMPHOUR |
bigint |
8 |
The time the user logged on to the portal, a long value equal to the number of milliseconds since January 1, 1970, 00:00:00 GMT |
|
AGGREGATIONLEVEL |
nvarchar |
1 |
The aggregation level, either h , d , m , q |
|
HASHEDUSERID |
nvarchar |
28 |
If the USERTYPE = a (anonymous), the number of logons by anonymous users If the USERTYPE = n (named), then this column contains the username |
|
USERTYPE |
nvarchar |
1 |
The type of user, either named or anonymous .. |
|
LOGONID |
nvarchar |
255 |
The user's logon ID Caution
This column must not contain null. |
|
Field Name |
Data Type |
Length |
Description |
|---|---|---|---|
|
TIMESTAMPHOUR |
bigint |
8 |
The time the content was viewed, a long value equal to the number of milliseconds since January 1, 1970, 00:00:00 GMT. |
|
AGGREGATIONLEVEL |
nvarchar |
1 |
The aggregation level, either h , d , m , q |
|
COUNTER |
bigint |
8 |
For internal use |
|
PCDURL |
nvarchar |
1024 |
The PCD address of the object |
|
OBJECTTYPE |
nvarchar |
1 |
The type of object, either Page or iView |
|
IMPRESSIONS |
int |
4 |
The number of times the object was viewed in the specified period (hit count) |
|
VISITS |
int |
4 |
The number of unique users who saw the object in the specified period |
|
CUSTOM |
nvarchar |
1024 |
The title of the object |
|
ID |
bigint |
8 |
A unique ID for each page in the table, later used for retrieving the users who saw the page |
Each page is given a unique ID for each aggregation period in which it was viewed. For example, if a page was viewed between 9 and 10 a.m. and also between 10 and 11 a.m., two records are created in the table, each with h as the aggregation level and each with a unique ID field. At the end of the day, a third record is created, with d as the aggregation level and a different unique ID.
|
Field Name |
Data Type |
Length |
Description |
|---|---|---|---|
|
TIMESTAMPHOUR |
bigint |
8 |
The time the user first logged on to the portal, a long value equal to the number of milliseconds since January 1, 1970, 00:00:00 GMT |
|
USERTYPE |
nvarchar |
1 |
The type of user. (Only the user type named is relevant for this field.) |
|
LOGONID |
nvarchar |
255 |
The user's logon ID, or null if the user ID is hashed |
|
IMPRESSIONS |
bigint |
8 |
The number of pages and iViews the user viewed |
|
Field Name |
Data Type |
Length |
Description |
|---|---|---|---|
|
HOURLY_ID |
bigint |
8 |
A page ID defined in the WCR_WEBCONTENTSTAT table, indicating that the user that is specified in the LOGONID field viewed the page for this aggregation period |
|
DAILY_ID |
bigint |
8 |
|
|
WEEKLY_ID |
bigint |
8 |
|
|
MONTHLY_ID |
bigint |
8 |
|
|
QUARTERLY_ID |
bigint |
8 |
|
|
LOGONID |
nvarchar |
255 |
The logon ID of a user who viewed the page with one of the IDs in the above fields |
|
Field Name |
Data Type |
Length |
Description |
|---|---|---|---|
|
NUMAGGREGATES |
bigint |
8 |
The number of records aggregated |
|
LASTAGGLEVEL |
nvarchar |
1 |
The interval upon which the current aggregation was based. (Monthly and weekly aggregations are based on daily aggregations; quarterly on monthly; daily on hourly.) |
|
AGGREGATIONLEVEL |
nvarchar |
1 |
The aggregation level |
|
TIMESTAMPHOUR |
bigint |
8 |
The timestamp at the bottom range of the period for which the aggregation is performed. |