{"id":87834,"date":"2020-07-11T01:42:00","date_gmt":"2020-07-10T23:42:00","guid":{"rendered":"https:\/\/prohoster.info\/blog\/administrirovanie\/kuda-delos-mesto-na-servere-confluence-ili-statistika-confluence-po-vlozheniyam"},"modified":"2020-07-11T01:42:00","modified_gmt":"2020-07-10T23:42:00","slug":"kuda-delos-mesto-na-servere-confluence-ili-statistika-confluence-po-vlozheniyam","status":"publish","type":"post","link":"https:\/\/prohoster.info\/it\/blog\/administrirovanie\/kuda-delos-mesto-na-servere-confluence-ili-statistika-confluence-po-vlozheniyam","title":{"rendered":"Dove \u00e8 andato lo spazio sul server Confluence? O le statistiche di Confluence sugli allegati","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Vi \u00e8 mai capitato che il server Confluence fosse a corto di spazio e non sapete chi carica di pi\u00f9 gli allegati?<\/p>\n

<\/p>\n

Per sapere questo \u00e8 necessario avere accesso al database PostgreSQL di confluence.<\/p>\n

<\/p>\n

Con una query SQL al database PostgreSQL \u00e8 possibile ottenere statistiche utili su Confluence.<\/p>\n

<\/a><\/noindex><\/p>\n

Dimensione totale di tutti gli allegati in Confluence:<\/h3>\n

<\/p>\n

SELECT s.spaceid,\n       s.spacename,\n       sum(LONGVAL)\nFROM contentproperties c\nJOIN content co ON c.contentid = co.contentid\nJOIN spaces s ON co.spaceid = s.spaceid\nWHERE c.contentid IN\n    (SELECT contentid\n     FROM content\n     WHERE contenttype = 'ATTACHMENT')\n  AND c.propertyname = 'FILESIZE'\nGROUP BY s.spaceid\nORDER BY SUM DESC\nLIMIT 5;<\/code><\/pre>\n
<\/p>\n
Risultato (spacename sostituito):<\/p>\n
<\/p>\n
\"Dove<\/p>\n
<\/p>\n
Pagine con il maggior numero di versioni storiche nel tempo:<\/h3>\n
<\/p>\n
SELECT title,\n       MAX(VERSION)\nFROM content\nWHERE contenttype = 'PAGE'\nGROUP BY title\nORDER BY 2 DESC\nLIMIT 5;<\/code><\/pre>\n
<\/p>\n
Risultato:<\/p>\n
<\/p>\n
\"Dove<\/p>\n
<\/p>\n
I file allegati pi\u00f9 grandi nel vostro istanza di Confluence<\/h3>\n
<\/p>\n
SELECT DISTINCT c.contentid,\n                c.title AS attachmentTitle,\n                u.username AS uploadedBy,\n                co.title AS pageTitle,\n                cn.longval AS bytes\nFROM CONTENT AS c\nJOIN USER_MAPPING AS u ON u.user_key = c.creator\nJOIN CONTENT AS co ON c.pageid = co.contentid\nJOIN CONTENTPROPERTIES AS cn ON cn.contentid = c.contentid\nWHERE c.contenttype = 'ATTACHMENT'\n  AND cn.longval IS NOT NULL\nORDER BY cn.longval DESC\nLIMIT 5;<\/code><\/pre>\n
<\/p>\n
Risultato:<\/p>\n
<\/p>\n
\"Dove<\/p>\n
<\/p>\n
Numero di pagine nel cestino e dimensione totale delle pagine nel cestino per spazio:<\/h3>\n
<\/p>\n
\nSELEZIONA Count(content.contentid) AS number_of_trashed_pages,\n       Pg_size_pretty(SUM(Pg_column_size(bodycontent.BODY))) AS trash_total_size,\n       spaces.spacename AS space_name\nDA bodycontent\nINNER JOIN content ON (content.contentid = bodycontent.contentid)\nINNER JOIN spaces ON (content.spaceid = spaces.spaceid)\nDOVE bodycontent.contentid IN\n    (SELEZIONA contentid\n     DA content\n     DOVE content_status = 'deleted'\n       E contenttype = 'PAGE')\nGROUP BY space_name\nORDER BY trash_total_size\nLIMIT 5;<\/code><\/pre>\n
<\/p>\n
Risultato:<\/p>\n
<\/p>\n
\"Dove<\/p>\n
<\/p>\n
Dimensione totale degli allegati caricati da ciascun utente su tutte le pagine<\/h3>\n
<\/p>\n
SELECT u.lower_username,\n       sum(cp.longval) AS \"size\"\nFROM content c1\nJOIN content c2 ON c1.contentid = c2.pageid\nJOIN user_mapping u ON c1.creator=u.user_key\nJOIN contentproperties cp ON c2.contentid = cp.contentid\nWHERE c2.contenttype='ATTACHMENT'\nGROUP BY u.lower_username\nORDER BY sum(cp.longval) DESC\nLIMIT 5;<\/code><\/pre>\n
<\/p>\n
Risultato:<\/p>\n
<\/p>\n
\"Dove<\/p>\n
<\/p>\n
P.S. Aggiungi al post query SQL utili per Confluence<\/p>\n
Fonte: habr.com<\/a> <\/p>","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"excerpt":{"rendered":"
\u0423 \u0432\u0430\u0441 \u0431\u044b\u0432\u0430\u043b\u043e \u0442\u0430\u043a\u043e\u0435 \u043d\u0430 \u0441\u0435\u0440\u0432\u0435\u0440\u0435 Confluence \u0437\u0430\u043a\u043e\u043d\u0447\u0438\u043b\u043e\u0441\u044c \u043c\u0435\u0441\u0442\u043e, \u0430 \u0432\u044b \u043d\u0435 \u0437\u043d\u0430\u0435\u0442\u0435 \u043a\u0442\u043e \u0431\u043e\u043b\u044c\u0448\u0435 \u0432\u0441\u0435\u0433\u043e \u0437\u0430\u0433\u0440\u0443\u0436\u0430\u0435\u0442 \u0432\u043b\u043e\u0436\u0435\u043d\u0438\u0439? \u0427\u0442\u043e\u0431\u044b \u0443\u0437\u043d\u0430\u0442\u044c \u044d\u0442\u043e \u043d\u0435\u043e\u0431\u0445\u043e\u0434\u0438\u043c \u0434\u043e\u0441\u0442\u0443\u043f \u043a \u0432 \u0411\u0414 PostgreSQL confluence. \u0421 \u043f\u043e\u043c\u043e\u0449\u044c\u044e SQL \u0437\u0430\u043f\u0440\u043e\u0441\u043e\u043c \u043a \u0411\u0414 PostgreSQL \u043c\u043e\u0436\u043d\u043e \u0443\u0437\u043d\u0430\u0442\u044c \u043f\u043e\u043b\u0435\u0437\u043d\u0443\u044e \u0441\u0442\u0430\u0442\u0438\u0441\u0442\u0438\u043a\u0443 \u043f\u043e Confluence. \u041e\u0431\u0449\u0438\u0439 \u0440\u0430\u0437\u043c\u0435\u0440 \u0432\u0441\u0435\u0445 \u0432\u043b\u043e\u0436\u0435\u043d\u0438\u0439 \u0432 Confluence: SELECT s.spaceid, s.spacename, sum(LONGVAL) FROM contentproperties c JOIN […]<\/p>\n","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"author":1,"featured_media":87835,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[688],"tags":[],"class_list":["post-87834","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-administrirovanie"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\n","aioseo_head_json":{"title":"\ud83e\udd47Dove \u00e8 finito lo spazio sul server Confluence? O statistiche Confluence sugli allegati | ProHoster","description":"Hai mai avuto questo problema: lo spazio sul server Confluence \u00e8 terminato e non sai chi carica pi\u00f9 allegati? Per scoprirlo, \u00e8 necessario avere accesso al database PostgreSQL di Confluence. Utilizzando una query SQL sul database PostgreSQL, \u00e8 possibile ottenere statistiche utili su Confluence. Dimensione totale di tutti gli allegati in Confluence: SELECT s.spaceid, s.spacename, sum(LONGVAL) DA contentproperties c JOIN","canonical_url":"https:\/\/prohoster.info\/it\/blog\/administrirovanie\/kuda-delos-mesto-na-servere-confluence-ili-statistika-confluence-po-vlozheniyam","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"it_IT","og:site_name":"ProHoster | \u041a\u0443\u043f\u0438\u0442\u044c \u043d\u0430\u0434\u0435\u0436\u043d\u044b\u0439 \u0445\u043e\u0441\u0442\u0438\u043d\u0433 \u0434\u043b\u044f \u0441\u0430\u0439\u0442\u043e\u0432 \u0441 \u0437\u0430\u0449\u0438\u0442\u043e\u0439 \u043e\u0442 DDoS, VPS VDS \u0441\u0435\u0440\u0432\u0435\u0440\u044b","og:type":"article","og:title":"\ud83e\udd47\u041a\u0443\u0434\u0430 \u0434\u0435\u043b\u043e\u0441\u044c \u043c\u0435\u0441\u0442\u043e \u043d\u0430 \u0441\u0435\u0440\u0432\u0435\u0440\u0435 Confluence? \u0418\u043b\u0438 \u0441\u0442\u0430\u0442\u0438\u0441\u0442\u0438\u043a\u0430 Confluence \u043f\u043e \u0432\u043b\u043e\u0436\u0435\u043d\u0438\u044f\u043c | ProHoster","og:description":"\u0423 \u0432\u0430\u0441 \u0431\u044b\u0432\u0430\u043b\u043e \u0442\u0430\u043a\u043e\u0435 \u043d\u0430 \u0441\u0435\u0440\u0432\u0435\u0440\u0435 Confluence \u0437\u0430\u043a\u043e\u043d\u0447\u0438\u043b\u043e\u0441\u044c \u043c\u0435\u0441\u0442\u043e, \u0430 \u0432\u044b \u043d\u0435 \u0437\u043d\u0430\u0435\u0442\u0435 \u043a\u0442\u043e \u0431\u043e\u043b\u044c\u0448\u0435 \u0432\u0441\u0435\u0433\u043e \u0437\u0430\u0433\u0440\u0443\u0436\u0430\u0435\u0442 \u0432\u043b\u043e\u0436\u0435\u043d\u0438\u0439? \u0427\u0442\u043e\u0431\u044b \u0443\u0437\u043d\u0430\u0442\u044c \u044d\u0442\u043e \u043d\u0435\u043e\u0431\u0445\u043e\u0434\u0438\u043c \u0434\u043e\u0441\u0442\u0443\u043f \u043a \u0432 \u0411\u0414 PostgreSQL confluence. \u0421 \u043f\u043e\u043c\u043e\u0449\u044c\u044e SQL \u0437\u0430\u043f\u0440\u043e\u0441\u043e\u043c \u043a \u0411\u0414 PostgreSQL \u043c\u043e\u0436\u043d\u043e \u0443\u0437\u043d\u0430\u0442\u044c \u043f\u043e\u043b\u0435\u0437\u043d\u0443\u044e \u0441\u0442\u0430\u0442\u0438\u0441\u0442\u0438\u043a\u0443 \u043f\u043e Confluence. \u041e\u0431\u0449\u0438\u0439 \u0440\u0430\u0437\u043c\u0435\u0440 \u0432\u0441\u0435\u0445 \u0432\u043b\u043e\u0436\u0435\u043d\u0438\u0439 \u0432 Confluence: SELECT s.spaceid, s.spacename, sum(LONGVAL) FROM contentproperties c JOIN","og:url":"https:\/\/prohoster.info\/it\/blog\/administrirovanie\/kuda-delos-mesto-na-servere-confluence-ili-statistika-confluence-po-vlozheniyam","og:image":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:secure_url":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:width":350,"og:image:height":350,"article:published_time":"2020-07-10T23:42:00+00:00","article:modified_time":"2020-07-10T23:42:00+00:00","article:publisher":"https:\/\/www.facebook.com\/prohoster","article:author":"https:\/\/www.facebook.com\/prohoster"},"aioseo_meta_data":{"post_id":"87834","title":null,"description":null,"keywords":null,"keyphrases":null,"primary_term":null,"canonical_url":null,"og_title":null,"og_description":null,"og_object_type":"default","og_image_type":"default","og_image_url":null,"og_image_width":null,"og_image_height":null,"og_image_custom_url":null,"og_image_custom_fields":null,"og_video":null,"og_custom_url":null,"og_article_section":null,"og_article_tags":null,"twitter_use_og":false,"twitter_card":"default","twitter_image_type":"default","twitter_image_url":null,"twitter_image_custom_url":null,"twitter_image_custom_fields":null,"twitter_title":null,"twitter_description":null,"schema":{"blockGraphs":[],"customGraphs":[],"default":{"data":{"Article":[],"Course":[],"Dataset":[],"FAQPage":[],"Movie":[],"Person":[],"Product":[],"ProductReview":[],"Car":[],"Recipe":[],"Service":[],"SoftwareApplication":[],"WebPage":[]},"graphName":"","isEnabled":true},"graphs":[]},"schema_type":null,"schema_type_options":null,"pillar_content":false,"robots_default":true,"robots_noindex":false,"robots_noarchive":false,"robots_nosnippet":false,"robots_nofollow":false,"robots_noimageindex":false,"robots_noodp":false,"robots_notranslate":false,"robots_max_snippet":null,"robots_max_videopreview":null,"robots_max_imagepreview":"large","priority":null,"frequency":null,"local_seo":null,"seo_analyzer_scan_date":null,"breadcrumb_settings":null,"limit_modified_date":false,"reviewed_by":null,"ai":null,"created":"2021-02-28 13:42:28","updated":"2022-09-30 17:43:09"},"gt_translate_keys":[{"key":"link","format":"url"}],"_links":{"self":[{"href":"https:\/\/prohoster.info\/it\/wp-json\/wp\/v2\/posts\/87834","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/it\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/it\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/it\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/it\/wp-json\/wp\/v2\/comments?post=87834"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/it\/wp-json\/wp\/v2\/posts\/87834\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/it\/wp-json\/wp\/v2\/media\/87835"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/it\/wp-json\/wp\/v2\/media?parent=87834"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/it\/wp-json\/wp\/v2\/categories?post=87834"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/it\/wp-json\/wp\/v2\/tags?post=87834"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}