F
fred099 via AccessMonster.com
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
080028092006 UP
080128092006 UP
080228092006 DOWN
080228092006 DOWN
080928092006 UP
080928092006 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.
I Have a solution posted but i do not know how to use the attached solution.
thanks very much for the efforts previously but any more help would be
appreciated. i need to know how the query works so i can adapt it to my needs.
the solution provided appears to crash my Access Database.
The provided output is perfect as it shows the time the cct went down and the
time it came back up, also the interval between these.
So please if anyone can explain how it works and how to adapt it to my needs
i will be very greatful.
_________________________________________________-----------------------
______________
OK, you need to break this down into several stages.
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
=================== =================== =========
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
080028092006 UP
080128092006 UP
080228092006 DOWN
080228092006 DOWN
080928092006 UP
080928092006 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.
I Have a solution posted but i do not know how to use the attached solution.
thanks very much for the efforts previously but any more help would be
appreciated. i need to know how the query works so i can adapt it to my needs.
the solution provided appears to crash my Access Database.
The provided output is perfect as it shows the time the cct went down and the
time it came back up, also the interval between these.
So please if anyone can explain how it works and how to adapt it to my needs
i will be very greatful.
_________________________________________________-----------------------
______________
OK, you need to break this down into several stages.
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
=================== =================== =========