Hi,

To the best of my knowledge, there's not a table or view which stores the resolved URLs in a ready-to-go format. There's a bit of logic required to construct the URL but, at its most basic, the URL for a given page is constructed by combining the URL segments of the page's ancestors. Depending on configuration, a language segment or host and protocol may need to be be prepended.

If you needed to do that in SQL you may be able to encapsulate the basic logic in a function but it probably wouldn't be that efficient if you're trying to get a load of page URLs all at once. I suspect your best bet would be to extract the urls separately using either a custom script calling the supported APIs (e.g. UrlResolver) or, if you have it installed, you could potentially use the content delivery API. If you've got an XML sitemap generator like Geta Optimizely Sitemaps, that may also be an option for you.

Hi,

Touch the sql database and playing around with it is going to take a long time to get it right.

If you want some quick and easy then it would be better to use the API's in Optimizely, something like this could help:

public class OptiPageService
{
    private readonly IContentLoader _contentLoader;

    public OptiPageService()
    {
        _contentLoader = ServiceLocator.Current.GetInstance<IContentLoader>();
    }

    public IEnumerable<string> GetExternalUrlsOfPageType<T>() where T : PageData
    {
        var externalUrls = new List<string>();

        var descendents = _contentLoader.GetDescendents(ContentReference.RootPage);

        foreach (var descendent in descendents)
        {
            _contentLoader.TryGet<T>(descendent, out var page);

            if(page !=null)
            {
                var externalUrl = GetExternalUrl(page, true, "en");

                if (!string.IsNullOrEmpty(externalUrl))
                {
                    externalUrls.Add(externalUrl);
                }
            }
        }


        return externalUrls;
    }

    private string GetExternalUrl(PageData page, bool absoluteUrl, string languageBranch)
    {
        var result = ServiceLocator.Current.GetInstance<IUrlResolver>().GetUrl(page.ContentLink, languageBranch, new VirtualPathArguments
        {
            ContextMode = ContextMode.Default,
            ForceCanonical = absoluteUrl
        });

        if (absoluteUrl)
        {
            Uri relativeUri;

            if (Uri.TryCreate(result, UriKind.RelativeOrAbsolute, out relativeUri))
            {
                if (!relativeUri.IsAbsoluteUri)
                {
                    var siteDefinitionResolver = ServiceLocator.Current.GetInstance<ISiteDefinitionResolver>();
                    var siteDefinition = siteDefinitionResolver.GetByContent(page.ContentLink, true, true);
                    var hosts = siteDefinition.GetHosts(page.Language, true);
                    var host = hosts.FirstOrDefault(h => h.Type == HostDefinitionType.Primary) ?? hosts.FirstOrDefault(h => h.Type == HostDefinitionType.Undefined);

                    var basetUri = siteDefinition.SiteUrl;

                    if (host != null)
                    {
                        Uri.TryCreate(siteDefinition.SiteUrl.Scheme + "://" + host.Name, UriKind.Absolute, out basetUri);
                    }

                    var absoluteUri = new Uri(basetUri, relativeUri);

                    return absoluteUri.AbsoluteUri;
                }
            }
        }

        return result;
    }
}

Then use like this:

var service = new OptiPageService();
var urls = service.GetExternalUrlsOfPageType<StartPage>();

You can change the 'StartPage' to the specific page you need to export.

Then all you need to do is use something like CSVHelper, to out to a csv file.

Let me know if that helps