I have a requirement to track the most viewed pages in last month in our Sitecore 9.0.1. I have followed the answer here https://theroks.com/use-sitecore-analytics-show-number-pageviews/ and tried to get it from Fact_PageViews table in Reporting database but in our production environment i get null reference exception in the ReportDataProvider in the GetData function. This functionality is working as expected in our Development environment Sitecore 9.0.1 onprem. Where as the Production instance hosted in Azure is having the issues finding the ReportQuery item.
Here is the Log from Content Management (CM) production server
ManagedPoolThread #4 09:26:02 ERROR ExceptionException: System.Reflection.TargetInvocationExceptionMessage: Exception has been thrown by the target of an invocation.Source: mscorlibat System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)at Sitecore.Reflection.ReflectionUtil.InvokeMethod(MethodInfo method, Object[] parameters, Object obj)at Sitecore.Jobs.JobRunner.RunMethod(JobArgs args)at (Object , Object[] )at Sitecore.Pipelines.CorePipeline.Run(PipelineArgs args)at Sitecore.Pipelines.DefaultCorePipelineManager.Run(String pipelineName, PipelineArgs args, String pipelineDomain)at Sitecore.Jobs.Job.DoExecute()at Sitecore.Jobs.Job.ThreadEntry(Object state) Nested Exception
Exception: System.Reflection.TargetInvocationExceptionMessage: Exception has been thrown by the target of an invocation.Source: mscorlibat System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)at Sitecore.Reflection.ReflectionUtil.InvokeMethod(MethodInfo method, Object[] parameters, Object obj)at Sitecore.Tasks.CommandItem.Execute(Item[] items, ScheduleItem schedule) Nested Exception
Exception: System.ArgumentNullExceptionMessage: There is no item with ID 43C07521-4FAF-48EE-B1EF-75EB06C4AF38.Parameter name: itemSource: Sitecore.Framework.Conditionsat Sitecore.Xdb.Reporting.Datasources.Remote.RemoteReportDataSourceProxy.GetData(ReportDataQuery query)at Sitecore.Xdb.Reporting.ReportDataProvider.<>c__DisplayClass18_0.b__1()at Sitecore.Xdb.Reporting.ReportDataProvider.GetCachedDataForQuery(ReportDataQuery query, Func
1 ifNotAvailableInCache, Nullable
1 ifModifiedSince)at Sitecore.Xdb.Reporting.ReportDataProvider.ExecuteQueryWithCache(ReportDataQuery query, ReportDataSource dataSource, CachingPolicy cachingPolicy)at Sitecore.Xdb.Reporting.ReportDataProvider.GetData(String dataSourceName, ReportDataQuery query, CachingPolicy cachingPolicy)at Sitecore.Xdb.Reporting.ReportingQueryBase.ExecuteQuery(Dictionary`2 parameters)at Perth.Sitecore.Tasks.MostPopularEvents.VisitsQuery.Execute() in C:\Projects\Sitecore.Solution\Tasks\MostPopularEvents\VisitsQuery.cs:line 47at Perth.Sitecore.Tasks.MostPopularEvents.SyncGenericEventsTask.ExecuteTask(Item[] items, CommandItem command, ScheduleItem schedule) in C:\Projects\Sitecore.Solution\Tasks\MostPopularEvents\SyncGenericEventsTask.cs:line 44
The error System.ArgumentNullExceptionMessage: There is no item with ID 43C07521-4FAF-48EE-B1EF-75EB06C4AF38. Below is the Report query item present in CM
Query
SELECT
[ItemId], COUNT(*) as cnt
FROM
[Fact_PageViews]
WHERE
Date > DATEADD(DAY, @timeSpan, GETDATE())
GROUP BY
ItemId
ORDER BY
cnt DESC
Code
VisitsQuery.cs
public class VisitsQuery : ItemBasedReportingQuery
{
public VisitsQuery(ID queryItemId, ReportDataProviderBase reportProvider = null, CachingPolicy cachingPolicy = null) : base(queryItemId, reportProvider, cachingPolicy)
{
QueryItemId = queryItemId;
}
public ID ItemId { get; set; }
public int NumberOfItems { get; set; }
public int Timespan { get; set; }
public long Visits { get; protected set; }
public List<Guid> Results;
public override void Execute()
{
var webDb = SitecoreConfig.Factory.GetDatabase("web");
Results = new List<Guid>();
var parameters = new Dictionary<string, object> //line 47
{
{
"@TimeSpan", Timespan
}
};
var dt = this.ExecuteQuery(parameters);
if (dt != null && dt.Rows.Count > 0)
{
//ItemID1 = Guid.Parse(dt.Rows[0].ItemArray[0].ToString());
for (var i =0; i <= dt.Rows.Count; i++)
{
var rowId = Guid.Parse(dt.Rows[i].ItemArray[0].ToString());
if (rowId == new Guid("{00000000-0000-0000-0000-000000000000}"))
{
continue;
}
//check if this is an Event template
var item = webDb.GetItem(new ID(rowId));
if (item == null)
{
continue;
}
if (item.TemplateID.ToString() == "{8085E99E-435B-47F7-9959-F6045CF0C78D}")
{
Results.Add(rowId);
}
if (Results.Count == 3)
{
break;
}
}
}
}
//Task Scheduler
}
}
SyncGenericEventsTask.cs
public class SyncGenericEventsTask
{
public void ExecuteTask(Item[] items, CommandItem command, ScheduleItem schedule)
{
Log.Debug("------- STARTING ---------------");
var provider = (ReportDataProvider)Factory.CreateObject("reporting/dataProvider", true);
var query = new VisitsQuery(new ID("{43C07521-4FAF-48EE-B1EF-75EB06C4AF38}"), provider)
{
Timespan = -30
};
query.Execute();
var result1 = query.Results; //line 44
}
}
}
}