RE: SQL: How to create a list of MISSING numbers in a sequence -- MIDRANGE-L

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.