Uptime report for servers polled by cdm (w/samplevalue conversion)

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)
• DX Unified Infrastructure Management (Nimsoft / UIM)
• CA Unified Infrastructure Management SaaS (Nimsoft / UIM)
• Unified Infrastructure Management for Mainframe

Issue/Introduction

Looking for a database query to pull the server uptime for every server being polled by the CDM probe.

Environment

Release : 20.4

Component : UIM - CDM WITH IOSTAT

Cause

- uptime report

Resolution

Computer uptime, as reported by the cdm probe will be shown as cumulative, in seconds, since the robot was started. If you are looking for 'Availability' you might try using the net_connect probe to ping a device and then base an SLA on the maximum response time you consider the node to be 'available' versus unavailable. Uptime is the number of seconds the computer has been running since the last reboot. It is not that useful for an SLA because it is a counter that continues to rise until a reboot occurs. Note also that it is not possible to change the interval this metric is reported by the cdm probe.  It is hard-coded to once an hour.

select sqd.source, sqd.target, sqs.sampletime, sqs.samplevalue from s_qos_data AS sqd
join s_qos_snapshot sqs on
sqd.table_id = sqs.table_id
where sqd.qos like '%uptime%';

You can use this as a starting point and modify it for specific machines, for example;

select sqd.source, sqd.target, sqs.sampletime, sqs.samplevalue from s_qos_data AS sqd
join s_qos_snapshot sqs on
sqd.table_id = sqs.table_id
where sqd.qos like '%uptime%'
and sqd.source like '%hostname%';

Here is the query expanded to support conversion of the cumulative seconds of uptime into days, hours, minutes and seconds.

SELECT source, target, sampletime, samplevalue,Formatted_time = CAST(FLOOR(samplevalue / 86400) AS VARCHAR(10))+'d ' + CONVERT(VARCHAR(8), DATEADD(SECOND, samplevalue, '19000101'), 8)
FROM    (select sqd.source, sqd.target, sqs.sampletime, sqs.samplevalue from s_qos_data AS sqd
join s_qos_snapshot sqs on
sqd.table_id = sqs.table_id
where sqd.qos like '%uptime%') S

Query results – example:

 

Additional Information

Online converter to check conversion of samplevalue (seconds) to dats, minutes, hours and seconds