Administration Guide > Collecting TDV and Data Usage Metrics > Setting Up and Configuring Metrics Collection > Pre-Creating the External Database and Tables for Metrics Data Storage
 
Pre-Creating the External Database and Tables for Metrics Data Storage
If you need to create the database and table that are used to store your usage metrics without using Studio, you can use the following instructions.
To create the metrics database and required tables
1. Using the administration tools or command line calls, create the following tables on a PostgreSQL, Oracle, or SQL Server database. For Oracle there are a few syntax differences, such as BIGINT needs to be number (10,0).
Table Name
Create Table Syntax
Create Table Syntax for Oracle
Create Table for SQL Server
metrics_sessions
CREATE OR REPLACE TABLE tutorial.metrics_sessions (
cluster VARCHAR(255),
nodehost VARCHAR(255) NOT NULL,
nodeport INTEGER NOT NULL,
sessionid BIGINT NOT NULL,
sessiontype VARCHAR(40) NOT NULL,
clienthost VARCHAR(255),
type VARCHAR(20) NOT NULL,
logintime TIMESTAMP NOT NULL,
logouttime TIMESTAMP,
status VARCHAR(20),
totalduration BIGINT,
totalRequests BIGINT,
bytestoclient BIGINT,
bytesfromclient BIGINT,
user VARCHAR(255),
domain VARCHAR(255),
group VARCHAR(255)
);
CREATE OR REPLACE TABLE tutorial.metrics_sessions (
cluster VARCHAR(255),
nodehost VARCHAR(255) NOT NULL,
nodeport INTEGER NOT NULL,
sessionid BIGINT NOT NULL,
sessiontype VARCHAR(40) NOT NULL,
clienthost VARCHAR(255),
type VARCHAR(20) NOT NULL,
logintime TIMESTAMP NOT NULL,
logouttime TIMESTAMP,
status VARCHAR(20),
totalduration number(10, 0),
totalRequests number(10, 0),
bytestoclient number(10, 0),
bytesfromclient number(10, 0),
user VARCHAR(255),
domain VARCHAR(255),
group (VARCHAR(255)
);
CREATE TABLE [Northwind].[guest].[metrics_sessions] (
[cluster] varchar(255),
[nodehost] varchar(255) NOT NULL,
[nodeport] int NOT NULL,
[sessionid] bigint NOT NULL,
[sessiontype] varchar(40) NOT NULL,
[clienthost] varchar(255),
[type] varchar(20) NOT NULL,
[logintime] datetime NOT NULL,
[logouttime] datetime,
[status] varchar(20),
[totalduration] bigint,
[totalRequests] bigint,
[bytestoclient] bigint,
[bytesfromclient] bigint,
[user] varchar(255),
[domain] varchar(255),
[group] varchar(255)
)
metrics_requests
CREATE OR REPLACE TABLE tutorial.metrics_requests (
cluster VARCHAR(255),
nodehost VARCHAR(255) NOT NULL,
nodeport INTEGER NOT NULL,
requestid BIGINT NOT NULL,
parentid BIGINT,
sessionid BIGINT NOT NULL,
requesttype VARCHAR(255) NOT NULL,
description VARCHAR(65535),
starttime TIMESTAMP NOT NULL,
endtime TIMESTAMP,
totalduration BIGINT,
serverduration BIGINT,
rowsAffected BIGINT,
maxmemory BIGINT,
maxdisk BIGINT,
message VARCHAR(65535),
status VARCHAR(20),
user VARCHAR(255),
domain VARCHAR(255),
group VARCHAR(255)
);
CREATE OR REPLACE TABLE tutorial.metrics_requests (
cluster VARCHAR(255),
nodehost VARCHAR(255) NOT NULL,
nodeport INTEGER NOT NULL,
requestid BIGINT NOT NULL,
parentid BIGINT,
sessionid BIGINT NOT NULL,
requesttype VARCHAR(255) NOT NULL,
description VARCHAR(65535),
starttime timestamp(9) NOT NULL,
endtime timestamp(9),
totalduration number(10, 0),
serverduration number(10, 0),
rowsAffected number(10, 0),
maxmemory number(10, 0),
maxdisk number(10, 0),
message CLOB,
status VARCHAR(20),
user VARCHAR(255),
domain VARCHAR(255),
group VARCHAR(255)
);
CREATE TABLE [Northwind].[guest].[metrics_requests] (
[cluster] varchar(255),
[nodehost] varchar(255) NOT NULL,
[nodeport] int NOT NULL,
[requestid] bigint NOT NULL,
[parentid] bigint,
[sessionid] bigint NOT NULL,
[requesttype] varchar(255) NOT NULL,
[description] text,
[starttime] datetime NOT NULL,
[endtime] datetime,
[totalduration] bigint,
[serverduration] bigint,
[rowsAffected] bigint,
[maxmemory] bigint,
[maxdisk] bigint,
[message] text,
[status] varchar(20),
[user] VARCHAR(255),
[domain] VARCHAR(255),
[group] VARCHAR(255)
)
metrics_resources_usage
CREATE OR REPLACE TABLE tutorial.metrics_resources_usage (
cluster VARCHAR(255),
nodehost VARCHAR(255) NOT NULL,
nodeport INTEGER NOT NULL,
sessionid BIGINT NOT NULL,
user VARCHAR(255),
domain VARCHAR(255),
group VARCHAR(255),
requestid BIGINT NOT NULL,
parentid BIGINT,
datasourcepath VARCHAR(255),
datasourcetype VARCHAR(255),
resourcepath VARCHAR(255),
resourcetype VARCHAR(40),
resourceguid VARCHAR(40),
resourcekind VARCHAR(20),
starttime TIMESTAMP NOT NULL,
endtime TIMESTAMP
);
CREATE OR REPLACE TABLE tutorial.metrics_resources_usage (
cluster VARCHAR(255),
nodehost VARCHAR(255) NOT NULL,
nodeport INTEGER NOT NULL,
sessionid number(10, 0) NOT NULL,
user VARCHAR(255),
domain VARCHAR(255),
group VARCHAR(255),
requestid number(10, 0) NOT NULL,
parentid number(10, 0),
datasourcepath VARCHAR(255),
datasourcetype VARCHAR(255),
resourcepath VARCHAR(255),
resourcetype VARCHAR(40),
resourceguid VARCHAR(40),
resourcekind VARCHAR(20),
starttime timestamp(9) NOT NULL,
endtime timestamp(9)
);
CREATE TABLE [Northwind].[guest].[metrics_resources_usage] (
[cluster] varchar(255),
[nodehost] varchar(255) NOT NULL,
[nodeport] int NOT NULL,
[sessionid] bigint NOT NULL,
[user] varchar(255),
[domain] varchar(255),
[group] varchar(255),
[requestid] bigint NOT NULL,
[parentid] bigint,
[datasourcepath] varchar(255),
[datasourcetype] varchar(255),
[resourcepath] varchar(255),
[resourcetype] varchar(40),
[resourceguid] varchar(40),
[resourcekind] varchar(20),
[starttime] datetime NOT NULL,
[endtime] datetime
)
2. Make sure that permissions on the database and tables allow for their modification from TDV Studio.
3. Note the connection information for the database, so that you have the necessary information to add it as a data source in Studio.
4. Open Studio.
5. Create a new data source that connects to the database and tables that you have created to hold metrics storage data.
6. In the Studio resource tree, select localhost > policy > metrics.
7. Right click and select Open.
8. Browse to the data source where you created the tables for the storage of metrics data. See Supported Data Source Types for Metrics Storage.
9. Bind the tables that you created for the storage of metrics data.
10. Browse to the schema location of the tables that you created.
11. Bind each of the following:
Table Name
Description of Steps to Create the Table
metrics_sessions
• Click Browse.
• Navigate to the data source or schema where you created the table.
• Click OK.
metrics_requests
• Click Browse.
• Navigate to the data source or schema where you created the table.
• Click OK
metrics_resources_usage
• Click Browse.
• Navigate to the data source or schema where you created the table.
• Click OK
12. Click OK.
13. Click Save.
14. Optionally, make selections for the following:
Option
Description
Memory Threshold
Number of megabytes to set aside as the buffer space to store records. When the buffer reaches the threshold, the results are posted to the metrics tables.
Request Count Threshold
Number of records or rows to retain in the buffer. When the buffer reaches the threshold, the results are posted to the metrics tables.
How long do you want to keep the metrics data?
Use to indicate the number of days you want your metrics data retained.
The default is 30 days.
How often do you want to run the truncate process on expired data?
Use to indicate the time frequency with which you want the metrics collection database data to be truncated. The default is 1 hour.
15. Click Enable.