# #######################################

# DATABASE 

# ####################################### 

CREATE OR REPLACE DATABASE denodo_logs 'Database that contains the following interface views to be consumed by third-party applications: - denodo_requests: information about the requests received. - denodo_connections: information about the connections opened and closed. The implementation views of these interfaces retrieves the data from the Denodo Monitor running on this same computer. The implementation of these interfaces can be switched in case Denodo Monitor is configured to store the logs on a database. **NOTE**: the end user may create these views on a database different from "denodo_logs". '; 

CONNECT DATABASE denodo_logs; 

# ####################################### 

# LISTENERS JMS 

# ####################################### 

# No listeners jms 

# ####################################### 

# DATASOURCES 

# ####################################### 

CREATE OR REPLACE DATASOURCE DF denodo_monitor_connections ROUTE LOCAL 'LocalConnection' 'C:/Denodo/DenodoPlatform9.0/tools/monitor/denodo-monitor/denodo-monitor/logs' FILENAMEPATTERN = 'vdp-connections\.log.*' CHARSET = 'UTF-8' COLUMNDELIMITER = '\t' ENDOFLINEDELIMITER = '\n' HEADER = FALSE; 

CREATE OR REPLACE DATASOURCE DF denodo_monitor_queries ROUTE LOCAL 'LocalConnection' 'C:/Denodo/DenodoPlatform9.0/tools/monitor/denodo-monitor/denodo-monitor/logs' FILENAMEPATTERN = 'vdp-queries\.log(\.\d{4}-\d{2}-\d{2})?' CHARSET = 'UTF-8' COLUMNDELIMITER = '\t' ENDOFLINEDELIMITER = '\n' HEADER = FALSE; 

# ####################################### 

# WRAPPERS 

# ####################################### 

CREATE OR REPLACE WRAPPER DF denodo_monitor_connections DATASOURCENAME=denodo_monitor_connections OUTPUTSCHEMA ( server_name = 'ServerName' (OPT), host = 'Host' (OPT), port = 'Port' (OPT), notification_type = 'NotificationType' (OPT), connection_id = 'ConnectionId' (OPT), connection_start_time = 'ConnectionStartTime' (OPT), connection_end_time = 'ConnectionEndTime' (OPT), client_ip = 'ClientIP' (OPT), user_agent = 'UserAgent' (OPT), access_interface = 'AccessInterface' (OPT), session_id = 'SessionId' (OPT), session_start_time = 'SessionStartTime' (OPT), session_end_time = 'SessionEndTime' (OPT), user_name = 'Login' (OPT), database_name = 'DatabaseName' (OPT), web_service_name = 'WebServiceName' (OPT), jms_queue_name = 'JMSQueueName' (OPT), intermediate_client_ip = 'IntermediateClientIP' (OPT) ); 

CREATE OR REPLACE WRAPPER DF denodo_monitor_queries DATASOURCENAME=denodo_monitor_queries OUTPUTSCHEMA ( server_name = 'ServerName' (OPT), host = 'Host' (OPT), port = 'Port' (OPT), request_id = 'Id' (OPT), database_name = 'Database' (OPT), user_name = 'UserName' (OPT), notification_type = 'NotificationType' (OPT), session_id = 'SessionId' (OPT), start_time = 'StartTime' (OPT), end_time = 'EndTime' (OPT), duration = 'Duration' (OPT), waiting_time = 'WaitingTime' (OPT), num_rows = 'NumRows' (OPT), state = 'State' (OPT), completed = 'Completed' (OPT), cache = 'Cache' (  OPT), query = 'Query' (OPT), request_type = 'RequestType' (OPT), elements = 'Elements' (OPT), user_agent = 'UserAgent' (OPT), access_interface = 'AccessInterface' (OPT), client_ip = 'ClientIP' (OPT), transaction_id = 'TransactionId' (OPT), webservice_name = 'WebServiceName' (OPT), cpu_time = 'CpuTime' (OPT), cpu_usage_avg = 'CpuUsageAvg' (OPT), cpu_usage_max = 'CpuUsageMax' (OPT), cpu_usage_max_time = 'CpuUsageMaxTime' (OPT), cpu_usage_std_dev = 'CpuUsageStdDev' (OPT), estimated_query_cost = 'EstimatedQueryCost' (OPT), global_security_policies_applied = 'GlobalSecurityPoliciesApplied' (OPT));# ####################################### 

