Pages Analytics error

Mark Elliott asked on August 31, 2018 17:46

Trying to run some analytics on a few of our pages and getting SQL errors when I go to Pages - Analytics - Reports It's throwing invalid SQL syntax errors so I dug into what SQL statements are being executed. It's not putting in the field names in the statement for some reason (as seen where I've bolded below). I'm just at a loss as to why and wondered if anyone had any ideas?

SET @FromDate =dbo.Func_Analytics_DateTrim(@FromDate,'');

SELECT AS StartTime , T1.Hits AS Time FROM dbo.Func_Analytics_EnsureDates (@FromDate,@ToDate,'') AS Dates LEFT JOIN (SELECT HitsStartTime AS StartTime, SUM(HitsValue) / SUM(HitsCount) AS hits FROM Analytics_Statistics INNER JOIN ON .HitsStatisticsID = Analytics_Statistics.StatisticsID WHERE (StatisticsSiteID = @CMSContextCurrentSiteID) AND (StatisticsCode = 'avgtimeonpage') AND StatisticsObjectID = @NodeID AND StatisticsObjectCulture = @CultureCode GROUP BY HitsStartTime) AS T1 ON T1.StartTime = [Date]

pages analtics

Correct Answer

Brian McKeiver answered on September 4, 2018 23:38

Did any of the admin user accounts of the site get deleted? Typically it is that or something happens with the macro salt value in the web.config not being the same across environments. The docs about macro signatures may be helpful on it too. Since you are on 11 I would also consider switching to using macro signature identities.

0 votesVote for this answer Unmark Correct answer

Recent Answers

Brian McKeiver answered on September 1, 2018 16:33

Mark,

If that query is not generating correctly, you may have Macro Signing errors. Can you run the Macro security report (System -> Macros -> Report) and see if it reports any errors? If so you may need to re-sign your macros for your site. I'm not 100% this is it, but it is definitely something to check.

0 votesVote for this answer Mark as a Correct answer

Mark Elliott answered on September 4, 2018 17:17

Brian,

Yes there are quite a few (over 600) Macros with invalid signatures. All the macros were just re-signed a couple months ago when we upgraded to 11 How could so many of the macro signatures be invalidated so quickly?

0 votesVote for this answer Mark as a Correct answer

Mark Elliott answered on September 5, 2018 17:02

No the admin user accounts haven't been changed, either in dev or production. The salt value is the same in both environments as well. I'll try re-signing all the macros again and investigate using macro signature identities.

0 votesVote for this answer Mark as a Correct answer