Reading Play log information directly from the Myriad Playout SQL Database

Introduction

Sometimes it's desirable for 3rd party products to be able to read information directly from the Myriad Playout Database, for example an external Advert/Traffic scheduler might want to read additional information directly from the Play history.

Important note:

Directly accessing the SQL database CAN be dangerous if not done extremely carefully and Broadcast Radio will NOT be able to provide support for issues arising from anything other than Myriad accessing the database directly.

You must never WRITE any data directly to the Myriad Database, and should always use the Myriad tools available to do this.  Any READs should be done with no "locking" to prevent any performance issues.

Example SQL

This example reads Play log information directly from the PlayLogs table.

You set the date/time range you are interested in via the 2 DateTime variables at the top. Remember to convert your local time into UTC before calling this query.

In this example, we are only interested in returning Adverts, so we are using an ItemType filter to only return items with a type of 4, which is the Type number for Adverts.

DECLARE @AdvertItemType Int = 4;
DECLARE @StartDateTimeUtc DateTime = '20231214 00:00:00'
DECLARE @EndDateTimeUtc   DateTime = '20231214 23:59:59'

SELECT  PlayLogs.StationID, PlayLogs.StartDateTimeUtc, PlayLogs.EndDateTimeUtc, PlayLogs.PlayedDuration, PlayLogs.MediaId, PlayLogs.MediaLength, PlayLogs.ExtSchedulerReference, 
                 ItemTitles.ItemTitle, Artists.ArtistName

FROM    PlayLogs LEFT OUTER JOIN
                 Artists ON PlayLogs.FirstArtistId = Artists.ArtistId LEFT OUTER JOIN
                 ItemTitles ON PlayLogs.ItemTitleId = ItemTitles.ItemTitleId

WHERE  (PlayLogs.ItemType = @AdvertItemType)
          AND (PlayLogs.OnAirMode = 1) AND (PlayLogs.PreviewMode = 0)
          AND (PlayLogs.StartDateTimeUtc>=@StartDateTimeUtc) AND (PlayLogs.EndDateTimeUtc<=@EndDateTimeUtc)

ORDER BY StartDateTimeUtc