PacketFence
Bug Tracking System
Main | My View | View Issues | Change Log | Roadmap
  

View Issue Details [ Jump to Notes ] [ Issue History ] [ Print ]
IDProjectCategoryView StatusDate SubmittedLast Update
0001685PacketFenceweb adminpublic2013-08-14 06:172013-12-17 09:32
Reportermuhlig 
Assigned Tofrancis 
PrioritynormalSeverityminorReproducibilityalways
StatusresolvedResolutionfixed 
PlatformOSOS Version
Product Version4.0.4 
Target VersionFixed in Version4.1.1 
Summary0001685: connections number wrong on Connections Types report page
DescriptionAfter 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.

Additional Informationmysql> 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>
TagsNo tags attached.
fixed in git revision66139bb516f17c579ae06aadb0a4b445e90aa7e3
fixed in mtn revision
Attached Filespng file icon PF_connection_types.png [^] (27,105 bytes) 2013-08-14 09:06

- Relationships

-  Notes
(0003406)
francis (administrator)
2013-08-14 08:35

 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 (reporter)
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>
(0003409)
francis (administrator)
2013-08-14 09:44

 The queries I posted were for the dashboard. I'll have a look at the queries for the connection types report.
(0003481)
francis (administrator)
2013-12-17 09:30

 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 |
+-----------------------+-------------+---------+

- 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

Copyright © 2000 - 2012 MantisBT Group
Powered by Mantis Bugtracker