| 440 | = Statistics for ENES data nodes = |
| 441 | |
| 442 | == Prerequisites == |
| 443 | 1. Install postgres client |
| 444 | {{{ |
| 445 | #!sh |
| 446 | $ yum install postgresql |
| 447 | }}} |
| 448 | |
| 449 | 2. Install bind utils |
| 450 | {{{ |
| 451 | #!sh |
| 452 | $ yum install bind-utils |
| 453 | }}} |
| 454 | |
| 455 | == Procedure == |
| 456 | |
| 457 | [[NoteBox(note, The procedure (including all the queries) is reported in the [attachment:is-enes2_statistics.txt] file )]] |
| 458 | |
| 459 | |
| 460 | 1) Create a 'stats' directory |
| 461 | {{{ |
| 462 | #!sh |
| 463 | $ cd stats |
| 464 | }}} |
| 465 | |
| 466 | 1.1) |
| 467 | {{{ |
| 468 | #!sh |
| 469 | $ psql esgcet -U dbsuper -c "select distinct remote_addr from esgf_node_manager.access_logging" > ip_addresses.txt |
| 470 | }}} |
| 471 | |
| 472 | 1.2) Open the file ip_addresses.txt and cleanup the first two rows and the last one at the end of the file |
| 473 | |
| 474 | 2) |
| 475 | {{{ |
| 476 | #!sh |
| 477 | $ ./geoiplookup0.2.sh > ip_addr_country.txt |
| 478 | }}} |
| 479 | |
| 480 | 3) Creation of a support table in the esgf_dashboard namespace |
| 481 | 3.1) |
| 482 | {{{ |
| 483 | #!sh |
| 484 | $ psql esgcet -U dbsuper -c "drop table esgf_dashboard.stats_support_table;" |
| 485 | }}} |
| 486 | |
| 487 | 3.2) |
| 488 | {{{ |
| 489 | #!sh |
| 490 | $ psql esgcet -U dbsuper -c "create table esgf_dashboard.stats_support_table(ip varchar(20), country varchar(10), eunoteu varchar(10));" |
| 491 | }}} |
| 492 | |
| 493 | 4) |
| 494 | {{{ |
| 495 | #!sh |
| 496 | $ psql esgcet -U dbsuper -e < ip_addr_country.txt |
| 497 | }}} |
| 498 | |
| 499 | 5) |
| 500 | {{{ |
| 501 | #!sh |
| 502 | $ psql esgcet -U dbsuper -c "update esgf_dashboard.stats_support_table set eunoteu='EU' where country in ('FR','GB','SI','HR','BE','CZ','GR','NO','IT','ES','DE','FI', 'DK', 'SE', 'CH', 'RS', 'NL', 'ME', 'LU', 'PT', 'LI', 'IE', 'HU', 'EE','BG', 'BA', 'AT', 'AL', 'SK', 'RS', 'SM', 'RO', 'PL', 'MC', 'MT');" |
| 503 | }}} |
| 504 | |
| 505 | 6) |
| 506 | {{{ |
| 507 | #!sh |
| 508 | $ psql esgcet -U dbsuper -c "update esgf_dashboard.stats_support_table set eunoteu='NotEU' where eunoteu is NULL;" |
| 509 | }}} |
| 510 | |
| 511 | 7) |
| 512 | {{{ |
| 513 | #!sh |
| 514 | $ psql esgcet -U dbsuper -c "create table esgf_dashboard.euips as select distinct ip from esgf_dashboard.stats_support_table where eunoteu='EU';" |
| 515 | }}} |
| 516 | |
| 517 | 8) Execute query stats: |
| 518 | |
| 519 | Generate a query_stats_[NUMBER].sql for each statistics queries in attachment:is-enes2_statistics.txt and execute: |
| 520 | |
| 521 | {{{ |
| 522 | #!sh |
| 523 | $ psql esgcet -U dbsuper -e < query_stats_1.sql > query_stats_1.result |
| 524 | }}} |
| 525 | |
| 526 | 9) Generate a zip |
| 527 | {{{ |
| 528 | #!sh |
| 529 | $ zip -9r node_stats_data.meteo.unican.es.zip stats |
| 530 | }}} |
| 531 | |
| 532 | Notes: |
| 533 | |
| 534 | 3.1 and 3.2 aim at providing the same results. |
| 535 | 3.1 is the original query for the statistics inherited from IS-ENES1. Since the results were not as we expected, we defined the query 3.2 |
| 536 | 3.2 is the new query that basically replaces the regexpression with a simple "joining" condition between the access_logging table (node_manager namespace) and the file_version table (publisher) |
| 537 | |
| 538 | The same for 4.1 and 4.2 |
| 539 | |
| 540 | Both at CMCC and DKRZ 3.2 and 4.2 ran fine. On the contrary 3.1 and 4.1 did not provide the expected statistics. |
| 541 | |
| 542 | Concerning query number 5: |
| 543 | |
| 544 | Get distinct remote locations (by remote address) who have performed at least 1 download from the data node |
| 545 | select distinct(remote_addr) from esgf_node_manager.access_logging; |
| 546 | |
| 547 | please store the result (list of IPs) into a different file and send it to us. |
| 548 | Actually, this is already provided by step n°1 ("ip_addresses.txt"). |
| 549 | Please send the "ip_addresses.txt" file to us. |
| 550 | We'll then try to get a global picture about all the remote locations connecting to the IS-ENES data nodes. |
| 551 | |