Re: [1'b68'1 astrad v8] clear stat of telephones with problems on all servers

[Task-By Emin Gabrielyan <emin.gabrielyan@switzernet.com>]

This is fantastically clear!

Bravo!

Emin

Emin Gabrielyan

Le Jun 16, 2011 à 19:22, Oussama Hammami <oussama.hammami@switzernet.com> a écrit :

Salut,

Ci-dessous les statistiques MySQL (DB3):

mysql> CALL  StatReg('91.121.142.9','2011-06-16 00:00:00',NOW(),30);
+---------------------+---------------------+-----------+-----------+
| START               | STOP                | COUNT = 1 | COUNT > 1 |
+---------------------+---------------------+-----------+-----------+
| 2011-06-16 00:00:00 | 2011-06-16 00:30:00 |         3 |        27 |
| 2011-06-16 00:30:00 | 2011-06-16 01:00:00 |         2 |        26 |
| 2011-06-16 01:00:00 | 2011-06-16 01:30:00 |         4 |        26 |
| 2011-06-16 01:30:00 | 2011-06-16 02:00:00 |         1 |        27 |
| 2011-06-16 02:00:00 | 2011-06-16 02:30:00 |         4 |        26 |
| 2011-06-16 02:30:00 | 2011-06-16 03:00:00 |         1 |        26 |
| 2011-06-16 03:00:00 | 2011-06-16 03:30:00 |         2 |        27 |
| 2011-06-16 03:30:00 | 2011-06-16 04:00:00 |         1 |        26 |
| 2011-06-16 04:00:00 | 2011-06-16 04:30:00 |         4 |        26 |
| 2011-06-16 04:30:00 | 2011-06-16 05:00:00 |         1 |        26 |
| 2011-06-16 05:00:00 | 2011-06-16 05:30:00 |         4 |        26 |
| 2011-06-16 05:30:00 | 2011-06-16 06:00:00 |         2 |        26 |
| 2011-06-16 06:00:00 | 2011-06-16 06:30:00 |         4 |        25 |
| 2011-06-16 06:30:00 | 2011-06-16 07:00:00 |         3 |        25 |
| 2011-06-16 07:00:00 | 2011-06-16 07:30:00 |         5 |        26 |
| 2011-06-16 07:30:00 | 2011-06-16 08:00:00 |         1 |        26 |
| 2011-06-16 08:00:00 | 2011-06-16 08:30:00 |         5 |        26 |
| 2011-06-16 08:30:00 | 2011-06-16 09:00:00 |         1 |        27 |
| 2011-06-16 09:00:00 | 2011-06-16 09:30:00 |         3 |        26 |
| 2011-06-16 09:30:00 | 2011-06-16 10:00:00 |         2 |        26 |
| 2011-06-16 10:00:00 | 2011-06-16 10:30:00 |         4 |        26 |
| 2011-06-16 10:30:00 | 2011-06-16 11:00:00 |         0 |        27 |
| 2011-06-16 11:00:00 | 2011-06-16 11:30:00 |         5 |        26 |
| 2011-06-16 11:30:00 | 2011-06-16 12:00:00 |         2 |        27 |
| 2011-06-16 12:00:00 | 2011-06-16 12:30:00 |         2 |        27 |
| 2011-06-16 12:30:00 | 2011-06-16 13:00:00 |         2 |        27 |
| 2011-06-16 13:00:00 | 2011-06-16 13:30:00 |         4 |        26 |
| 2011-06-16 13:30:00 | 2011-06-16 14:00:00 |         1 |        26 |
| 2011-06-16 14:00:00 | 2011-06-16 14:30:00 |         2 |        27 |
| 2011-06-16 14:30:00 | 2011-06-16 15:00:00 |         1 |        26 |
| 2011-06-16 15:00:00 | 2011-06-16 15:30:00 |         2 |        26 |
| 2011-06-16 15:30:00 | 2011-06-16 16:00:00 |         2 |        26 |
| 2011-06-16 16:00:00 | 2011-06-16 16:30:00 |         0 |         0 |
| 2011-06-16 16:30:00 | 2011-06-16 17:00:00 |         2 |         0 |
| 2011-06-16 17:00:00 | 2011-06-16 17:30:00 |         2 |         1 |
| 2011-06-16 17:30:00 | 2011-06-16 18:00:00 |         1 |         1 |
| 2011-06-16 18:00:00 | 2011-06-16 18:30:00 |         3 |         0 |
| 2011-06-16 18:30:00 | 2011-06-16 19:00:00 |         5 |         0 |
+---------------------+---------------------+-----------+-----------+
38 rows in set (1.34 sec)

Query OK, 38 rows affected, 2 warnings (1.34 sec)

