Dspace repository reports

[Bravismore Mumanyi]

Dear Dspace User Community

Any pointers on how we can generate the reports below.

1. Items added last month
2. Items added in a given year (between given dates)
3. Total Items in repository
4. Item views in the past month
5. Item views in a given year  (between given dates)
6. Total item views

Are these SQL queries, solr-based queries? If anyone is willing to share the procedure followed. 

Your guidance would be appreciated

Regards

[Sean Carte]

Hi Bravismore

I can tell you what I do, though I'm no expert in this. Also, this is on DSpace 6.3.

1. Items added last month & 2. Items added in a given year (between given dates)

Query the postgres db directly using psql:

SELECT cm.community_id,  substring(mv.text_value,1,7) AS year_month, count(*) AS num_items_added

FROM metadatavalue mv, item it, collection co, community cm, community2collection c2c

WHERE mv.dspace_object_id = it.uuid

AND it.owning_collection = co.uuid

AND co.uuid = c2c.collection_id

AND c2c.community_id = cm.uuid

AND mv.metadata_field_id = 11

AND it.in_archive AND text_value >= '2019-09'

AND text_value < '2019-10' GROUP BY 1, 2 ORDER BY 1;

3. Total Items in repository

I add up the item totals for each community displayed in the UI. You'll need this in your config file: webui.strengths.show = true

4. Item views in the past month & 5. Item views in a given year (between given dates)

This is a useful post on using solr: https://groups.google.com/g/dspace-tech/c/wudFqZMSF-Y

and these are the solr queries that I use:

- downloads:

/solr/statistics/select?q=type:0+owningColl:9+isBot:false+time:[2017-06-01T00:00:00Z+TO+2017-07-01T00:00:00Z]&fq=-(bundleName:[*+TO+*]-bundleName:ORIGINAL)&indent=on&rows=0

- visits:

/solr/statistics/select?q=type:2+owningColl:9+isBot:false+time:[2017-06-01T00:00:00Z+TO+2017-07-01T00:00:00Z]&indent=on&rows=0

6. Total item views

I've never considered this, but I should think a variation on the SQL above should work.

Sean

Dspace repository reports

Bravismore Mumanyi <bravi...@gmail.com>: Jan 19 02:51PM +0200

Dear Dspace User Community   Any pointers on how we can generate the reports below.

1. Items added last month 2. Items added in a given year (between given dates) 3. Total Items in repository 4. Item views in the past month 5. Item views in a given year (between given dates) 6. Total item views

Are these SQL queries, solr-based queries? If anyone is willing to share the procedure followed.   Your guidance would be appreciated   Regards

Back to top

You received this digest because you're subscribed to updates for this group. You can change your settings on the group membership page. To unsubscribe from this group and stop receiving emails from it send an email to dspace-communi...@googlegroups.com.

[Bravismore Mumanyi]

Your comprehensive response is greatly appreciated Sean. I am still on 5.6 though.

Maybe it's a compelling reason to upgrade. I will try to modify the SQL for 5.6 and upgrade if I hit a brick wall.

Regards

--
All messages to this mailing list should adhere to the Code of Conduct: https://www.lyrasis.org/about/Pages/Code-of-Conduct.aspx
---
You received this message because you are subscribed to the Google Groups "DSpace Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to dspace-communi...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/dspace-community/CA%2BxAuhPdWReh%3DWK-Lu6eTMVOvH1103D9B8%2BgW_9zqscECUdXAw%40mail.gmail.com.

[Sean Carte]

Hi Bravismore

We're also still on 5.x for our main repository. This is the SQL I use there:

SELECT cm.community_id, co.collection_id, substring(mv.text_value,1,7) AS year_month,  count(*) AS num_items_added 

FROM metadatavalue mv, item it, collection co, community cm, community2collection c2c 

WHERE mv.resource_id = it.item_id

AND it.owning_collection = co.collection_id 

AND co.collection_id = c2c.collection_id 

