2

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, Func1 ifNotAvailableInCache, Nullable1 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

enter image description here

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


             }
        }
    }
}
0

It looks like your "Most Popular Events" item can not be found. Make sure that your Item /sitecore/system/settings/Conten Testing/COP/Most Popular Events is present on your production environment's Web database and that the ID of the item is '43C07521-4FAF-48EE-B1EF-75EB06C4AF38'.

| improve this answer | |
  • Thanks for the response Mark. I've checked again and confirmed that "Most Popular Events" item is present in my web database and the id is "43C07521-4FAF-48EE-B1EF-75EB06C4AF38". – user6500 Jan 15 at 1:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.