Hello All,
I have some data in a MySQL table and I need to format it better. I have some
thousands of data from my users connections like this:
+----+---------------------+-----------------+------------+--------------+---------------+----------------+
| id | unixtime | ip | porta_snmp | porta_switch | ip_cliente
| mac_cliente |
+----+---------------------+-----------------+------------+--------------+---------------+----------------+
| 1 | 2003-01-07 12:23:24 | AAA.BBB.CCC.DDD | 1604 | 1 |
192.168.1.248 | 0x0090f50e1a5c |
| 2 | 2003-01-07 12:24:11 | AAA.BBB.CCC.DDD | 1604 | 1 |
192.168.1.248 | 0x0090f50e1a5c |
| 3 | 2003-01-07 12:25:34 | AAA.BBB.CCC.DDD | 1604 | 1 |
192.168.1.248 | 0x0090f50e1a5c |
| 4 | 2003-01-07 12:21:19 | AAA.BBB.CCC.DDD | 1604 | 1 |
192.168.1.248 | 0x0090f50e1a5c |
| 5 | 2003-01-07 12:32:01 | AAA.BBB.CCC.DDD | 1604 | 1 |
192.168.1.248 | 0x0090f50e1a5c |
+----+---------------------+-----------------+------------+--------------+---------------+----------------+
I want to generate a report with the total time connected, but the most important is
to get the connection periods.
Example:
Report 1)
User: 0x0090f50e1a5c
IP: AAA.BBB.CCC.DDD
Day: 01/0
Start: 2003-01-07 12:23:24
Stop: 2003-01-07 12:25:34
Start: 2003-01-07 12:21:19
Stop: 2003-01-07 12:32:01
Total: 00:12:52
Report 2)
User IP Start Stop
Total Day
0x0090f50e1a5c AAA.BBB.CCC.DDD 2003-01-07 12:23:24 2003-01-07 12:25:34
00:02:10 01/07/2003
0x0090f50e1a5c AAA.BBB.CCC.DDD 2003-01-07 12:21:19 2003-01-07 12:32:01
00:10:42 01/07/2003
Overall
Total: 00:12:52
Any help on how I could manage this data ?
Thank's in advance,
Gustavo