# BASE VIEWS 

# ####################################### 

CREATE OR REPLACE TABLE bv_denodo_monitor_connections I18N us_est ( server_name:text, host:text, port:text, notification_type:text, connection_id:text, connection_start_time:text, connection_end_time:text, client_ip:text, user_agent:text, access_interface:text, session_id:text, session_start_time:text, session_end_time:text, user_name:text, database_name:text, web_service_name:text, jms_queue_name:text, intermediate_client_ip:text ) CACHE OFF TIMETOLIVEINCACHE DEFAULT ADD SEARCHMETHOD denodo_monitor_connections( I18N us_est CONSTRAINTS ( ADD server_name (=,in) OPT ANY ADD host (=,in) OPT ANY ADD port (=,in) OPT ANY ADD notification_type (=,in) OPT ANY ADD connection_id (=,in) OPT ANY ADD connection_start_time (=,in) OPT ANY ADD connection_end_time (=,in) OPT ANY ADD client_ip (=,in) OPT ANY ADD user_agent (=,in) OPT ANY ADD access_interface (=,in) OPT ANY ADD session_id (=,in) OPT ANY ADD session_start_time (=,in) OPT ANY ADD session_end_time (=,in) OPT ANY ADD user_name (=,in) OPT ANY ADD database_name (=,in) OPT ANY ADD web_service_name (=,in) OPT ANY ADD jms_queue_name (=,in) OPT ANY ADD intermediate_client_ip (=,in) OPT ANY ) OUTPUTLIST (access_interface, client_ip, connection_end_time, connection_id, connection_start_time, database_name, host, intermediate_client_ip, jms_queue_name, notification_type, port, server_name, session_end_time, session_id, session_start_time, user_agent, user_name, web_service_name ) WRAPPER (df denodo_monitor_connections) ); 

CREATE OR REPLACE TABLE bv_denodo_monitor_queries I18N us_est ( server_name:text, host:text, port:text, request_id:text, database_name:text, user_name:text, notification_type:text, session_id:text, start_time:text, end_time:text, duration:text, waiting_time:text, num_rows:text, state:text, completed:text, cache:text, query:text, request_type:text, elements:text, user_agent:text, access_interface:text, client_ip:text, transaction_id:text, webservice_name:text,cpu_time:text,cpu_usage_avg:text,cpu_usage_max:text,cpu_usage_max_time:text,cpu_usage_std_dev:text,estimated_query_cost:text,global_security_policies_applied:text) CACHE OFF TIMETOLIVEINCACHE DEFAULT ADD SEARCHMETHOD denodo_monitor_queries( I18N us_est CONSTRAINTS ( ADD server_name (=,in) OPT ANY ADD host (=,in) OPT ANY ADD port (=,in) OPT ANY ADD request_id (=,in) OPT ANY ADD database_name (=,in) OPT ANY ADD user_name (=,in) OPT ANY ADD notification_type (=,in) OPT ANY ADD session_id (=,in) OPT ANY ADD start_time (=,in) OPT ANY ADD end_time (=,in) OPT ANY ADD duration (=,in) OPT ANY ADD waiting_time (=,in) OPT ANY ADD num_rows (=,in) OPT ANY ADD state (=,in) OPT ANY ADD completed (=,in) OPT ANY ADD cache (=,in) OPT ANY ADD query (=,in) OPT ANY ADD request_type (=,in) OPT ANY ADD elements (=,in) OPT ANY ADD user_agent (=,in) OPT ANY ADD access_interface (=,in) OPT ANY ADD client_ip (=,in) OPT ANY ADD transaction_id (=,in) OPT ANY ADD webservice_name (=,in) OPT ANY ADD cpu_time (=,in) OPT ANY ADD cpu_usage_avg (=,in) OPT ANY ADD cpu_usage_max (=,in) OPT ANY ADD cpu_usage_max_time (=,in) OPT ANY ADD cpu_usage_std_dev (=,in) OPT ANY ADD estimated_query_cost (=,in) OPT ANY ADD global_security_policies_applied (=,in) OPT ANY) OUTPUTLIST (access_interface, cache, client_ip, completed, database_name, duration, elements, end_time, host, notification_type, num_rows, port, query, request_id, request_type, server_name, session_id, start_time, state, transaction_id, user_agent, user_name, waiting_time, webservice_name, cpu_time, cpu_usage_avg, cpu_usage_max, cpu_usage_max_time, cpu_usage_std_dev, estimated_query_cost, global_security_policies_applied ) WRAPPER (df denodo_monitor_queries));

