Hi David!

Thank you for the solution and the explanation.  That worked perfectly.  It’s the search _expression_ syntax that I needed to understand. 

Tomasz

From: David R Newman <drn@ecs.soton.ac.uk>
Sent: April 29, 2025 2:48 AM
To: eprints-tech@ecs.soton.ac.uk; Tomasz Neugebauer <Tomasz.Neugebauer@concordia.ca>
Subject: Re: [EP-tech] report on how many users with ORCIDs in our repository, but limited by year/date.

Attention This email originates from outside the concordia.ca domain. // Ce courriel provient de l'extérieur du domaine de concordia.ca

Hi Tomasz,

Here is a quick bin script that can return the results you want:

#!/usr/bin/perl -I /opt/eprints3/perl_lib

use EPrints;
use strict;
use warnings;

my $repoid = $ARGV[0];
my $repo = new EPrints::Session( 1 , $repoid );
my $ds = $repo->dataset( 'user' );

my $searchexp = $ds->prepare_search();

$searchexp->add_field(
    fields => [
        $ds->field('orcid_token_expires')
    ],
    value => "-2044",
    match => "EQ",
);

my $users = $searchexp->perform_search;

print "users before 2044: ".$users->count."\n";

It should be possible to extract the core of this into however you want to display this.  The two critical parts are you just want to refer to to the main name of the field: orcid_token_expires and not the year part.  Also, you need to appreciate the syntax of date matching.  In the above example, "-2044" is the same as <=2044 in the SQL query example you gave (and also excludes all NULL values).  If you only wanted to match before 2044, you would need to use "-2043", if you only want those in 2044 (January 1st to December 31st), you would simply use "2044".  Here is the API page section on the wiki that give a bit more detail:

https://wiki.eprints.org/w/API:EPrints/MetaField/Date#Searching

Regards

David Newman

CAUTION: This e-mail originated outside the University of Southampton.

CAUTION: This e-mail originated outside the University of Southampton.

I was able to construct an SQL query that does the job, it's this:

 

SELECT COUNT(*)

FROM user

WHERE orcid IS NOT NULL

  AND orcid != ''

  AND orcid_token_expires_year IS NOT NULL

  AND orcid_token_expires_year <= 2044;

 

Then I spent the rest of the day trying to extract this same info using the EPrints API / Perl script, and I just couldn't get it to work!   One error after another after another...  defeat after defeat.  AI coding bots had equally little success.  I think the info is in a field in the user object called orcid_token_expires, but how to extract the info to get that same information proved too much.  I will try again, just because now I have to get it, but if anyone can help, it would be greatly appreciated!

 

Tomasz

 

 

---

From: eprints-tech-request@ecs.soton.ac.uk <eprints-tech-request@ecs.soton.ac.uk> on behalf of Alan.Stiles [He/Him/They] <alan.stiles@open.ac.uk>
Sent: Monday, April 28, 2025 12:27 PM
To: eprints-tech@ecs.soton.ac.uk <eprints-tech@ecs.soton.ac.uk>
Subject: Re: [EP-tech] report on how many users with ORCIDs in our repository, but limited by year/date.

 

Attention This email originates from outside the concordia.ca domain. // Ce courriel provient de l'extérieur du domaine de concordia.ca

 

 

CAUTION: This e-mail originated outside the University of Southampton.

CAUTION: This e-mail originated outside the University of Southampton.

Sorry Tomasz,

To my understanding, the token is valid for 20 years, so it should be before 1/1/2045, (epoch time 2366841600)  but my fingers apparently couldn’t type the right date in the email last week…

 

Alan.

 

From: eprints-tech-request@ecs.soton.ac.uk <eprints-tech-request@ecs.soton.ac.uk> on behalf of Tomasz Neugebauer <Tomasz.Neugebauer@concordia.ca>
Date: Monday, 28 April 2025 at 16:01
To: eprints-tech@ecs.soton.ac.uk <eprints-tech@ecs.soton.ac.uk>
Subject: Re: [EP-tech] report on how many users with ORCIDs in our repository, but limited by year/date.

External email: if the sender or content looks suspicious, please click the Report Message icon, or forward it to report-phishing

CAUTION: This e-mail originated outside the University of Southampton.

CAUTION: This e-mail originated outside the University of Southampton.

Thank you Alan!  That's clever, but I'm not if I understand.  The reason why it's before 01/01/2046 is because of the default length of time that the ORCID token is valid for? How do I go from "up to the end of 2024" to 01/01/2046 ?  

I suppose there is no date recorded in EPrints when the ORCID was synched/added to the repository?

 

Tomasz

 

 

 

 

---

From: eprints-tech-request@ecs.soton.ac.uk <eprints-tech-request@ecs.soton.ac.uk> on behalf of Alan.Stiles [He/Him/They] <alan.stiles@open.ac.uk>
Sent: Friday, April 25, 2025 3:59 AM
To: eprints-tech@ecs.soton.ac.uk <eprints-tech@ecs.soton.ac.uk>
Subject: Re: [EP-tech] report on how many users with ORCIDs in our repository, but limited by year/date.

 

Attention This email originates from outside the concordia.ca domain. // Ce courriel provient de l'extérieur du domaine de concordia.ca

 

 

CAUTION: This e-mail originated outside the University of Southampton.

CAUTION: This e-mail originated outside the University of Southampton.

Possibly extract from the user table by token expiry date before 1/1/46 ? (epoch 2366841600)

 

Alan

 

From: eprints-tech-request@ecs.soton.ac.uk <eprints-tech-request@ecs.soton.ac.uk> on behalf of Tomasz Neugebauer <Tomasz.Neugebauer@concordia.ca>
Date: Thursday, 24 April 2025 at 17:53
To: eprints-tech@ecs.soton.ac.uk <eprints-tech@ecs.soton.ac.uk>
Subject: [EP-tech] report on how many users with ORCIDs in our repository, but limited by year/date.

External email: if the sender or content looks suspicious, please click the Report Message icon, or forward it to report-phishing

CAUTION: This e-mail originated outside the University of Southampton.

CAUTION: This e-mail originated outside the University of Southampton.

Hi everyone!  

 

I need to extract how many users have synched/added an ORCID in our repository, which the ORCID plugin already provides, but I need to have that limited by date.  So, I need to report how many ORCIDs up until the end of 2024.  Next year, I will need up until 2025, etc.  Any suggestions on how I can get that data out of our repo? 

 

Tomasz

*** Options: https://wiki.eprints.org/w/Eprints-tech_Mailing_List

*** Archive: https://www.eprints.org/tech.php/

*** EPrints community wiki: https://wiki.eprints.org/