mysql> CALL  StatReg('91.121.147.45','2011-06-16 00:00:00',NOW(),30);
+---------------------+---------------------+-----------+-----------+
| START               | STOP                | COUNT = 1 | COUNT > 1 |
+---------------------+---------------------+-----------+-----------+
| 2011-06-16 00:00:00 | 2011-06-16 00:30:00 |         6 |         6 |
| 2011-06-16 00:30:00 | 2011-06-16 01:00:00 |         6 |         6 |
| 2011-06-16 01:00:00 | 2011-06-16 01:30:00 |         6 |         6 |
| 2011-06-16 01:30:00 | 2011-06-16 02:00:00 |         6 |         6 |
| 2011-06-16 02:00:00 | 2011-06-16 02:30:00 |         6 |         6 |
| 2011-06-16 02:30:00 | 2011-06-16 03:00:00 |         5 |         7 |
| 2011-06-16 03:00:00 | 2011-06-16 03:30:00 |         6 |         6 |
| 2011-06-16 03:30:00 | 2011-06-16 04:00:00 |         5 |         7 |
| 2011-06-16 04:00:00 | 2011-06-16 04:30:00 |         6 |         6 |
| 2011-06-16 04:30:00 | 2011-06-16 05:00:00 |         6 |         6 |
| 2011-06-16 05:00:00 | 2011-06-16 05:30:00 |         6 |         6 |
| 2011-06-16 05:30:00 | 2011-06-16 06:00:00 |         6 |         6 |
| 2011-06-16 06:00:00 | 2011-06-16 06:30:00 |         6 |         6 |
| 2011-06-16 06:30:00 | 2011-06-16 07:00:00 |         6 |         6 |
| 2011-06-16 07:00:00 | 2011-06-16 07:30:00 |         5 |         7 |
| 2011-06-16 07:30:00 | 2011-06-16 08:00:00 |         6 |         6 |
| 2011-06-16 08:00:00 | 2011-06-16 08:30:00 |         5 |         7 |
| 2011-06-16 08:30:00 | 2011-06-16 09:00:00 |         5 |         7 |
| 2011-06-16 09:00:00 | 2011-06-16 09:30:00 |         4 |         7 |
| 2011-06-16 09:30:00 | 2011-06-16 10:00:00 |         6 |         6 |
| 2011-06-16 10:00:00 | 2011-06-16 10:30:00 |         6 |         6 |
| 2011-06-16 10:30:00 | 2011-06-16 11:00:00 |         7 |         6 |
| 2011-06-16 11:00:00 | 2011-06-16 11:30:00 |         6 |         5 |
| 2011-06-16 11:30:00 | 2011-06-16 12:00:00 |         6 |         6 |
| 2011-06-16 12:00:00 | 2011-06-16 12:30:00 |         5 |         6 |
| 2011-06-16 12:30:00 | 2011-06-16 13:00:00 |         5 |         7 |
| 2011-06-16 13:00:00 | 2011-06-16 13:30:00 |         5 |         6 |
| 2011-06-16 13:30:00 | 2011-06-16 14:00:00 |         7 |         6 |
| 2011-06-16 14:00:00 | 2011-06-16 14:30:00 |         6 |         6 |
| 2011-06-16 14:30:00 | 2011-06-16 15:00:00 |         6 |         6 |
| 2011-06-16 15:00:00 | 2011-06-16 15:30:00 |         6 |         6 |
| 2011-06-16 15:30:00 | 2011-06-16 16:00:00 |         8 |         6 |
| 2011-06-16 16:00:00 | 2011-06-16 16:30:00 |         8 |         2 |
| 2011-06-16 16:30:00 | 2011-06-16 17:00:00 |         1 |         0 |
| 2011-06-16 17:00:00 | 2011-06-16 17:30:00 |         1 |         0 |
| 2011-06-16 17:30:00 | 2011-06-16 18:00:00 |         2 |         0 |
| 2011-06-16 18:00:00 | 2011-06-16 18:30:00 |         3 |         0 |
| 2011-06-16 18:30:00 | 2011-06-16 19:00:00 |         3 |         0 |
+---------------------+---------------------+-----------+-----------+
38 rows in set (1.28 sec)

Query OK, 38 rows affected, 2 warnings (1.28 sec)

-- Nombre de client par serveur
mysql> select domain,count(*) from location2  group by domain;
+----------------+----------+
| domain         | count(*) |
+----------------+----------+
| 91.121.142.9   |      185 | -> astrad6
| 91.121.147.45  |      115 | -> astrad7
| 91.121.143.56  |      265 |
| 91.121.16.79   |      160 |
| 91.121.178.108 |      182 |
+----------------+----------+
5 rows in set (0.00 sec)

      

On 2011-06-15 10:52, Task-By Emin Gabrielyan wrote:

Assume that we have the following location history table which is growing as a result of triggers of location table:
 Start
 Stop
 Domain
 Account
 IP
 Port
 Count

Assume we have a period from A to B.

For each account
  Count records
    Where
      Start < B and Stop > A (meaning any intersection)
  If count = 1
    Then the account does not loose its port
    Else the telephone looses the connection

A php script or an excel file must display hour by hour the percentage of accounts loosing their port.

Emin Gabrielyan

Le Jun 14, 2011 à 1:05, Emin Gabrielyan <emin.gabrielyan@switzernet.com> a écrit :

  

Need a clear quantity of telephones with lost ports on all servers. Write a script that can compute it automatically.

Envoyé de mon iPad