Connectivity log

  • Thread starter Thread starter fred099
  • Start date Start date
F

fred099

Hi, i have a problem that i think someone will be able to help me with,
i have a log file showing my router connectivity. the fields are
Date, Time, cct condition
the cct condition shows the result of a ping, this can either give the result
UP or Down
i need the time between the last down and the first up.

the table looks like this:

time date CCT condition
0800 28092006 UP
0801 28092006 UP
0802 28092006 DOWN
0802 28092006 DOWN
0809 28092006 UP
0809 28092006 UP

I need to know how long the cct has been down. The time the cct is down is
the time between the last down and the first up. I dont know how to get the
time difference between the two records ignoring all the ups prior and all
the downs after. if anyone has any ideas i would be greatful.
 
Hi,



Have the date and the time in a single field, else, the syntax become an
abomination.


I suggest you rank the date_time a first time, will all records, and a
second time, with only the records having the UP status. Subtract these two
ranks, group on the subtraction, and keep the min and max date_time for each
group. At that point, you should have:

r1 r2 g
max- min (for each g value)
0800 28092006 UP 1 1 0
0801 28092006 UP 2 2 0 1
0802 28092006 DOWN 3 - -
0803 28092006 DOWN 4 - -
0809 28092006 UP 5 3 2
0809 28092006 UP 5 3 2
0813 28092006 UP 7 5 2 4 ( =13-9)


where r1 is the first rank, r2 the second one I mentioned, and g, the group
(difference between the two ranks). Note that we really need the max-min of
each group, not necessary the max and the min, individually.

In another query, SUM the max-min values over all the groups to know the
"sure" UP time. Here, that is 5 minutes. The DOWN time can be then as large
as MAX-MIN (of the whole data, here 13 minutes) less the sure UP time. So
pessimistic DOWN time = 8 (from 08:01:30 to 08:09:30; as example ).

To get the OPTIMISTIC down time, compute a third rank, r3, but considering
only the DOWN status, and group by on r1-r3, computing Max-Min for each
group.


r1 r3 g
max- min
0800 28092006 UP 1 -
0801 28092006 UP 2 -
0802 28092006 DOWN 3 1 2
0803 28092006 DOWN 4 2 2 1
0809 28092006 UP 5 -
0809 28092006 UP 5 -
0813 28092006 UP 7 -


Then, in another query, sum those max-min. Here, that makes 1 minute (ex:
down from 08:02:30 to 08:03:30).


Sure, if the time is known at the second, and if there are a lop of pings,
the pessimistic and the optimistic values will be closer.



I don't supply any SQL formulation, since basically, the problem is now
divided in smaller steps (such as computing a rank) and I don't know if you
need further help, or not, in that matter. Or if you will add precision to
your original question, now that you see we can compute the DOWN time in, at
least, two different ways (with different, but logical, results).



Hoping it may help,
Vanderghast, Access MVP
 
fred099 said:
i have a log file showing my router connectivity. the fields are
Date, Time, cct condition
the cct condition shows the result of a ping, this can either give the result
UP or Down
i need the time between the last down and the first up.

the table looks like this:
time date CCT condition
0800 28092006 UP
0801 28092006 UP
0802 28092006 DOWN
0802 28092006 DOWN
0809 28092006 UP
0809 28092006 UP

I need to know how long the cct has been down. The time the cct is down is
the time between the last down and the first up. I dont know how to get the
time difference between the two records ignoring all the ups prior and all
the downs after. if anyone has any ideas i would be greatful.
Click to expand...

OK, you need to break this down into several stages.

First, create a query that converts the 'time' and 'date' fields into
a single record (I agree with Michel here, it's an abomination and
fixing it first will save complications later!). Call it
qryConnectionState (I've assumed the table is called
'connection_state' - you should insert your table name instead):

SELECT DateValue(Right([date],4) & "/" &
Mid([date],3,2) & "/" &
Left([date],2)) +
TimeValue(Left([time],2) & ":" &
Right([time],2)) AS log_time,
cct_condition
FROM connection_state;

Now the tricky bit! You need to extract each instance where the state
changes to DOWN. Here I disagree with Michel, as I don't think you
need a ranking query to do this - you just find all the DOWNs where
there is no UP that is more than any DOWN that is less the current
DOWN. Call it qryConnectionFailures:

SELECT Q.log_time, Q.cct_condition
FROM qryConnectionState AS Q
WHERE Q.cct_condition = 'DOWN' AND NOT EXISTS
(SELECT * FROM qryConnectionState AS Q1
WHERE Q1.cct_condition = 'UP'
AND Q1.log_time < Q.log_time
AND NOT EXISTS
(SELECT * FROM qryConnectionState AS Q2
WHERE Q2.cct_condition = 'DOWN'
AND Q2.log_time > Q1.log_time
AND Q2.log_time < Q.log_time))