# ####################################### 

# VIEWS 

# ####################################### 

CREATE OR REPLACE VIEW p_denodo_connections DESCRIPTION = 'View that returns the information from the "connections" log captured by the Denodo Monitor. The data source over which this view is built assumes that Denodo Monitor is storing the logs in /tools/monitor/denodo_monitor/logs.' AS SELECT server_name AS server_name, host AS host, port AS port, notification_type AS notification_type, connection_id AS connection_id, to_timestamp('yyyy-MM-dd''T''HH:mm:ss.SSS', connection_start_time) AS connection_start_time, case WHEN (connection_end_time = '-') THEN NULL ELSE to_timestamp('yyyy-MM-dd''T''HH:mm:ss.SSS', connection_end_time) END AS connection_end_time, client_ip AS client_ip, user_agent AS user_agent, access_interface AS access_interface, session_id AS session_id, to_timestamp('yyyy-MM-dd''T''HH:mm:ss.SSS', session_start_time) AS session_start_time, case WHEN (session_end_time = '-') THEN NULL ELSE to_timestamp('yyyy-MM-dd''T''HH:mm:ss.SSS', session_end_time) END AS session_end_time, user_name AS user_name, database_name AS database_name, case WHEN (jms_queue_name = '-') THEN NULL ELSE jms_queue_name END AS jms_queue_name, case WHEN (intermediate_client_ip = '-') THEN NULL ELSE intermediate_client_ip END AS intermediate_client_ip, case WHEN (web_service_name = '-') THEN NULL ELSE web_service_name END AS web_service_name FROM bv_denodo_monitor_connections WHERE server_name <> 'ServerName';

CREATE OR REPLACE INTERFACE VIEW denodo_connections ( server_name:text, host:text, port:text, notification_type:text, connection_id:text, connection_start_time:timestamp, connection_end_time:timestamp, client_ip:text, user_agent:text, access_interface:text, session_id:text, session_start_time:timestamp, session_end_time:timestamp, user_name:text, database_name:text, jms_queue_name:text, intermediate_client_ip:text, web_service_name:text ) SET IMPLEMENTATION p_denodo_connections DESCRIPTION = 'Interface view that returns the information from the "connections" log captured by the Denodo Monitor.'; 

