Sitecore Powershell query for big images of a certain size

Question

Can someone help me with the Sitecore Powershell query which I can use for finding the images more than the given size.

Answer 1

August 2019 Update

There is a report included with SPE allowing you to find images by a specific size.

January 2018 Update - 6 months later

So we've added a few more websites with media to our database. Updated results are below. I've also added a SQL Query example with looks promising but may give some people heartburn.

Example: Use Sitecore Query to find the content.

# Look for media greater than 500k in size.
Get-Item -Path "master:" -Query "/sitecore/media library/Images//*[@size > '500000']"

Query Performance

A quick non-scientific test produced the following timing

• Get-ChildItem followed by Where-Object returned 1764 items : 8.62 seconds
• Get-Item followed by Axes returned 1764 items : 2.08 seconds
• Get-Item using query returned 260 items (Query.MaxItems setting) : .6 seconds
• Get-Item using fast query returned 6240 items : 9.2 seconds
• Sql Query returned 1764 items : 5.1 seconds
• Content Search API returned ~3000 items (need to retest) : 2.1 seconds

Thoughts

I did not expect how fast the Axes command could be when searching recursively. I will have to try that out in more of the reports we have in SPE. Marek and Hishaam both gave some good examples at how you can achieve it. I hope that you will find the below queries useful when trying to determine an optimal query for your use case.

Performance Measurements

Run these queries in the ISE if you want to see an output of how long each command took to run. Substitute your own filters to see which meets your requirements.

Measure-Command {
    $items = Get-ChildItem -Path "/sitecore/media library" -Recurse | Where-Object { ($_.Size / 1) -gt 100000 }
} | Select-Object -Expand TotalSeconds

Measure-Command {
    # Call Initialize-Item so that SPE adds the automatic properties.
    $mediaItemContainer = Get-Item "master:/media library"
    $items = $mediaItemContainer.Axes.GetDescendants() | Where-Object { [int]$_.Fields["Size"].Value -gt 100000 } | Initialize-Item
} | Select-Object -Expand TotalSeconds

Measure-Command {
    $items = Get-Item -Path "master:" -Query "/sitecore/media library//*[@size > '100000']"
} | Select-Object -Expand TotalSeconds

Measure-Command {
    $items = Get-Item -Path "master:" -Query "fast:/sitecore/media library//*[@size > '100000']"
} | Select-Object -Expand TotalSeconds

Measure-Command {
    Import-Function -Name Invoke-SqlCommand
    $database = Get-Database -Name "master"
    $connection = [Sitecore.Configuration.Settings]::GetConnectionString($database.Name)
    $sizeFieldId = "{6954B7C7-2487-423F-8600-436CB3B6DC0E}"
    $parameters = @{
        "size" = 100000
        "fieldId" = $sizeFieldId
    }

    $query = "SELECT [ItemId] ,[Value] FROM [dbo].[SharedFields] WHERE FieldId = @fieldId AND [Value] > @size UNION ALL SELECT [ItemId] ,[Value] FROM [dbo].[VersionedFields] WHERE [FieldId] = '{5BE6C122-84C9-4661-A0C9-3718909C8DAD}' AND [Value] > @size"
    $itemIds = Invoke-SqlCommand -Connection $connection -Query $query -Parameters $parameters | Select-Object -ExpandProperty "ItemId"
    $items = $itemIds | ForEach-Object { Get-Item -Path "master:" -ID $_ }
} | Select-Object -Expand TotalSeconds

Content Search API

After much trial and error, I found a way to use the Content Search API to query the items. In the following example, I had to change the field type from Single-line text to Number on the File data template. SOLR then interpreted the field as a float. If you want to use this approach consider creating a computed field instead so you can leave the File template alone.

$code = @"
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;

using Sitecore.ContentSearch;
using Sitecore.ContentSearch.Linq;
using Sitecore.ContentSearch.Linq.Utilities;
using Sitecore.ContentSearch.SearchTypes;
using Sitecore.ContentSearch.Utilities;

namespace Demo {
    public class ExtendedSearchResultItem : Sitecore.ContentSearch.SearchTypes.SearchResultItem {
        [IndexField("size")]
        public float Size { get; set; }
    }

    public class SearchRunner {
        public IEnumerable<ExtendedSearchResultItem> DoSearch() {
            using (var context = ContentSearchManager.GetIndex("sitecore_master_index").CreateSearchContext())
            {
                var predicate = PredicateBuilder.False<ExtendedSearchResultItem>();
                predicate = predicate.Or(i=> i.Size > 100);
                return context.GetQueryable<ExtendedSearchResultItem>().Where(predicate).ToList();
            }
        }
    }
}
"@

Add-Type -TypeDefinition $code -ReferencedAssemblies @("Sitecore.Kernel", "Sitecore.ContentSearch", "Sitecore.ContentSearch.Linq")

$search = New-Object Demo.SearchRunner
Measure-Command {
    $items = $search.DoSearch()
} | Select-Object -Expand TotalSeconds

Answer 2

This should do the trick

# Call Initialize-Item so that SPE adds the automatic properties.
$mediaItemContainer = Get-Item "master:/media library"
$items = $mediaItemContainer.Axes.GetDescendants() | Where-Object { [int]$_.Fields["Size"].Value -gt 100000 } | Initialize-Item

And if you want to see a nice report use:

# Call Initialize-Item so that SPE adds the automatic properties.
$mediaItemContainer = Get-Item "master:/media library"
$items = $mediaItemContainer.Axes.GetDescendants() | Where-Object { [int]$_.Fields["Size"].Value -gt 100000 } | Initialize-Item

if ($items.Count -eq 0){
    Show-Alert "There are no big media items"
} else {
    $props = @{
    InfoTitle = "Big media items"
    InfoDescription = "Lists all media items which are bigger than 100000 bytes."
    PageSize = 25}
}

$items |
    Show-ListView @props -Property @{Label="Name"; Expression={$_.DisplayName} },
        @{Label="Updated"; Expression={$_.__Updated} },
        @{Label="Updated by"; Expression={$_."__Updated by"} },
        @{Label="Size"; Expression={$_.Size} },
        @{Label="Path"; Expression={$_.ItemPath} }

Answer 3

Please find below the script you may use to get the images which their size is greater than a defined value.

## Change the path your your specific container and also the language
$mediaItemContainer = Get-Item "master:/media library" -Language en-GB

$media = $mediaItemContainer.Axes.GetDescendants()

ForEach($mediaItem in $media){

    $size = [int]$mediaItem.Fields["Size"].Value

    ## Change the value 20 to your required size
    if($size -gt 20){
        $mediaItem.Name
    }
}

Note that the value "20" is in Byte.