AND c2c.community_id = cm.community_id 

AND mv.metadata_field_id = 11 

AND it.in_archive  AND text_value >= '2019-08' 

AND text_value < '2019-09'  GROUP BY 1, 2, 3 ORDER BY 2;

I think the main change was the ids -> uuids.

Also, I'm not sure if your metadata_field_id will be the same as mine.

Sean

[Karol Sokalski]

Hi Sean,

where i can find owningColl number for collections and Communities?: 

/solr/statistics/select?q=type:0+owningColl:9+isBot:false+time:[2017-06-01T00:00:00Z+TO+2017-07-01T00:00:00Z]&fq=-(bundleName:[*+TO+*]-bundleName:ORIGINAL)&indent=on&rows=0

Thanks,

Karol

[Sean Carte]

Hi Karol

I think the easiest way to find the ID (or uuid in 6+) is to query the database:

 SELECT * FROM collection;

You haven't said what version of DSpace you are using, but for DSpace 5, you could do this:

SELECT
co.collection_id, mv.resource_id, mv.text_value
FROM collection co, metadatavalue mv
WHERE co.collection_id = mv.resource_id
AND mv.resource_type_id = 3;

That won't work in DSpace 6 as there is no resource_type_id field anymore.

For communities:

SELECT * FROM community;

SELECT
cm.community_id, mv.resource_id, mv.text_value
FROM community cm, metadatavalue mv
WHERE cm.community_id = mv.resource_id
AND mv.resource_type_id = 4;

Sean

Karol Sokalski <karols...@gmail.com>: Jun 08 04:17AM -0700

Hi Sean,
 
where i can find owningColl number for collections and Communities?:

/solr/statistics/select?q=type:0+*owningColl:9*

[Sean Carte]

For DSpace 6:

SELECT community_id, uuid, text_value
FROM community co, metadatavalue mv
WHERE co.uuid = dspace_object_id;

SELECT collection_id, uuid, text_value

FROM collection co, metadatavalue mv

WHERE co.uuid = dspace_object_id;

Sean

[Karol Sokalski]

Hi Sean,

Yes, i using DSpace 6.3, thank You for helping.  I tried this:  SELECT * FROM community; and i see:

community_id |   uuid                            |
9                        |  sdsd343241535454 |

great, but, how can i corelate uuid with name of communitys/collections? Community_id is the number after domain name https://repozytorium.amu.edu.pl/10593/9 ?

Thank You,

Karol

[Sean Carte]

Hi Karol

These queries should give you what you need:

SELECT community_id, uuid, text_value
FROM community co, metadatavalue mv
WHERE co.uuid = dspace_object_id;

SELECT collection_id, uuid, text_value
FROM collection co, metadatavalue mv

WHERE co.uuid = dspace_object_id;

Sean

Dspace repository reports

Karol Sokalski <karols...@gmail.com>: Jun 10 01:16AM -0700

Hi Sean,   Yes, i using DSpace 6.3, thank You for helping. I tried this: SELECT * FROM community; and i see:   community_id | uuid | 9 | sdsd343241535454 |   great, but, how can i corelate uuid with name of communitys/collections? Community_id is the number after domain name

https://repozytorium.amu.edu.pl/10593/*9 *?   Thank You,   Karol

Back to top

[Karol Sokalski]

Sean,

work perfectly! Thanks a lot! Best Regards,

Karol

--
All messages to this mailing list should adhere to the Code of Conduct: https://www.lyrasis.org/about/Pages/Code-of-Conduct.aspx
---

You received this message because you are subscribed to a topic in the Google Groups "DSpace Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/dspace-community/GsbtgJpN6mM/unsubscribe.
To unsubscribe from this group and all its topics, send an email to dspace-communi...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/dspace-community/CA%2BxAuhMu-DbHrwx-7Ug-kWcOBqHuLpi_cFX%3D38AB4D3q1b%2BM0Q%40mail.gmail.com.