List of all uploaded files

[terraboss (Markus)]

Hello friends,

what are you thinking about some extra tab at the admin interface to keep control over the current disk usage, files sizes, click rates, filtering them after extension and searching for specific terms?

I wish, I could get more control over all attachments.

Best

[zogstrip]

That’s something I definitely want done. Not sure when though.

[Tarek_Khalil]

Sounds useful!

There is a bunch of product questions around how this could/should work in my mind. It looks like a problem that has been already figured out or solved in other forum software, but I wonder how will this be framed / built around Discourse’s philosophy.

I am also wondering more about the manifestations of providing uploads settings, e.g.: if you chose to introduce extension configuration, the composer needs to cater for this when users are selecting/uploading attachments, and so on. Also, whether this should be in core, or as a plugin.

On the bright side, if we ignore all questions / assumptions / product / design considerations, it doesn’t look like it is technically tricky, I made a one hour spike here (this is very immature implementation though ).

You can definitely extend this with a bunch of useful features: settings, search / filtering, sorting, etc (and some other considerations like supporting pagination, …)

[adopilot (Admir Hodzic)]

If youbtalking about admin tools. You can considure usong plugin data explorer whic allows admins to query database. When I hunting free space I am querying db there is table uploads whic gives you size location and other useful things. Also one good thing is that deleating rows from uploads also dealeting files. Becouse ther is sidekiq job pruge oprhan uploads.

[sam (Sam Saffron)]

I am totally open to adding something here, but I feel a top level tab is a bit too much .

Conceptually this feels like a “report” to me with a drilldown vs a section for uploading things.

I would like to see this link to the new report

When I think about this problem I think the main use case is around admins trying to get a handle on the … upload problem…

• Why do I have so many uploads?

• Which users have the most/largest uploads ?

• What are the 100 biggest uploads on my forum?

• How many things were uploaded in the last month? That way I can keep track of trends.

@codinghorror / @j.jaffeux what are your thoughts here?

[j.jaffeux (Joffrey Jaffeux)]

Yes I agree it could be a report, we might have to start working on the filtering logic I talked with you months ago. But other than that it should be good.

[Tarek_Khalil]

Great line of questioning @sam !

I think perhaps I lack some context on what is the admin’s job / use case for the upload problem, but it appears to be (if there is any research or perhaps admin opinions to confirm that, it would be great!) that it can be framed as: I am an Admin, I want to gain understanding of my instance’s uploads usage.

I wonder if there are any after actions when the job / use case is satisfied. For example, if the admin notices a problem or a trend, will the admin/site_settings/category/files is the place they can optimise their file uploading strategy?

Also, I agree that the Uploads section is heavy as a top level tab for this use case.

[frold]

Any news about this topic?

Im looking for a option to look through the uploaded files.

[terraboss (Markus)]

I guess, sorting and listing files isn’t as much as important for Discourse as for us.

Similar issue:

Issue with adding attachments inside table cells

