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

  • 1
    In total pixel area? Larger than a certain width or height? In bytes? "large" is a relative term in more ways than one – Ben Leggiero Jul 27 '17 at 21:47
  • @Prakash, is there an answer below that you can mark as accepted? – Michael West Aug 17 at 14:33
up vote 14 down vote accepted

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']"

Sitecore Query

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
  • Get-Item using query returned only 260 because that's the default value of Query.MaxItems in config (it used to be 100 pre-SC 8.1). fast probably returned that number due to also counting language versions. – jammykam Jul 28 '17 at 4:37
  • 2
    Just wanted mention the fact that caching is heavily affecting the numbers in the performance piece above. From my understanding the first Get-ChildItem is seeding the cache for all the subsequent calls (other than fast: which I don't believe is ever cached). If order and caching (only running this on very rare occasions) is irrelevant and you have a very large result, I almost always default to fast: – vandsh Aug 8 '17 at 14:51

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} }

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.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.