Joel,

The easiest way to handle gap finding is to have a "numbers table" from 0
to 1 million or whatever. You can use it for all kinds of cool things...
https://dba.stackexchange.com/questions/11506/why-are-numbers-tables-invaluable

In your specific case, you might consider a specialized "hour minute" table
(or even a view over the numbers table) that just goes from 00:00 to 23:59.

Charles



On Mon, Dec 29, 2014 at 12:45 PM, Stone, Joel <Joel.Stone@xxxxxxxxxx> wrote:

We are a heavy Webdocs (document management s/w) user, and webdocs is
intermittently freezing for 20-30 minutes a few times per week. It
self-recovers with no intervention.

I would like to send an email to several IT personnel when this occurs,
instead of having users call us.

So, how to detect when Webdocs is unresponsive?

There is a log file of every event that Webdocs records.

I would like to query the log file, and identify gaps in time that no
activity has occurred. I was thinking to run a job every 5 minutes or
so, and if there are any minute gaps in the log file (during the business
day), then I would know that a freeze has probably occurred.

Is it possible in SQL to inform me when a "minute" record doesn't exist in
a sequence?

For example, if there were events at 9:36, 9:37, 9:38, 9:39, and 9:41, and
it is now 9:41, can SQL somehow inform me that 9:40 is missing?

Thanks!





Here is the SQL that I have so far:

select
hour(ldate),
minute(ldate),count(*)
from rjsimage/doclog00
where minute(timestamp(curdate(),curtime())- ldate) <20
and date(curdate()) = date(ldate)
and hour(curtime()) = hour(ldate)
group by hour (ldate),
minute(ldate)
order by minute(ldate)


....+....1....+....2....+....3....+....4....+....
HOUR MINUTE COUNT ( * )
9 36 10
9 37 20
9 38 16
9 39 23
9 40 19
9 41 15
9 42 18
9 43 21
9 44 7
9 45 16
9 46 6
9 47 9
9 48 16
9 49 11
9 50 27
9 51 10
9 52 14
9 53 15
9 54 14


______________________________________________________________________
This outbound email has been scanned for all viruses by the MessageLabs
Skyscan service.
For more information please visit http://www.symanteccloud.com
______________________________________________________________________
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.



This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2019 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].