On 2/9/10 6:09 PM, drugh10 wrote:
> Yes, I have the schema, and tools to read the database, etc....just not sure
> which variable(s) to look for, and which table(s) is(are) used. I've run some
> queries against the public.datapoint table for specific dataset_id's, but
> haven't found numbers to match what is displayed in the status window. It
> also takes quite a while to run the query against the entire table (duh), so
> there must also be a better way to do that (use dates)...
>
> The values in the status window must be stored in a temporary area and
> calculated on the fly for faster performance, yes??
Availability is computed by tracking time-spent-down for each device.
This statistic is tracked internally by InterMapper.
Availability = time-spent-down / time-monitored*
(* InterMapper itself uses time-spent-down + time-spent-up as the value
for time-monitored. This does not include time-spent-unknown)
To compute availability from the database, run a query against the
'event' table to obtain the total down_time for a particular time period
(?time1 to ?time2):
SELECT server_id, map_id, device_id,
SUM((CASE WHEN end_time > ?time2 THEN ?time2 ELSE end_time END) -
(CASE WHEN begin_time < ?time1 THEN ?time1 ELSE
begin_time END)) AS down_time
FROM event
WHERE (?time1, ?time2) OVERLAPS (begin_time, end_time)
AND status = 'down'
GROUP BY server_id, map_id, device_id;
The CASE WHEN's limit the begin_time and end_time to the specified time
period.
You'll need to join the above sub-query with the public.device table on
server_id, map_id, device_id to get meaningful device names. At the same
time, you can add a computed column for the availability percentage by
dividing down_time by (?time2 - ?time1).
--
Bill Fisher
Dartware, LLC
____________________________________________________________________
List archives:
http://www.mail-archive.com/intermapper-talk%40list.dartware.com/
To unsubscribe: send email to: [email protected]
