You may built a list containing all hours, minutes, seconds beginning with a
specific time up to the current time or any other time and use an exception
join for joining it with your log file. The result will be all times without
any message in your log file.
Something like this
With TimeList (CTETime)
as ( Values(Time('00.00.00')) --> set your starting time here
Union All
Select CTETime + 1 Minute from TimeList
Where CTETime < Current_Time),
Log as (Select Distinct Time(Digits(Dec(Hour(Ldate), 2, 0)) concat '.'
concat Digits(Dec(Minute(LDate), 2, 0)) concat '.00') LogTime
from from rjsimage/doclog00))
Select * from TimeList exception join Log on CTETime = LogTime;
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
-----Ursprüngliche Nachricht-----
Von: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von
Stone, Joel
Gesendet: Monday, 29.12 2014 18:46
An: 'Midrange Systems Technical Discussion'
Betreff: SQL: How to create a list of MISSING numbers in a sequence
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.
midrange.com
