Categories
Sitecore

Querying Sitecore

Often times we run into situations where we need to pull information from Sitecore.  Two of my favorite tools to do this are Sitecore Rocks and Sitecore PowerShell Extensions (SPE).

For those of you who are unfamiliar with these tools, Sitecore Rocks is a Visual Studio extension that makes developing and interacting with Sitecore easy.  SPE is an installed module into Sitecore that gives you a scripting environment and a command line interface.  We will simply be executing queries, but I highly recommend reading their respective documentation sites to learn about their full set of features.

I don’t claim to be a PowerShell guru, so what I’ve done is create simple queries you can run in both Sitecore Rocks and SPE.  You can write pure PowerShell queries, but I find the ones I’ve created to feel overly complex.  So for any query below, you can execute this in SPE with the following:

Get-Item -Path master: -Query "/sitecore/templates/Feature//*[@@templatename = 'Template']"

Or like this so you can easily define the columns into a table:

$items = Get-Item -Path master: -Query "/sitecore/templates/Feature//*[@@templatename = 'Template']"

$items | Format-Table Name, @{ Label = 'Path'; Expression={ $_.Paths.Path } },  @{ Label = 'Std Val'; Expression={ $_["__Standard values"] } }

You can get more information about Working with Items and getting item by XPath on the SPE doc site.  If you have built indexes and aren’t working off of restored databases, a performant way to do this would be finding items using the content search API.

The examples provided below are intended to be run in the Sitecore Rocks Query Analyzer.  I highly recommend reading 28 Days of Sitecore Rocks: Query Analyzer to get started.

Standard Values of Templates
Are there Standard Values on all the templates?

select @@Name as Name, @@Path as Path, @#__Standard values# as #Standard Values# from /sitecore/templates/Feature//*[@@templatename = 'Template']

Types of Fields
Are there fields of type Multilist and what is their source?

select @@Name as Name, @Type, @Source, @@Path as Path from /sitecore/templates/Feature//*[@Type = 'Multilist']

Are there fields of type Treelist and what is their source?

select @@Name as Name, @Type, @Source, @@Path as Path from /sitecore/templates/Feature//*[@Type = 'Treelist']

What are all the fields, their types, and their sources regardless of their template? Order them by Type and Name.

select @@Name as Name, @Type, @@Path as Path from /sitecore/templates/Project//*[@@templatename = 'Template field'] order by Type, Name

List of Templates
Just give me a list of all the templates.

select @@Name as Name, @@Path as Path from /sitecore/templates/Project//*[@@templatename = 'Template']

List of Content Items
Where are all my content items based on the Article template in the Draft workflow state?  The GUID in this example is of the Draft item in a the Sample Workflow.

select @@Name as Name, @@Path as Path from /sitecore/content//*[@@templatename = 'Article' and contains(@#__Workflow state#, "{190B1C84-F1BE-47ED-AA41-F42193D9C8FC}")] order by Path

Rich Text Editor fields with HTML editor profiles defined
What are all the rich-text fields and their sources? (originally from Sitecore John himself)

select @@path, @Source from /sitecore/templates//*[@@templatekey = 'template field' and @type = 'Rich Text'] order by Source

Search for Tokens in Fields
Where are all the single-line text fields with a $name token?

select @@Name as Name, @Type, @@Path as Path from /sitecore/templates//*[@type = 'Single-Line Text' and contains(@title, '$name')]

Explorer Sitecore Rocks Tools
Right next to the Execute Command in the ribbon, there are some really useful tools!Sitecore Rocks Query Analyzer Tools

Insert Fields gives you a list of every field available out of Sitecore.  As it is connected to your Sitecore instance, this means it will pull the fields off of the items specific to your environment!

Sitecore Rocks Query Analyzer Insert Fields

Exporting Data with Sitecore Rocks

One of my favorite features is the ability easily export the results.  Simply right click on the Results tab.  This is invaluable for providing an ad hoc report of items.  For example, we could export the results of the “Article” query we performed.

Sitecore Rocks Query Analyzer Export

For repeatable queries/reports you want Sitecore users to access, consider a different approach with Sitecore PowerShell Extensions Dynamic Reports.

The Big Gotcha

When using a Sitecore Query, it’s important to be cognizant of the configured Query.MaxItems value in the Sitecore configs.  Depending on your version of Sitecore, it may be set to 100 or 260 by default.  I typically set this to 9999 on my local, but I wouldn’t do this on a production environment.

Disclaimer

These queries are intended for developers to gather information.  They are not optimized for performance and should not be used in a production environment.

By Greg Coffman

Technical strategist, agile evangelist, and all-around web nerd. Spends the day as Solution Architect at Sitecore. Thoughts and ideas are my own and do not represent Sitecore.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.