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 On Thu, 20 Jan 2022 at 10:48, Bravismore Mumanyi <[email protected]> wrote: > 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 > > On Thu, Jan 20, 2022 at 8:56 AM Sean Carte <[email protected]> wrote: > >> 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 >>> <http://groups.google.com/group/dspace-community/t/1ac6ed809a4dea63?utm_source=digest&utm_medium=email> >>> Bravismore Mumanyi <[email protected]>: 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 >>> <#m_-6812337490614000549_m_-6237699818254643969_m_2141145938362024295_digest_top> >>> You received this digest because you're subscribed to updates for this >>> group. You can change your settings on the group membership page >>> <https://groups.google.com/forum/?utm_source=digest&utm_medium=email#!forum/dspace-community/join> >>> . >>> To unsubscribe from this group and stop receiving emails from it send an >>> email to [email protected]. >>> >> -- >> 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 [email protected]. >> 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 >> <https://groups.google.com/d/msgid/dspace-community/CA%2BxAuhPdWReh%3DWK-Lu6eTMVOvH1103D9B8%2BgW_9zqscECUdXAw%40mail.gmail.com?utm_medium=email&utm_source=footer> >> . >> > -- 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 [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/dspace-community/CA%2BxAuhNs50UfHogFUKYcKGMz3g5vvsQjUDyVugHKJRv-U4hpGA%40mail.gmail.com.