Hi there, it seems, it’s not possible any more, to add attachments inside tables. Any idea how to fix this? Example Name File bla bla [bla.docx | Name | File | | --- | --- | | bla | bla | | | [bla.docx|attachment](upload://hu8jvVTNiCjzn5XmxXRnhUaRINy.docx) (22.4 KB) | It is because of the seperator “|” between file name and the function name attachment. I really need this, to keep things clean and organised

[simon]

Aqui estão algumas consultas do Data Explorer para obter detalhes sobre uploads de posts. Algo semelhante poderia ser adicionado à seção de relatórios administrativos para sites que não têm o plugin Data Explorer instalado. Avise-me se esses dados não forem o que você procura.

Quais usuários têm mais uploads ou uploads maiores?

Retorna o usuário, a quantidade de uploads e o total de uploads em KB, arredondado para duas casas decimais. O join na tabela users serve para evitar que dados de usuários excluídos sejam retornados. Os resultados são ordenados por tamanho total de upload em ordem decrescente.

Incluindo imagens otimizadas

WITH uploads_with_optimized AS (
SELECT
ul.user_id,
ROUND((SUM(COALESCE(oi.filesize, 0)) + SUM(ul.filesize)) / 1000.0, 2) AS total_kb
FROM post_uploads pul
JOIN uploads ul
ON ul.id = pul.upload_id
LEFT JOIN optimized_images oi
ON ul.id = oi.upload_id
GROUP BY ul.user_id
)

SELECT
uwo.user_id,
COUNT(uploads.user_id) AS upload_count,
total_kb
FROM uploads_with_optimized uwo
JOIN uploads
ON uploads.user_id = uwo.user_id
GROUP BY uploads.user_id, uwo.user_id, total_kb
ORDER BY total_kb DESC
LIMIT 50

Excluindo imagens otimizadas

SELECT
ul.user_id,
COUNT(ul.user_id) AS upload_count,
ROUND(SUM(ul.filesize) / 1000.0, 2) AS total_kb
FROM post_uploads pul
JOIN uploads ul
ON ul.id = pul.upload_id
GROUP BY ul.user_id
ORDER BY total_kb DESC
LIMIT 50

Quais são os 100 maiores uploads do meu fórum?

Retorna o usuário, o post com o upload e o tamanho do arquivo do upload em KB. Os resultados são ordenados por tamanho de upload em ordem decrescente.

Incluindo imagens otimizadas

SELECT
ul.user_id,
pul.post_id,
ROUND((SUM(oi.filesize) + ul.filesize) / 1000.0, 2) AS total_kb
FROM post_uploads pul
JOIN uploads ul
ON ul.id = pul.upload_id
JOIN optimized_images oi
ON ul.id = oi.upload_id
GROUP BY oi.upload_id, ul.user_id, pul.post_id, ul.filesize
ORDER BY total_kb DESC
LIMIT 100

Excluindo imagens otimizadas

SELECT
ul.user_id,
pul.post_id,
ROUND(ul.filesize / 1000.0, 2) AS total_kb
FROM post_uploads pul
JOIN uploads ul
ON ul.id = pul.upload_id
ORDER BY total_kb DESC
LIMIT 100

Uploads dos últimos 30 dias

A consulta exige que você forneça um parâmetro :end_date. A data deve estar no formato ‘aaaa-mm-dd’. Por exemplo, ‘2020-01-08’. Ela retorna resultados para o período de 30 dias que termina com o end_date. Retorna o dia, a quantidade de uploads e o total de uploads do dia em KB para todos os dias do período que possuem uploads de posts. Os resultados são ordenados por dia.

Incluindo imagens otimizadas

--[params]
-- date :end_date

SELECT
ul.created_at::date AS day,
COUNT(1) AS upload_count,
ROUND((SUM(COALESCE(oi.filesize, 0)) + SUM(ul.filesize)) / 1000.0, 2) AS total_kb
FROM post_uploads pul
JOIN uploads ul
ON ul.id = pul.upload_id
LEFT JOIN optimized_images oi
ON ul.id = oi.upload_id
WHERE ul.created_at::date BETWEEN :end_date::date - INTERVAL '30 days' AND :end_date
GROUP BY ul.created_at::date
ORDER BY ul.created_at::date DESC

Excluindo imagens otimizadas

--[params]
-- date :end_date

SELECT
ul.created_at::date AS day,
COUNT(1) AS upload_count,
ROUND(SUM(ul.filesize) / 1000.0, 2) AS daily_upload_kb
FROM post_uploads pul
JOIN uploads ul
ON ul.id = pul.upload_id
WHERE ul.created_at::date BETWEEN :end_date::date - INTERVAL '30 days' AND :end_date
GROUP BY ul.created_at::date
ORDER BY ul.created_at::date DESC

[jochen_weber]

Sou um novo membro da comunidade (meta) e recentemente assumi as funções de administrador de outra comunidade que usa o Discourse (e que é hospedada pelo próprio Discourse). No painel, acabei de ver um “salto” no espaço utilizado pelos Uploads (de cerca de 0,7 GB para 1,2 GB). Parece que o Data Explorer está disponível apenas para planos Business ou superiores… Haveria alguma outra forma de descobrir quais arquivos recentes estão consumindo esses 0,5 GB adicionais?

[sam (Sam Saffron)]

@jochen_weber

Acabei de ver um “salto” no espaço utilizado pelos Uploads (de cerca de 0,7 GB para 1,2 GB).

Isso é inteiramente culpa minha.

Tivemos um grande bug no passado em que não estávamos contando corretamente o tamanho de todos os uploads.

Quando um usuário faz o upload de uma imagem, frequentemente a redimensionamos de 3 a 4 vezes para várias resoluções e otimizações. Essas imagens otimizadas são armazenadas na nuvem e ainda ocupam espaço de armazenamento.

Para ver as imagens reais, você faria:

SELECT * FROM optimized_images

@simon talvez seja necessário atualizar o comando acima para incluir as optimized_images?

[jochen_weber]

@sam

Tivemos um grande bug no passado em que não estávamos contando corretamente o tamanho de todos os uploads.

Ah, isso faz muito sentido. Eu brevemente me perguntei se poderia ser o caso — depois de escrever o post aqui

Hmmm, talvez valha a pena adicionar uma breve frase nesse sentido na barra de armazenamento no painel, para que as pessoas não pensem que alguém está desviando espaço de armazenamento delas com dados desconhecidos

E se você algum dia criar um “Inspeção de Uploads” que não exija os plugins do plano Business, isso seria super apreciado!!

Abraços!

[karagah_majazi]

Finalmente, não entendi como posso ver a lista de uploads dos membros em diferentes tópicos e posts!!??

[robbie.morrison]

Gostaria de navegar pelo diretório de arquivos carregados. Meu caso de uso específico é investigar os detalhes deste URL com link quebrado:

• https://forum.openmod.org/uploads/short-url/1ATLh9pa3Zmjf9V6aKMDRpRIArG.pdf

Qualquer suporte administrativo interpretado para isso seria de grande ajuda. Ou deixei passar algo?
Com os melhores cumprimentos,
R

[robbie.morrison]

Outra solução alternativa é:\n\n- criar e baixar um backup do site com "incluir uploads"\n- navegar até esse arquivo e descompactá-lo, por exemplo, a partir da linha de comando: $ tar -xvzf xxxx.tar.gz\n- mudar para o diretório de uploads: $ cd uploads\n- selecionar entre as seções original e otimizada e explorar a árvore de arquivos resultante\n- nenhum dos nomes de upload está presente, todos os nomes usam strings aleatórias (ou codificadas)\n\nUma interface gráfica no portal de administração proporcionaria uma melhor experiência do usuário. Portanto, meu voto é para essa funcionalidade.\u0026nbsp;R