In Ektron, how can I find all pages where a particular widget has been included with a particular configuration?

Question

I'm working with an Ektron 8.6 installation with a phenomenal amount of content (i.e. tens of thousands of pages), and trying to identify which pages have included a certain widget, where that widget is misconfigured. Unfortunately, I have a low amount of hands-on experience with Ektron, and I am struggling to work out how to find these pages.

A large number of pages have this widget. So far, out of manually checking a couple of hundred, I've identified two offending pages where it's miconfigured. I must find the rest of the pages, if there are any, which there probably are.

What I have access to

I have a local copy of this installation, can connect to its database to run queries via SQL Server Management Studio 2008, and I can enter the CMS work area.

What I know so far

The widget itself is named Taxonomy widget. It has a display type of tertiary menu. The misconfigured widgets have a checkbox named "include summary" ticked.

From the Internet Archive Wayback Machine, I can see the offending pages only entered a misconfigured state in October and December of 2013.

I know the full URLs for the pages. (I found them by clicking around links on the website, after all.)

I've hunted through the database and found the following:

• From our content table, I've found the rows that store the main content for the two offending pages. I know their content IDs.
• From our page_to_object table: I've found the page IDs corresponding to these content IDs.
• Since I already know the pages' aliases, I've identified their rows in our UrlAliasMapping table.
• There's a widget_types table which contains a row where the title is FOO_Taxonomy (where FOO is the organisation's name), and another row where the title is TaxonomySummary. These may or may not be relevant.

... but from here I'm stuck.

What I think I need to do (but I might be going about this wrong)

I think the most efficient way to do this would be through the database, with a query to find:

• Any instances of the Taxonomy Widget,
• Where their configuration is such and such,
• Joined to whatever pages have these widgets,
• Joined to whatever the URL aliases are for these pages.

But whilst I can write a SQL query to join a URL alias to its page_to_object mapping to the actual content object, I have no idea how to link any of this stuff to instances of the widget I'm looking for.

I'm open to methods that don't involve the database.

So in summary.

I need all the pages where there's a Taxonomy widget, and it has the "include summary" checkbox ticked.

What can I do to find these pages?

Answer 1

This info is valid for v9.1, but I can't imagine a whole lot would have changed in how pagebuilder pages are stored.

Each page builder page is stored in the content table of the database. This query seems to pull up all pagebuilder pages:

select * from content where content_type = 1 and content_subtype = 1

The content_html is an xml document that describes the page. This xml will have all the settings for each widget on the page. Here's a simplified version of the xml structure. There's a lot more in there, but here's what you would need to check the settings of a widget:

<PageData xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="PB">
    <Zones> ... </Zones>
    <Widgets>
        <WidgetData>
            <ID xmlns="PW">58</ID>
            <ControlURL xmlns="PW">Name_of_widget.ascx</ControlURL>
            <Settings xmlns="PW">&lt;ArrayOfDataStore xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"&gt;&lt;dataStore&gt;&lt;Property&gt;ContentBlockId&lt;/Property&gt;&lt;Value xsi:type="xsd:long"&gt;612&lt;/Value&gt;&lt;TypeName&gt;System.Int64&lt;/TypeName&gt;&lt;AssemblyAndType&gt;System.Int64, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089&lt;/AssemblyAndType&gt;&lt;/dataStore&gt;&lt;/ArrayOfDataStore&gt;</Settings>
            <DropID>dz1</DropID>
            <Associations>
                <ObjectData>
                    <ObjectId>612</ObjectId>
                    <ObjectType>Content</ObjectType>
                    <ObjectLanguage>1033</ObjectLanguage>
                </ObjectData>
            </Associations>
        </WidgetData>
    </Widgets>
</PageData>

The <Associations> portion of the xml comes from the part of your widget (if yours does this), typically in the Save event handler of the edit view, where you have code similar to this:

if (Host != null)
{
    var commonApi = new Ektron.Cms.CommonApi();
    var objectData = new ObjectData
    {
        ObjectId = ContentBlockId,
        ObjectLanguage = commonApi.UserLanguage,
        ObjectType = EkEnumeration.CMSObjectTypes.Content
    };

    Host.PBWidgetInfo.Associations.Clear();
    Host.PBWidgetInfo.Associations.Add(objectData);

    Host.SaveWidgetDataMembers();
}

It sounds like you are going to want to examine the <Settings> node of any widget where <ControlURL> has a value equal to the name of your widget's ascx filename. The value of the <Settings> node is an xml-escaped document. If you decode the settings value from my above example, you'll end up with the following:

<ArrayOfDataStore xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <dataStore>
        <Property>ContentBlockId</Property>
        <Value xsi:type="xsd:long">612</Value>
        <TypeName>System.Int64</TypeName>
        <AssemblyAndType>System.Int64, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</AssemblyAndType>
    </dataStore>
</ArrayOfDataStore>

So let's assume your widget is Taxonomy.ascx. You could find every page that has this widget by using the following query:

select * from content
where content_type = 1 and content_subtype = 1
and content_html like N'%<ControlURL xmlns="PW">Taxonomy.ascx</ControlURL>%'

From there, you can examine the settings and take the appropriate action. I hope this helps!

update:

If the number of results is too large to look through manually, you can add an extra condition to your where clause. If you're looking for a property called ContentBlockId whose value is 612, something like this would get you closer to the result set you want. Keep in mind, however, that this may include some false positives. It doesn't guarantee that the setting ContentBlockId belongs to the particular widget you're looking for, only that a widget on the page has that property with the specified value, and that the widget you're looking for also exists on the page.

Also, you can cast the content_html field as XML. That can sometimes help in cases like this, since SQL Server Management Studio will format the field and allow you to open that field's xml in a separate window with nice formatting.

select cast(content_html as xml) as 'content_xml', *
from content where content_type = 1 and content_subtype = 1
and content_html like N'%<ControlURL xmlns="PW">Edison_Content.ascx</ControlURL>%'
and content_html like N'%&lt;Property&gt;ContentBlockId&lt;/Property&gt;&lt;Value xsi:type="xsd:long"&gt;612&lt;/Value&gt;%'

Your query will be different, as it sounds like you're looking for a boolean field, but this should get you most of the way there.