Versions Compared

Old Version 41

changes.mady.by.user Stefan Winter

Saved on

compared with

New Version Current

changes.mady.by.user Stefan Winter

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: stats are not correct unless explicitly doing a three-way left join

...

To be executed on the database "managed_idp" on hosted.eduroam.org. The data is cumulative since start of technical setup of the hosts.


SELECT countCOUNT(distinct p.inst_id) AS active_inst, i.country AS federation 
   FROM silverbullet_user su, LEFT JOIN profile p, institution i 
   WHERE ON su.deactivationprofile_statusid = "ACTIVE" AND sup.profile_id = p.inst_id ANDLEFT JOIN institution i ON p.inst_id = i.inst_id 
   WHERE GROUP BY i.countrysu.deactivation_status = "ACTIVE" 
   ORDERGROUP BY federation 
   ORDER BY active_inst DESC;

Example output at service launch day (20 Mar 2019):

...

+-----------+---------+
| certcount | userfed |
+-----------+---------+
|        11 | PL      |
|         7 | LU      |
+-----------+---------+

Interplay of the eduroam Managed IdP system components

eduroam Managed IdP includes multiple components which need to interwork correctly for the service as a whole to work. The following external dependencies between the components exist

eduroam Managed IdP web frontend → OCSP responder

  • issues OCSP statements for each of the certificates known to the system, using a cron job. See documentation on GitHub above. Make sure the cron job is running and verify that updated statements end up in the correct directory on the OCSP responder.

eduroam Managed IdP web frontend → CAT code signing cluster

  • web frontend creates installers for Windows, macOS and iOS which are to be digitally signed. The actual signature on the files is offloaded to the existing eduroam CAT code signing cluster (machines located in SURFnet premises). Make sure HTTPS traffic from the web frontend to the signing cluster is allowed.

eduroam Managed IdP RADIUS Server → OCSP responder

  • makes request at OCSP responder during every user authentication. Make sure the HTTP communication between RADIUS server and OCSP Responder is possible.

Interplay of the eduroam Managed IdP system components

eduroam Managed IdP includes multiple components which need to interwork correctly for the service as a whole to work. The following external dependencies between the components exist

eduroam Managed IdP web frontend → OCSP responder

  • issues OCSP statements for each of the certificates known to the system, using a cron job. See documentation on GitHub above. Make sure the cron job is running and verify that updated statements end up in the correct directory on the OCSP responder.

eduroam Managed IdP web frontend → CAT code signing cluster

  • web frontend creates installers for Windows, macOS and iOS which are to be digitally signed. The actual signature on the files is offloaded to the existing eduroam CAT code signing cluster (machines located in SURFnet premises). Make sure HTTPS traffic from the web frontend to the signing cluster is allowed.

eduroam Managed IdP RADIUS Server → OCSP responder

Authentication count
To be executed on the database "eduroam" on auth-1.hosted.eduroam.org and auth-2.hosted.eduroam.org (each server maintains its own counters). These statistics can't be accumulative because data is deleted after 6 months. So, instead, the queries below return the counters for the last 24h. They should be run on exactly the same time of day every day to maintain full statistics coverage.
Successful authentications
SELECT COUNT(*) AS authcount, SUBSTR(username,LOCATE('@',username)+1) AS
realm FROM eduroamauth WHERE reply = "Access-Accept" AND authdate >=
TIMESTAMPADD(DAY, -1, NOW()) GROUP BY realm ORDER BY authcount DESC;
Example output at day after service launch:
+-----------+-----------------------------+
| authcount | realm                       |
+-----------+-----------------------------+
|       288 | 9-9.lu.hosted.eduroam.org   |
|        54 | 16-15.no.hosted.eduroam.org |
|        40 | 10-10.lu.hosted.eduroam.org |
|         6 | 11-19.lu.hosted.eduroam.org |
|         2 | 23-21.ua.hosted.eduroam.org |
|         1 | 20-20.jp.hosted.eduroam.org |
+-----------+-----------------------------+
Failed authentications
SELECT COUNT(*) AS authcount, SUBSTR(username,LOCATE('@',username)+1) AS
realm FROM eduroamauth WHERE reply = "Access-Reject" AND authdate >=
TIMESTAMPADD(DAY, -1, NOW()) GROUP BY realm ORDER BY authcount DESC;
Example output at day after service launch:
+-----------+-----------------------------+
| authcount | realm                       |
+-----------+-----------------------------+
|        25 | 16-15.no.hosted.eduroam.org |
+-----------+-----------------------------+
Interplay of the eduroam Managed IdP system components
eduroam Managed IdP includes multiple components which need to interwork correctly for the service as a whole to work. The following external dependencies between the components exist
eduroam Managed IdP web frontend → OCSP responder
  • issues OCSP statements for each of the certificates known to the system, using a cron job. See documentation on GitHub above. Make sure the cron job is running and verify that updated statements end up in the correct directory on the OCSP responder.
eduroam Managed IdP web frontend → CAT code signing cluster
  • web frontend creates installers for Windows, macOS and iOS which are to be digitally signed. The actual signature on the files is offloaded to the existing eduroam CAT code signing cluster (machines located in SURFnet premises). Make sure HTTPS traffic from the web frontend to the signing cluster is allowed.
eduroam Managed IdP RADIUS Server → OCSP responder
  • makes request at OCSP responder during every user authentication. Make sure the HTTP communication between RADIUS server and OCSP Responder is possible.
Interplay of the eduroam Managed IdP system components
eduroam Managed IdP includes multiple components which need to interwork correctly for the service as a whole to work. The following external dependencies between the components exist
eduroam Managed IdP web frontend → OCSP responder
  • issues OCSP statements for each of the certificates known to the system, using a cron job. See documentation on GitHub above. Make sure the cron job is running and verify that updated statements end up in the correct directory on the OCSP responder.
eduroam Managed IdP web frontend → CAT code signing cluster
  • web frontend creates installers for Windows, macOS and iOS which are to be digitally signed. The actual signature on the files is offloaded to the existing eduroam CAT code signing cluster (machines located in SURFnet premises). Make sure HTTPS traffic from the web frontend to the signing cluster is allowed.
eduroam Managed IdP RADIUS Server → OCSP responder
  • makes request at OCSP responder during every user authentication. Make sure the HTTP communication between RADIUS server and OCSP Responder is possible.SELECT COUNT(*) AS authcount, SUBSTR(username,LOCATE('@',username)+1) AS realm FROM eduroamauth WHERE reply = "Access-Accept" AND authdate >= TIMESTAMPADD(DAY, -1, NOW()) GROUP BY realm ORDER BY authcount DESC;+-----------+-----------------------------+ | authcount | realm | +-----------+-----------------------------+ | 288 | 9-9.lu.hosted.eduroam.org | | 54 | 16-15.no.hosted.eduroam.org | | 40 | 10-10.lu.hosted.eduroam.org | | 6 | 11-19.lu.hosted.eduroam.org | | 2 | 23-21.ua.hosted.eduroam.org | | 1 | 20-20.jp.hosted.eduroam.org | +-----------+-----------------------------+makes request at OCSP responder during every user authentication. Make sure the HTTP communication between RADIUS server and OCSP Responder is possible.