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

Products

• DX Infrastructure Management
• NIMSOFT PROBES

Issue/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}

<Please see attached file for image>

 

 

Environment

Release:
Component: CAUIM

Resolution

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

 

Additional Information

MySQL Date Functions: https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html