CREATE OR REPLACE VIEW p_bv_denodo_requests DESCRIPTION = 'View that returns the information from the "requests" log files captured by the Denodo Monitor. It applies the CAST function over several columns to adjust their data type. Esentially, apply the CAST function to convert values that contain "datetime" values to publish them as "timestamp". The data source over which this view is built assumes that Denodo Monitor is storing the logs in /tools/monitor/denodo_monitor/logs.' AS SELECT server_name AS server_name, host AS host, port AS port, request_id AS request_id, database_name AS database_name, user_name AS user_name, notification_type AS notification_type, session_id AS session_id, to_timestamp('yyyy-MM-dd''T''HH:mm:ss.SSS', start_time) AS start_time, case WHEN (end_time = '-') THEN NULL ELSE to_timestamp('yyyy-MM-dd''T''HH:mm:ss.SSS', end_time) END AS end_time, case WHEN (duration = '-') THEN NULL ELSE cast('long', duration) END AS duration, case WHEN (waiting_time = '-') THEN NULL ELSE cast('long', waiting_time) END AS waiting_time, case WHEN (num_rows = '-') THEN NULL ELSE cast('long', num_rows) END AS num_rows, case WHEN (state = '-') THEN NULL ELSE state END AS state, case WHEN (completed = '-') THEN NULL ELSE cast('boolean', completed) END AS completed, case WHEN (cache = '-') THEN NULL ELSE cast('boolean', cache) END AS cache, query AS query, request_type AS request_type, case WHEN (elements = '-') THEN NULL ELSE elements END AS elements, user_agent AS user_agent, access_interface AS access_interface, client_ip AS client_ip, case WHEN (transaction_id = '-') THEN NULL ELSE transaction_id END AS transaction_id, case WHEN (webservice_name = '-') THEN NULL ELSE webservice_name END AS webservice_name, case WHEN (cpu_time = '-') THEN NULL ELSE cpu_time END AS cpu_time, case WHEN (cpu_usage_avg = '-') THEN NULL ELSE cpu_usage_avg END AS cpu_usage_avg, case WHEN (cpu_usage_max = '-') THEN NULL ELSE cpu_usage_max END AS cpu_usage_max, case WHEN (cpu_usage_max_time = '-') THEN NULL ELSE cpu_usage_max_time END AS cpu_usage_max_time, case WHEN (cpu_usage_std_dev = '-') THEN NULL ELSE cpu_usage_std_dev END AS cpu_usage_std_dev, case WHEN (estimated_query_cost = '-') THEN NULL ELSE estimated_query_cost END AS estimated_query_cost, case WHEN (global_security_policies_applied = '-') THEN NULL ELSE global_security_policies_applied END AS global_security_policies_applied FROM bv_denodo_monitor_queries WHERE server_name <> 'ServerName';

CREATE OR REPLACE INTERFACE VIEW i_denodo_requests_appropriate_data_types ( server_name:text, host:text, port:text, request_id:text, database_name:text, user_name:text, notification_type:text, session_id:text, start_time:timestamp, end_time:timestamp, duration:long, waiting_time:long, num_rows:long, state:text, completed:boolean, cache:boolean, query:text, request_type:text, elements:text, user_agent:text, access_interface:text, client_ip:text, transaction_id:text, webservice_name:text, cpu_time:text, cpu_usage_avg:text, cpu_usage_max:text, cpu_usage_max_time:text, cpu_usage_std_dev:text, estimated_query_cost:text, global_security_policies_applied:text) SET IMPLEMENTATION p_bv_denodo_requests;

