Sample SQL queries for using the new Context Selector widget (Time option) in UIM 8.1

Document ID : KB000034698
Last Modified Date : 14/02/2018
Show Technical Document Details

Introduction

In the Context selector widget when you select Time, the user can select a time period from a drop down menu ranging from the last hour to the last 12 months. The selector passes the required start and end times to all SQL queries that use them in the dashboard, and appropriate data is displayed. Selecting another time period from the menu passes new values to the queries, and the dashboard automatically displays the new data.

Below are three samples queries with a WHERE clause on how to use the ${startTime} and the ${endTime}

Selector Widget with Date and custom




Procedure

Sample 1:
select convert(varchar, time, 120) time,severity,message
from NAS_TRANSACTION_LOG
where nimts between dateadd(ss, ${startTime}/1000,'1970-01-01 00:00:00:000')
and dateadd(ss, ${endTime}/1000,'1970-01-01 00:00:00:000') order by time



Sample 2:
SELECT??? rn28.sampletime,rn28.samplevalue
FROM???????? CM_CONFIGURATION_ITEM_METRIC AS cim INNER JOIN
????????????????????? S_QOS_DATA AS sqd ON cim.ci_metric_id = sqd.ci_metric_id INNER JOIN
????????????????????? CM_CONFIGURATION_ITEM AS ci ON cim.ci_id = ci.ci_id INNER JOIN
????????????????????? CM_COMPUTER_SYSTEM AS CM INNER JOIN
????????????????????? CM_DEVICE ON CM.cs_id = CM_DEVICE.cs_id ON ci.dev_id = CM_DEVICE.dev_id INNER JOIN
????????????????????? RN_QOS_DATA_0028 AS rn28 ON sqd.table_id = rn28.table_id

Where rn28.sampletime between dateadd(ss, ${startTime}/1000,'1970-01-01 00:00:00:000') and dateadd(ss, ${endTime}/1000,'1970-01-01 00:00:00:000')

Sample 3:
select entity_name as "Virtual Machine",
util_value as "Avg. Util.",
max_util as "Max Util",
trunc(num_cpus,0) num_cpus

from ( select /*+ ordered */
entity_name,
trunc(util_value,2) util_value,
trunc(max_util,2) max_util,
num_cpus,
round(total_memory/(1024*1024)) total_memory,
trunc(100-util_value,2) unused_util_value,
physical_host

from ( select guid,
avg(case when rollup_id = 1 then metric_value else null end) as util_value,
max(case when rollup_id = 9 then metric_value else null end) as max_util

from entity_day_fact a

where day_id between
to_char((to_date('1970-01-01','YYYY-MM-DD') + numtodsinterval(${startTime}/1000,'SECOND')),'YYYYMMDD')

and

to_char((to_date('1970-01-01','YYYY-MM-DD') + numtodsinterval(${endTime}/1000,'SECOND')),'YYYYMMDD')


and metric_id in ( select metric_dim_id from config_metric_dim

where std_name in ('GBL_LS_VIRT_CPU_UTIL'))

and rollup_id in (1,9)

group by guid ) a,
(select guid,
entity_name,
online_virt_cpu num_cpus,
online_memory total_memory,
c.physical_host

from entity a,
server_dim b,
server_virtual_dim c

where a.entity_name = b.host_name and b.physical_host is not null
and ( lower(c.virtualization_solution) like 'esx%' or lower(c.virtualization_solution) like 'vmware%')
and c.end_date is null
and b.server_dim_id = c.server_dim_id) b

where a.guid = b.guid)

order by util_value desc

keywords: UMP time selector widget TimeSelector widgets dashboard dashboards query

?