Report the 50 largest images on your system

Description

This is just a nice utility/tool to help locate all those massive images people upload without thinking about the size. This makes it easy for a graphics person to find the oversized images and replace them, without requiring them to have full administrator access.

It also helps you to find 'problem users' who consistently upload images that are too large.

It uses SQL to query the system for the info; the file structure might change in newer versions (or other SQL variants) - this version is for 1.9.2 and postgres. 

It does some aggregate math at the bottom to give you some more information.

You might want to consider putting this on an unpublished page, so that only system users can find it/use it.

 

Code

#set ($query="select * from file_asset WHERE mime_type LIKE '%jpeg%' and live='t' and working='t' ORDER BY file_size DESC limit 50;")


##Query below for ALL files - not just jpegs
##set ($query="select * from file_asset WHERE  live='t' and working='t' ORDER BY file_size DESC limit 50;")

#getSQLResults($query)
$SQLError


<h2>Top 50 images by size</h2>
<style>
td {padding:5px;}  
  th {font-weight:bold}  
  </style>

<table>
<tr>
<th>file_name</th>
<th>file_size</th>
<th>  author</th>
<th>  mod_date</th>
<th>  mod_user</th>
 </tr>  
  
  
  
  
#set ($mather=$math.toInteger(0))
#foreach ($myvar in $results)
#set ($inmath=$math.toInteger($myvar.get("file_size")))
#set ($mather=$mather+$inmath)
<tr>
  <td>
  $myvar.get("file_name")
  </td>
  <td>
  $math.roundTo(3,$math.div($myvar.get("file_size"),1048576)) MB
  </td>
  <td>
  $myvar.get("author")
  </td>
  <td>
  $myvar.get("mod_date")
  </td>  
    <td>
  $myvar.get("mod_user")
  </td>
  
  
  </tr>
#end
</table>



#set ($query="select COUNT(file_size) from file_asset where file_size >100000 and mime_type LIKE '%jpeg%';")

#getSQLResults($query)
$SQLError


#foreach ($myvar in $results)
<strong>Files > 1MB : $myvar.get("count")</strong><br>

#end



<h2>Average size of top 50: $math.roundTo(3,$math.div($mather,52428800)) MB</h2>