PacketFence - BTS - PacketFence |
| View Issue Details |
|
| ID | Project | Category | View Status | Date Submitted | Last Update |
| 0001685 | PacketFence | web admin | public | 2013-08-14 06:17 | 2013-12-17 09:32 |
|
| Reporter | muhlig | |
| Assigned To | francis | |
| Priority | normal | Severity | minor | Reproducibility | always |
| Status | resolved | Resolution | fixed | |
| Platform | | OS | | OS Version | |
| Product Version | 4.0.4 | |
| Target Version | | Fixed in Version | 4.1.1 | |
| fixed in git revision | 66139bb516f17c579ae06aadb0a4b445e90aa7e3 |
| fixed in mtn revision | |
|
| Summary | 0001685: connections number wrong on Connections Types report page |
| Description | After quite not heavy use of PacketFence Wired 802.1x connections number shown on web page is 12728 (Wired MAC Auth connections number is also too high - 1166). However, database queries show accordingly 158 and 17 connections which are real numbers (see below).
So looks like connections number is wrong on Connections Types report page.
|
| Steps To Reproduce | |
| Additional Information | mysql> select count(*) from locationlog where connection_type = "Ethernet-EAP";
+----------+
| count(*) |
+----------+
| 158 |
+----------+
1 row in set (0.00 sec)
mysql> select count(*) from locationlog where connection_type = "WIRED_MAC_AUTH";
+----------+
| count(*) |
+----------+
| 17 |
+----------+
1 row in set (0.00 sec)
mysql>
|
| Tags | No tags attached. |
| Relationships | |
| Attached Files |  PF_connection_types.png (27,105) 2013-08-14 09:06
https://www.packetfence.org/bugs/file_download.php?file_id=185&type=bug
|
|
| Issue History |
| Date Modified | Username | Field | Change |
| 2013-08-14 06:17 | muhlig | New Issue | |
| 2013-08-14 08:35 | francis | Note Added: 0003406 | |
| 2013-08-14 09:06 | muhlig | Note Added: 0003407 | |
| 2013-08-14 09:06 | muhlig | File Added: PF_connection_types.png | |
| 2013-08-14 09:08 | francis | Note Added: 0003408 | |
| 2013-08-14 09:09 | francis | Note Deleted: 0003408 | |
| 2013-08-14 09:44 | francis | Note Added: 0003409 | |
| 2013-12-17 09:30 | francis | fixed in git revision | => 66139bb516f17c579ae06aadb0a4b445e90aa7e3 |
| 2013-12-17 09:30 | francis | Note Added: 0003481 | |
| 2013-12-17 09:30 | francis | Status | new => resolved |
| 2013-12-17 09:30 | francis | Fixed in Version | => devel |
| 2013-12-17 09:30 | francis | Resolution | open => fixed |
| 2013-12-17 09:30 | francis | Assigned To | => francis |
| 2013-12-17 09:32 | francis | Fixed in Version | devel => 4.1.1 |
|
Notes |
|
|
|
The SQL queries to extract the number of wired and wireless connections for the past week look like this :
SELECT count(*) AS nb FROM (
SELECT mac, DATE_FORMAT(start_time,"%Y/%m/%d") AS start_day
FROM locationlog
WHERE start_time > '2013-08-07 00:00:00' AND start_time < '2013-08-14 23:59:59' AND connection_type NOT LIKE 'Wireless%' GROUP BY start_day, mac
) AS wired_count
SELECT count(*) AS nb FROM (
SELECT mac, DATE_FORMAT(start_time,"%Y/%m/%d") AS start_day
FROM locationlog
WHERE start_time > '2013-08-07 00:00:00' AND start_time < '2013-08-14 23:59:59' AND connection_type LIKE 'Wireless%' GROUP BY start_day, mac
) AS wireless_count |
|
|
|
(0003407)
|
|
muhlig
|
|
2013-08-14 09:06
|
|
So we have 5 and 0 (see below). Why the page displays thousands wired connections then (see attached file)?
mysql> use pf
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> SELECT count(*) AS nb FROM (
-> SELECT mac, DATE_FORMAT(start_time,"%Y/%m/%d") AS start_day
-> FROM locationlog
-> WHERE start_time > '2013-08-07 00:00:00' AND start_time < '2013-08-14 23:59:59' AND connection_type NOT LIKE 'Wireless%' GROUP BY start_day, mac
-> ) AS wired_count;
+----+
| nb |
+----+
| 5 |
+----+
1 row in set (0.00 sec)
mysql> SELECT count(*) AS nb FROM (
-> SELECT mac, DATE_FORMAT(start_time,"%Y/%m/%d") AS start_day
-> FROM locationlog
-> WHERE start_time > '2013-08-07 00:00:00' AND start_time < '2013-08-14 23:59:59' AND connection_type LIKE 'Wireless%' GROUP BY start_day, mac
-> ) AS wireless_count ;
+----+
| nb |
+----+
| 0 |
+----+
1 row in set (0.00 sec)
mysql> |
|
|
|
|
|
The queries I posted were for the dashboard. I'll have a look at the queries for the connection types report. |
|
|
|
|
Fixed by counting distinct MAC addresses.
BEFORE:
mysql> SELECT connection_type, COUNT(*) AS connections,
-> ROUND(COUNT(*)/
-> (SELECT COUNT(*)
-> FROM locationlog
-> WHERE start_time BETWEEN '2013-01-01' AND '2013-01-31'
-> )*100,1
-> ) AS percent
-> FROM locationlog
-> WHERE start_time BETWEEN '2013-01-01' AND '2013-01-31'
-> GROUP BY connection_type
-> ;
+-----------------------+-------------+---------+
| connection_type | connections | percent |
+-----------------------+-------------+---------+
| Ethernet-NoEAP | 1215 | 1.6 |
| Inline | 35377 | 47.7 |
| Wireless-802.11-EAP | 5851 | 7.9 |
| Wireless-802.11-NoEAP | 31670 | 42.7 |
+-----------------------+-------------+---------+
AFTER:
mysql> SELECT connection_type, COUNT(DISTINCT mac) AS connections,
-> ROUND(COUNT(DISTINCT mac)/
-> (SELECT COUNT(DISTINCT mac)
-> FROM locationlog
-> WHERE start_time BETWEEN '2013-01-01' AND '2013-01-31'
-> )*100,1
-> ) AS percent
-> FROM locationlog
-> WHERE start_time BETWEEN '2013-01-01' AND '2013-01-31'
-> GROUP BY connection_type
-> ;
+-----------------------+-------------+---------+
| connection_type | connections | percent |
+-----------------------+-------------+---------+
| Ethernet-NoEAP | 350 | 6.8 |
| Inline | 3155 | 61.2 |
| Wireless-802.11-EAP | 737 | 14.3 |
| Wireless-802.11-NoEAP | 3866 | 75.0 |
+-----------------------+-------------+---------+
|
|