Direct Access to Database Tables 
Generally, portal activity report data is displayed in iViews, pre-defined and delivered with the portal or created from templates and customized by portal administrators. Instead, you can access this aggregated data directly from the portal database.
The following lists the portal database tables, along with each table’s fields, for holding the aggregated data that is used for creating portal activity reports:
· WCR_USERSTAT: Holds information about the number of users who logged on to the portal.
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 |
Hashed user ID |
USERTYPE |
nvarchar |
1 |
The type of user, either Named or Anonymous |
LOGONID |
nvarchar |
255 |
The user’s logon ID, or null if the user ID is hashed |
· WCR_WEBCONTENTSTAT: Holds information about the content that was viewed.
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 has 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.
· WCR_USERFIRSTLOGON: Holds information about the first logon of each user and how many pages and iViews the user viewed.
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, either Named or Anonymous |
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 |
· WCR_USERPAGEUSAGE: Holds information about which users viewed which pages and iViews.
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 |