items submitted by specific users?

[Karol Sokalski]

Hi,

 

how can i check how many items was submited by specific user in spefic year? I found dc.description.provenance, where is stored data about user and year when item was submited, but i don't know how can I automating it?

Karol

[Jose Miguel Santana]

Hi,

We extract that data directly from the PostgreSQL database, using this SQL query:

select split_part(split_part(met1.text_value,'(',2),')',1) as user,
split_part(split_part(met1.text_value,') on ',2),'-',1)as year,
count(*) as Publications
from metadatavalue met1
where text_value like 'Submitted by%'
and met1.metadata_field_id=(select metadata_field_id from metadatafieldregistry m
where element='description' and qualifier='provenance' and metadata_schema_id=1)
and text_value not like 'null'
group by split_part(split_part(met1.text_value,'(',2),')',1),
split_part(split_part(met1.text_value,') on ',2),'-',1)
order by year desc

You can automate the recovery of that data using a script. We use the open source version of Metabase (https://www.metabase.com/) to query the DSpace PostgreSQL database, extract the data and display it graphically or as tables.

[Karol Sokalski]

Hi,

it is very helpfully - thanks:) Best regards,

Karol