{"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\/et\/blog\/administrirovanie\/kuda-delos-mesto-na-servere-confluence-ili-statistika-confluence-po-vlozheniyam","title":{"rendered":"Kuhu kadus Confluence\u2019i serveri ruum? V\u00f5i Confluence'i lisade statistika","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Kas olete kunagi kogenud, et Confluence serveris on ruum otsas ja te ei tea, kes on k\u00f5ige rohkem manuseid laadinud?<\/p>\n

<\/p>\n

Selleks on vajalik juurdep\u00e4\u00e4s PostgreSQL confluence andmebaasi.<\/p>\n

<\/p>\n

SQL-p\u00e4ringuga PostgreSQL andmebaasi saate teada kasulikku statistikat Confluence'i kohta.<\/p>\n

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

Confluence'i k\u00f5igi manuste kogumaht:<\/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
V\u00e4ljund (spacename asendatud):<\/p>\n
<\/p>\n
\"Kuhu<\/p>\n
<\/p>\n
Lehed, millel on k\u00f5ige rohkem ajaloolisi versioone:<\/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
V\u00e4ljund:<\/p>\n
<\/p>\n
\"Kuhu<\/p>\n
<\/p>\n
Suurimad manused teie Confluence'i instantsis<\/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
V\u00e4ljund:<\/p>\n
<\/p>\n
\"Kuhu<\/p>\n
<\/p>\n
Pr\u00fcgikasti lehtede arv ja pr\u00fcgikasti lehtede kogumaht ruumi kohta:<\/h3>\n
<\/p>\n
\nVALI Arv(content.contentid) NAGU number_of_trashed_pages,\n       Pg_size_pretty(SUM(Pg_column_size(bodycontent.BODY))) NAGU trash_total_size,\n       spaces.spacename NAGU space_name\nFROM bodycontent\nINNER JOIN content ON (content.contentid = bodycontent.contentid)\nINNER JOIN spaces ON (content.spaceid = spaces.spaceid)\nWHERE bodycontent.contentid IN\n    (SELECT contentid\n     FROM content\n     WHERE content_status = 'deleted'\n       AND contenttype = 'PAGE')\nGROUP BY space_name\nORDER BY trash_total_size\nLIMIT 5;<\/code><\/pre>\n
<\/p>\n
V\u00e4ljund:<\/p>\n
<\/p>\n
\"Kuhu<\/p>\n
<\/p>\n
Iga kasutaja \u00fcles laaditud manuste kogusummad k\u00f5igil lehtedel<\/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
V\u00e4ljund:<\/p>\n
<\/p>\n
\"Kuhu<\/p>\n
<\/p>\n
P.S. T\u00e4iendage postitust kasulike SQL p\u00e4ringutega Confluence'i jaoks<\/p>\n
Allikas: 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\udd47Kuhu kadus ruum Confluence serveris? V\u00f5i Confluence'i statistika manuste kohta | ProHoster","description":"Kas olete kunagi leidnud, et Confluence serveris on ruum otsa saanud ja te ei tea, kes enim manuseid \u00fcles laadib? Selleks, et seda teada saada, on vajalik juurdep\u00e4\u00e4s PostgreSQL confluence andmebaasi. SQL p\u00e4ringuga PostgreSQL andmebaasi kaudu on v\u00f5imalik saada kasulikku statistikat Confluence'i kohta. K\u00f5igi Confluence'i manuste kogusumma: SELECT s.spaceid, s.spacename, sum(LONGVAL) FROM contentproperties c JOIN","canonical_url":"https:\/\/prohoster.info\/et\/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":"et_EE","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\/et\/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\/et\/wp-json\/wp\/v2\/posts\/87834","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/comments?post=87834"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/posts\/87834\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/media\/87835"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/media?parent=87834"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/categories?post=87834"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/tags?post=87834"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}