× 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.



Joel,
Here would be a fully dynamic SQL statement which only returns which minutes in the previous twenty minutes did not have any entries in your log file. What would be your limits? Two non active minutes? Three non active minutes? You could group the output again to see aggregates.

With Nbrs ( X, N ) As ( Select 1, A.* From (Values Time('00:00:00')) As A Union All Select X+1, N + 1 Minute From Nbrs Where X <= 1440)SelectN As XTIMEFrom NbrsWhereN Between Current Time - 20 Minutes And Current TimeExceptselectDistinctTime(Trunc_Timestamp(LDATE, 'MI')) As XTIMEFromRJSIMAGE.DOCLOG00where ldate >= Current timestamp - 20 Minutes

Jim> From: Joel.Stone@xxxxxxxxxx
To: midrange-l@xxxxxxxxxxxx
Subject: RE: SQL: How to create a list of MISSING numbers in a sequence
Date: Mon, 29 Dec 2014 19:27:45 +0000

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.


--
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 inbound email has been scanned for all viruses by the MessageLabs SkyScan
service.
________________________________________________________________________

______________________________________________________________________
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 ...

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.