Can someone help me with the Sitecore Powershell query which I can use for finding the images more than the given size.
3 Answers
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 byWhere-Object
returned 1764 items : 8.62 secondsGet-Item
followed byAxes
returned 1764 items : 2.08 secondsGet-Item
using query returned 260 items (Query.MaxItems
setting) : .6 secondsGet-Item
using fast query returned 6240 items : 9.2 secondsSql Query
returned 1764 items : 5.1 secondsContent 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 ofQuery.MaxItems
in config (it used to be 100 pre-SC 8.1).fast
probably returned that number due to also counting language versions.– jammykamJul 28, 2017 at 4:37 -
2Just 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 thanfast:
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 tofast:
– vandshAug 8, 2017 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.