CREATE OR REPLACE VIEW denodo_request_transform_query DESCRIPTION = 'Removes the "CONTEXT" clause from the queries (column "query") so they can be analyzed by the "Query Log Ingestion" process of Alation.' AS SELECT i_denodo_requests_appropriate_data_types.server_name AS server_name, i_denodo_requests_appropriate_data_types.host AS host, i_denodo_requests_appropriate_data_types.port AS port, i_denodo_requests_appropriate_data_types.request_id AS request_id, i_denodo_requests_appropriate_data_types.database_name AS database_name, i_denodo_requests_appropriate_data_types.user_name AS user_name, i_denodo_requests_appropriate_data_types.notification_type AS notification_type, i_denodo_requests_appropriate_data_types.session_id AS session_id, i_denodo_requests_appropriate_data_types.start_time AS start_time, i_denodo_requests_appropriate_data_types.end_time AS end_time, i_denodo_requests_appropriate_data_types.duration AS duration, i_denodo_requests_appropriate_data_types.waiting_time AS waiting_time, i_denodo_requests_appropriate_data_types.num_rows AS num_rows, i_denodo_requests_appropriate_data_types.state AS state, i_denodo_requests_appropriate_data_types.completed AS completed, i_denodo_requests_appropriate_data_types.cache AS cache, case WHEN ((position('SELECT' IN upper(query)) = 1 AND position('CONTEXT' IN upper(query)) > 0)) THEN regexp(query, '(.*)context\s*\(.*\)\s*$', '$1') ELSE query END AS query, i_denodo_requests_appropriate_data_types.request_type AS request_type, i_denodo_requests_appropriate_data_types.elements AS elements, i_denodo_requests_appropriate_data_types.user_agent AS user_agent, i_denodo_requests_appropriate_data_types.access_interface AS access_interface, i_denodo_requests_appropriate_data_types.client_ip AS client_ip, i_denodo_requests_appropriate_data_types.transaction_id AS transaction_id, i_denodo_requests_appropriate_data_types.webservice_name AS webservice_name, i_denodo_requests_appropriate_data_types.cpu_time AS cpu_time, i_denodo_requests_appropriate_data_types.cpu_usage_avg AS cpu_usage_avg, i_denodo_requests_appropriate_data_types.cpu_usage_max AS cpu_usage_max, i_denodo_requests_appropriate_data_types.cpu_usage_max_time AS cpu_usage_max_time, i_denodo_requests_appropriate_data_types.cpu_usage_std_dev AS cpu_usage_std_dev, i_denodo_requests_appropriate_data_types.estimated_query_cost AS estimated_query_cost, i_denodo_requests_appropriate_data_types.global_security_policies_applied AS global_security_policies_applied FROM i_denodo_requests_appropriate_data_types;

CREATE OR REPLACE INTERFACE VIEW denodo_requests ( server_name:text, host:text, port:text, request_id:text, database_name:text, user_name:text, notification_type:text, session_id:text, start_time:timestamp, end_time:timestamp, duration:long, waiting_time:long, num_rows:long, state:text, completed:boolean, cache:boolean, query:text, request_type:text, elements:text, user_agent:text, access_interface:text, client_ip:text, transaction_id:text, webservice_name:text, cpu_time:text, cpu_usage_avg:text, cpu_usage_max:text, cpu_usage_max_time:text, cpu_usage_std_dev:text, estimated_query_cost:text, global_security_policies_applied:text) SET IMPLEMENTATION denodo_request_transform_query DESCRIPTION = 'Interface view that returns the information from the "requests" log captured by the Denodo Monitor.'; 

CLOSE; 

SELECT

user_name AS userName,

'-' AS defaultDatabases,

session_id AS sessionId,

FORMATDATE('yyyy-MM-dd'' ''HH:mm:ss.SSS', start_time) AS startTime,

FORMATDATE('yyyy-MM-dd'' ''HH:mm:ss.SSS', start_time) AS sessionStartTime,

query AS queryString

FROM <database_name>.denodo_requests

WHERE notification_type = 'startRequest'

AND query NOT LIKE 'DESC%'

AND query NOT LIKE 'CALL%'

AND query NOT LIKE 'LIST%';

CREATE OR REPLACE VIEW <alation_QLI_view_name> as SELECT

user_name AS userName,

'-' AS defaultDatabases,

session_id AS sessionId,

FORMATDATE('yyyy-MM-dd'' ''HH:mm:ss.SSS', start_time) AS startTime,

FORMATDATE('yyyy-MM-dd'' ''HH:mm:ss.SSS', start_time) AS sessionStartTime,

query AS queryString

FROM <database_name>.denodo_requests

WHERE notification_type = 'startRequest'

AND query NOT LIKE 'DESC%'

AND query NOT LIKE 'CALL%'

AND query NOT LIKE 'LIST%';