Exporting an Episerver Find report

There is no out-of-the box way to export the Episerver Find statistics (shown on the Manage tab) to a convenient report, which means that copying and pasting is the best way to extract all the frequent searches, searches without hits and searches without relevant hits.

This is data that editors frequently want to get access to, so, wouldn't it be convenient to wrap it up in a simple report which would make generating it as simple as entering two dates and clicking a button? Ok, that was a rhetorical question because that's what this blog post is about, and I've already created it:

Episerver Find report UI

Here I'll run through a few of the required pieces in putting it togetherbut if you just what the code you can find it all in a gist by clicking here.

Creating the Top Queries Command

I started off looking at the API calls that the statistics pages are making, they're all calling the same endpoint (EPiServer/Find/[index]/_stats/query/top) whilst specifying a different type.

I checked and there was no Command class for getting top queries already (I also saw this old forum post which essentially confirmed it), but from our perspective it doesn't make things much harder because we only have to create one:

internal class TopQueryCommand : Command
{
    private readonly string _index;
    
    private readonly DateTime _from;
    private readonly DateTime _to;
    private readonly int _size;
    private readonly string _type;

    public TopQueryCommand(ICommandContext commandContext, string index, DateTime from, DateTime to, int size, Type type) : base(commandContext)
    {
        _index = index;
        _from = from;
        _to = to;
        _size = size;
        _type = GetType(type);
    }

    public StatisticsHitsResult<TopQueryResult> Execute()
    {
        string url = GetUrl();
        var request = CommandContext.RequestFactory.CreateRequest(url, HttpVerbs.Get, ExplicitRequestTimeout);
        return GetResponse<StatisticsHitsResult<TopQueryResult>>(request);
    }

    private string GetUrl()
    {
        return $"{GetServerUrl()}{_index}/_stats/query/top?from={_from:s}Z&to={_to:s}Z&size={_size}&type={_type}";
    }

    private string GetType(Type type)
    {
        switch (type)
        {
            case Type.Top:
                return "top";
            case Type.NoHits:
                return "null";
            case Type.NoRelevantHits:
                return "nullclick";
            default:
                return "top";
        }
    }
}

All we really need to do here is build the URL and parameters based on the dates, size and type passed in through the constructor—the rest is much the same for most of the other commands.

Creating a Find Report Client

The next step is creating a convenient way to execute the command for the different types (which I defined in an enum). This ended up extremely simple, in this example it's even simpler as the three methods it actually has are virtually the same, so I cut them for brevity (again, the full version is here):

[ServiceConfiguration(ServiceType = typeof(IEpiserverFindReportClient))]
public class EpiserverFindReportClient : IEpiserverFindReportClient
{
    private readonly IClient _client;

    public EpiserverFindReportClient(IClient client)
    {
        _client = client;
    }

    public IReadOnlyList<IQueryCount> GetQueriesWithoutHits(DateTime from, DateTime to)
    {
        var topQueryCommand = _client.NewCommand(context => new TopQueryCommand(context, _client.DefaultIndex, from, to, 100, Type.NoHits));
        return topQueryCommand.Execute().Hits.Select(x => new QueryCount(x.Query, x.Count))
            .ToSafeReadOnlyCollection();
    }
}

The one thing to note, is the API this command interfaces with can only return a maximum of 100 results which is why I set the size to 100 here. This means if any tabs in the resulting report return more than 100 it's necessary to reduce the date range. This is an improvement that could be made in code because it could call the API multiple times and concatenate the reports, but I'll leave that to someone who requires it.

Creating the Plugin Code-Behind

The last bit that I'd like to call out is the code-behind for our plugin, which generates the Excel file and registers the plugin for the report menu using the GuiPlugIn Attribute:

[GuiPlugIn(Area = PlugInArea.ReportMenu,
    DisplayName = "Find Report",
    Description = "Generate and export Episerver Find reports",
    Url = "~/FindReport/FindReportGenerator.aspx",
    Category = "Episerver Find",
    SortIndex = 5000)]
[Authorize(Roles = "Administrators, WebAdmins, WebEditors")]
public partial class FindReportGenerator : WebFormsBase
{
    // A lot of methods removed here

    protected void Generate(object sender, EventArgs e)
    {
        if (!Page.IsValid)
        {
            return;
        }

        var fromDate = GetFromDate();
        var toDate = GetToDate();

        if (!fromDate.HasValue)
        {
            return;
        }

        IDictionary<string, IReadOnlyList<IQueryCount>> results = new Dictionary<string, IReadOnlyList<IQueryCount>>();

        if (includeTopQueriesInput.Checked)
        {
            results.Add("Most frequent searches", _findReportClient.Service.GetTopQueries(fromDate.Value, toDate));
        }

        if (includeQueriesWithoutHits.Checked)
        {
            results.Add("Searches without hits", _findReportClient.Service.GetQueriesWithoutHits(fromDate.Value, toDate));
        }

        if (includeQueriesWithoutRelevantHits.Checked)
        {
            results.Add("Searches without relevant hits", _findReportClient.Service.GetQueriesWithoutRelevantHits(fromDate.Value, toDate));
        }

        using (var package = new ExcelPackage())
        {
            foreach (var result in results)
            {
                var ws = package.Workbook.Worksheets.Add(result.Key);

                // Add the column headings
                ws.Cells[1, 1].Value = "Query";
                ws.Cells[1, 1].Style.Font.Bold = true;
                ws.Cells[1, 2].Value = "Count";
                ws.Cells[1, 2].Style.Font.Bold = true;

                var row = 2;

                foreach (var query in result.Value)
                {
                    ws.Cells[row, 1].Value = query.Query;
                    ws.Cells[row, 2].Value = query.Count;

                    row++;
                }
            }

            var packageBytes = package.GetAsByteArray();

            var filename = fromDate.Value.Date.Equals(toDate.Date)
                ? $"{toDate:yyyyMMdd}_find_report.xlsx"
                : $"{fromDate.Value:yyyyMMdd}-{toDate:yyyyMMdd}_find_report.xlsx";

            Response.Clear();
            Response.ClearHeaders();
            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            Response.AddHeader("Content-Disposition", $"attachment; filename={filename}");
            Response.OutputStream.Write(packageBytes, 0, packageBytes.Length);
            Response.Flush();
            Response.End();

        }
    }
}

Based on what checkboxes the user has selected in the UI the Generate method calls the various client methods, executing commands with different types specified. In then adds the result to different sheets in the outputted report which is generated with EPPlus.

The result!

That's all the key bits which go into generating the report, hopefully that gives some insight into how it comes together.

The resulting report should look like the below:

Episerver Find report output

Comments

There are zero comments 😢