× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Thanks for the several ideas suggesting to create a "numbers" table.

That will actually work perfectly for the requirements that I presented.

However, I have a feeling that sometimes a 1 minute lack of activity will not be adequate to identify a freeze, for example at 6 am prior to full business activity.

So I would like to modify the requirements to show any 2 minute or even 5 minute gaps? (Hey users modify requirements all the time, why shouldn't I haha).

Im not sure a table of numbers will identify that larger gap.

I was thinking maybe some type of MIN/MAX statement to find the max time between two adjacent times??

Any other creative or clever ideas to do this?

I appreciate the trigger idea, however on our production box that would take quite an effort to get mgmt. approval on such a change.





-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Charles Wilt
Sent: Monday, December 29, 2014 12:02 PM
To: Midrange Systems Technical Discussion
Subject: Re: SQL: How to create a list of MISSING numbers in a sequence

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.



As an Amazon Associate we earn from qualifying purchases.

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-2024 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].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.