The Guthrie NODEDETAILS table contains a list of all the nodes involved in PingER analysis done from SLAC. It is stored in the SLACPROD Oracle instance IEPM.NODEDETAILS under the IEPM user/schema.
To access the database the URL is https://oraweb.slac.stanford.edu/apex/slacprod/f?p=123:LOGIN:2700279091073:::::.
After successfully logging in you will get a web page of the form:
You search for information about the node/country/region etc. that you are interested by entering information into the Search box. E.g. entering gzhu gives:
To edit an existing node click on the pen and paper image in the left hand column.
After editing click on Apply Changes. Do NOT use the Delete tab unless you know what you are doing. To Disable a node enter a D in the Projecttype box. If you are just tesing or have messed up the editing just hit Cancel.
To create a new node tap on Create in the top level window:
The Create window appears as:
Column specifications
Name | Null? | Data Type | Use |
|---|---|---|---|
NODENAME | NOT NULL | VARCHAR2(100) | DNS host name |
IPADDRESS | VARCHAR2(15) | IPv4 address | |
SITENAME | VARCHAR2(100) | Domain name of the node | |
NICKNAME | VARCHAR2(35) | Abstraction of the hostname with the TLD first and the hostname last1 | |
FULLNAME | VARCHAR2(100) | Human-friendly description of the node/site/Institute | |
LOCATION | VARCHAR2(100) | City and/or State/Province/Region for node 2 | |
COUNTRY | VARCHAR2(100) | Country for node | |
CONTINENT | VARCHAR2(100) | Continent or region where node is thought to be located 3 | |
LATANDLONG | VARCHAR2(25) | Latitude and longitude of node (as signed decimal values separated by a space) | |
PROJECTTYPE | VARCHAR2(10) | Flags describing how nodes are used 4 | |
PINGSERVER | VARCHAR2(100) | URL for requesting a ping from this node to another 5 | |
TRACESERVER | VARCHAR2(100) | URL for requesting a traceroute from this node to another 5 | |
DATASERVER | VARCHAR2(100) | URL for retrieving PingER data from this node 5 | |
URL | VARCHAR2(100) | URL for the home page for the institution running the node | |
GMT | VARCHAR2(10) | Node's time offset from GMT, not used | |
COMMENTS | VARCHAR2(4000) | Comments and notes on when and how the node's record was last updated 6 | |
APP_USER | VARCHAR2(20) | Windows user name of the last user to edit the node's record through the UI. THis is entered automatically. | |
CONTACTS | VARCHAR2(100) | Name and email address(es) of the node's maintainer(s) | |
PING_SIZE | NUMBER | Size of pings to be sent to the node - only controls SLAC's PingER install. Usually left blank. |
1 This field should be of the form: TLD.Rest of site domain.hostname. For non-US hosts not using their country code TLD, use the country code TLD at the start of the identifier and include the actual TLD in the site domain string. Examples:
- EDU.SLAC.STANFORD.PINGER - node at SLAC,stanford.edu, fully qualified hostname pinger.slac.stanford.edu
- CH.CERN.WANMONINST1 - second node at CERN, fully qualified hostname wanmoninst1.cern.ch
- AO.UCAN.EDU.N1 - first node at the Catholic University of Angola, fully qualified hostname www.ucan.edu
- For IPv6 host we insert IPV6 in front of the hostname:
pship02.csc.fi =>NICKNAME=> FI.CSC.IPV6.PSHIP02
- If it is a NET or ORG etc. add the TLD country code at the start:
ps-esu-lt.perfsonar.kanren.net=>NICKNAME=>US.NET.KANREN.PERFSONAR.IPV6.PS-ESU-LT
2 Location information is either provided by the site or is based off a geographic IP database like GeoIPTool.
3 Some continents are broken up into sub-regions. A list of countries and continents can be seen at http://www.slac.stanford.edu/comp/net/mon/countries.tsv.
4 The various single character flags are described at: http://www-iepm.slac.stanford.edu/pinger/slaconly/nodedetails-readme.
5 Unless this is a monitoring node then enter NOT-SET. These URLs should point to the complete URL for the script at the site, including the trailing '?' used to start the list of parameters to the script. An example for a traceroute server is: http://www.slac.stanford.edu/cgi-bin/traceroute.pl? and for pingserver is http://www.slac.stanford.edu/cgi-bin/traceroute.pl?function=ping
If the script is not installed at the site or the service is not available (i.e. the site is not a PingER monitoring host), set the field to NOT-SET.
6 The usual format of the comments is: <Raeson>, <action> by <username> <mm/dd/yy>. Followed by an end of line (carriage return etc.)
User interface
A user interface for the table was created in Oracle's HTML DB / Application Express and can be accessed at https://oraweb.slac.stanford.edu/apex/slacprod/f?p=123:1. Authorized users are authenticated by their SLAC Windows credentials.
The UI allows for the creation, search, modification, and deletion of nodes. As a rule, nodes should not be deleted from the database for referential integrity reasons -- even though there is only one table in the database, various processes use this table in concert with the flat files that contain the raw and analyzed PingER results.
How to access the UI for updating is described in Updating NODEDETAILS with SQLPlus.
Updating the list of countries in the UI
Inside the UI, the country and continent (region) are set and restricted to a pop-up list of values. The pop-up list is based on a separate table called COUNTRY. New countries can be added to the list by creating new rows in the COUNTRY table with SQL INSERT statements. See Updating NODEDETAILS with SQLPlus
Example:
Arash 5/25/2023 (see Incident INC0407886) recommends: cottrell@pinger ~]$ [cottrell@pinger ~]$ setenv ORACLE_HOME /afs/slac.stanford.edu/package/oracle/f/19.3.0 [cottrell@pinger ~]$ $ORACLE_HOME/bin/sqlplus iepm@slacprod SQL*Plus: Release 19.0.0.0.0 - Production on Fri May 26 16:14:30 2023 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Enter password: #See escrow edit -c iepm iepmacct Last Successful login time: Fri May 26 2023 15:30:55 -07:00 Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.17.0.0.0 SQL> insert into country (country_id, country, continent, tld) 2 values (country_seq.nextval, 'Western Samoa', 'Oceania', 'ws'); #Don't forget semi-colon (); Good idea to edit and save this document 1 row created. SQL> commit; Commit complete. SQL> exit; Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.17.0.0.0 [cottrell@pinger ~]$ On the new host pinger-test use: [cottrell@pinger-test ~]$ setenv ORACLE_HOME /afs/slac.stanford.edu/package/oracle/f/19.3.0 [cottrell@pinger-test ~]$ $ORACLE_HOME/bin/sqlplus iepm@slacprod SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jan 21 20:20:38 2022 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Enter password: SQL> insert into country (country_id, country, continent, tld) 2 values (country_seq.nextval, 'Bermuda', 'Latin America', 'bm'); 1 row created. SQL> commit; Commit complete; SQL> exit; Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production With the Partitioning and Real Application Testing options 52cottrell@pinger:~> Or on the old host pinger use: [cottrell@pinger ~]$ setenv ORACLE_HOME /usr/oracle [cottrell@pinger ~]$ sqlplus iepm@slacprod SQL*Plus: Release 11.1.0.6.0 - Production on Fri Jan 21 13:13:48 2022 Copyright (c) 1982, 2007, Oracle. All rights reserved. Enter password: [cottrell@pinger ~]$ echo $ORACLE_HOME /usr/oracle [cottrell@pinger ~]$ setenv ORACLE_HOME /usr/oracle [cottrell@pinger ~]$ sqlplus iepm@slacprod SQL*Plus: Release 11.1.0.6.0 - Production on Fri Jan 21 13:32:15 2022 Copyright (c) 1982, 2007, Oracle. All rights reserved. Enter password: Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production SQL> insert into country (country_id, country, continent, tld) 2 values (country_seq.nextval, 'Fiji', 'Oceania', 'fj'); 1 row created. SQL> commit; Commit complete. SQL> exit; Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production [cottrell@pinger ~]$ [cottrell@pinger ~]$ date Fri Jan 21 13:40:21 PST 2022
The COUNTRY table is not used except by the list of values in the UI. Note that if a country name needs to be changed or if a country was placed in the wrong region, it must be updated on all the nodes in the NODEDETAILS table as well as on the COUNTRY table.
To update the COUNTRY table to fix the list of values, get the COUNTRY_ID for the location that needs correction:
select country_id, country, continent from country where country like 'Demo%';
Then update the COUNTRY table:
update country set country = 'Democratic Republic of Congo' where country_id = 461; commit;
Finally, fix any incorrect entries in the actual NODEDETAILS table by putting the correct country in the set clause and the incorrect value in the where clause.
update nodedetails set country = 'Democratic Republic of Congo' where country = 'Democratic Repulic of Congo'; commit;
To re-assign a country from one region to another, change the CONTINENT value in both tables.
update country set continent = 'Europe' where country = 'Latvia'; update nodedetails set continent = 'Europe' where country = 'Latvia'; commit;
Problems
The checking the validity of the data is not particularly robust, especially when creating a new entry. It can complain that there is an error in the data (invalid IPv4 address, extra spaces in the field, duplicate nodename (i.e. it already exists)). The error messages are not very instructive. In this case, you may be able to correct the entry or you may have to cancel the entry and re-create it.
When accessing https://oraweb.slac.stanford.edu/apex/slacprod/f?p=123:1:433331252041196::::: and entering 119.31.233.174 in the search field the right entry comes up, however, when I try to edit it I get
ORA-01403: no data found OK
The entry is corrupted. It is probably a space (e.g. a trailing blank) in the key field (Nodename) of the record containing 119.31.233.174 in IEPM.NODEDETAILS. Workspace IEPM on SLACPROD.