Stats REST API seems to be broken - 1.8.0-alpha2

Developer Experience
1

The API that returns Statistics api/stats is retruning nothing though for the same date range, I get some stats from the CMS UI’s Statistics link.

curl -X GET -H “Authorization: token” -H “Cache-Control: no-cache” ‘http://MyXiboSvr/api/stats?fromDt=2015-12-04 00:00:00&toDt=2016-02-04 00:00:00’

I am not sure if the date format I am using in the curl above is wrong. I also noticed that the SQL query that is built in the grid() method of Stats.php has an error in the SQL syntax. The SQL looks like below in my case. I think the ‘option’ column of widgetoption table being a reserved MySQL word, needs to be escaped by backticks as well.

I tried to change it locally in my CMS instance but still get no rows returned. Probably there something more I am missing.

SELECT stat.type, display.Display, layout.Layout, IFNULL(media.name, IFNULL(widgetoption.value, CONCAT(widget.type, ‘-’, widget.widgetId))) AS Name, COUNT(StatID) AS NumberPlays, SUM(TIME_TO_SEC(TIMEDIFF(end, start))) AS Duration, MIN(start) AS MinStart, MAX(end) AS MaxEnd FROM stat INNER JOIN display
ON stat.DisplayID = display.DisplayID INNER JOIN layout ON layout.LayoutID = stat.LayoutID
LEFT OUTER JOIN widget ON widget.widgetId = stat.MediaID LEFT OUTER JOIN widgetoption
ON widgetoption.widgetId = widget.widgetId AND widgetoption.type = ‘attrib’
AND widgetoption.option = ‘name’ LEFT OUTER JOIN lkwidgetmedia
ON lkwidgetmedia.widgetId = widget.widgetId LEFT OUTER JOIN media
ON media.mediaId = lkwidgetmedia.mediaId AND media.type <> ‘module’ WHERE stat.type <> ‘displaydown’
AND stat.end > ‘2015-12-04 00:00:00’ AND stat.start <= ‘2016-02-04 00:00:00’ AND stat.displayID IN (151,152,153,150)
GROUP BY stat.type, display.Display, layout.Layout, CONCAT(widget.type, ‘-’, widget.widgetId) ORDER BY stat.type, display.Display, layout.Layout, Name

  • Regards
2

It was my mistake. “Enable Stats Reporting” was disabled in my Default Display Settings profile. Once I enabled it, I start getting the Stats via the REST API.
The widgetoption.option being not back quoted which I observed earlier seems to have no effect when CMS runs the query. I noticed this Syntax error when I captured the SQL and tried to run it from my client (MySQL Workbench). So I guess we are all good here.

The results look like below:.

…
{
“type”: “Media”,
“display”: “Test Display A”,
“layout”: “layout1”,
“media”: “image1”,
“numberPlays”: 23,
“duration”: 230,
“minStart”: “2016-01-09 15:06:06”,
“maxEnd”: “2016-01-09 15:20:58”
},
…

Sorry for the confusion and thanks for a great product

1 Like