Finally, you can find the downtime:
SELECT Q.log_time AS went_down,
(SELECT MIN(Q1.log_time)
FROM qryConnectionState AS Q1
WHERE Q1.log_time > Q.log_time
AND cct_condition = 'UP') AS back_up,
DATEDIFF("s", Q.log_time, (SELECT MIN(Q1.log_time)
FROM qryConnectionState AS Q1
WHERE Q1.log_time > Q.log_time
AND cct_condition = 'UP')) AS down_time
FROM qryConnectionFailures AS Q

Which from the data sample you gave, gives you this:
went_down back_up down_time
=================== =================== =========
28/09/2006 08:03:00 28/09/2006 08:08:00 300
=================== =================== =========
 
Hi,


Ranking is not necessary, but it is an O2 process. Your sub-queries seems to
be O3, at first glance, and with two NOT EXISTS constructions, poorly
optimized construction under Jet. Other possible solutions, such as based on
JOINs, seem to also be O3 (or 3 times the same table joined to itself), and
that is why I proposed using rankings. Sure, that depends of the number of
records. With less than 1000, maybe walking through a recordset,
pre-ordered, is the easiest and fastest way, after all.


Vanderghast, Access MVP


Neil Sunderland said:
fred099 said:
i have a log file showing my router connectivity. the fields are
Date, Time, cct condition
the cct condition shows the result of a ping, this can either give the
result
UP or Down
i need the time between the last down and the first up.

the table looks like this:
time date CCT condition
0800 28092006 UP
0801 28092006 UP
0802 28092006 DOWN
0802 28092006 DOWN
0809 28092006 UP
0809 28092006 UP

I need to know how long the cct has been down. The time the cct is down is
the time between the last down and the first up. I dont know how to get
the
time difference between the two records ignoring all the ups prior and all
the downs after. if anyone has any ideas i would be greatful.
Click to expand...

OK, you need to break this down into several stages.

First, create a query that converts the 'time' and 'date' fields into
a single record (I agree with Michel here, it's an abomination and
fixing it first will save complications later!). Call it
qryConnectionState (I've assumed the table is called
'connection_state' - you should insert your table name instead):

SELECT DateValue(Right([date],4) & "/" &
Mid([date],3,2) & "/" &
Left([date],2)) +
TimeValue(Left([time],2) & ":" &
Right([time],2)) AS log_time,
cct_condition
FROM connection_state;

Now the tricky bit! You need to extract each instance where the state
changes to DOWN. Here I disagree with Michel, as I don't think you
need a ranking query to do this - you just find all the DOWNs where
there is no UP that is more than any DOWN that is less the current
DOWN. Call it qryConnectionFailures:

SELECT Q.log_time, Q.cct_condition
FROM qryConnectionState AS Q
WHERE Q.cct_condition = 'DOWN' AND NOT EXISTS
(SELECT * FROM qryConnectionState AS Q1
WHERE Q1.cct_condition = 'UP'
AND Q1.log_time < Q.log_time
AND NOT EXISTS
(SELECT * FROM qryConnectionState AS Q2
WHERE Q2.cct_condition = 'DOWN'
AND Q2.log_time > Q1.log_time
AND Q2.log_time < Q.log_time))

Finally, you can find the downtime:
SELECT Q.log_time AS went_down,
(SELECT MIN(Q1.log_time)
FROM qryConnectionState AS Q1
WHERE Q1.log_time > Q.log_time
AND cct_condition = 'UP') AS back_up,
DATEDIFF("s", Q.log_time, (SELECT MIN(Q1.log_time)
FROM qryConnectionState AS Q1
WHERE Q1.log_time > Q.log_time
AND cct_condition = 'UP')) AS down_time
FROM qryConnectionFailures AS Q

Which from the data sample you gave, gives you this:
went_down back_up down_time
=================== =================== =========
28/09/2006 08:03:00 28/09/2006 08:08:00 300
=================== =================== =========

--
Neil Sunderland
Braunton, Devon

Please observe the Reply-To address
Click to expand...
 
Michel said:
Ranking is not necessary, but it is an O2 process. Your sub-queries seems to
be O3, at first glance, and with two NOT EXISTS constructions, poorly
optimized construction under Jet. Other possible solutions, such as based on
JOINs, seem to also be O3 (or 3 times the same table joined to itself), and
that is why I proposed using rankings. Sure, that depends of the number of
records. With less than 1000, maybe walking through a recordset,
pre-ordered, is the easiest and fastest way, after all.
Click to expand...

Can't argue with any of that! In my defence, I tend not worry about
the performance of my queries...

When I'm writing queries in anger I'm using a cluster of SQL Server
boxes with tables containing anything up to two or three million rows
which are (mostly) cached in RAM, and they tend to go like wossname
off a shovel.

I honestly can't remember the last time I've written anything that's
taken - after some tweaking, possibly - more than two seconds. :)
 
Thanks for your solutions, i have joined the two fields date and time
together called "log_time" as suggested and tried the query , however the
supplied SQL crashes my MS Access everytime i try to run it, i suppose it
might be too complex. . this is a shame as it gives the output i require, and
as I am a complete novice where SQL is concerned i cannot work out where it
is going wrong.
I typed the SQL as listed so any more help would be greatly appreciated

again thanks for your work
 
Back
Top