Proof-of-play Logging in Moxie

Summary

Moxie has a log system that tracks the display of content and records it in a database. This database can then be queried to create reports containing data related to content displayed during specific time-spans.

Scope

This document focuses on a proof-of-play (POP) scenario and covers the following:

  • log basics
  • system requirements
  • SQL authentication requirements and process
  • setting up a Moxie log system
  • log system architecture
  • monitoring
  • log database backup
  • long-term storage

Log basics

Moxie logging works as follows:

  1. Once Moxie Players are configured to report log data, they write log data to files on their local hard disk drive.
  2. A locally-installed Client Logging Agent (CLA) system service collects the log data and creates a log data package.
  3. The CLA sends log data packages to the Log Server. Every CLA sending log data packages participates in a data transaction with the Log Server. This transactional design was created in order to minimize data loss under normal network operating conditions.
  4. The Log Server moves log data into a SQL Server database for decomposition, normalization and storage. The Log Server is a service that aggregates raw log data extracted from the log data packages received from CLAs in a Moxie network.
  5. The SQL Server contains a chain of databases that work in series to process the log data received from a Log Server. All of the data moving between the databases is managed by the SQL Server Agent service on the SQL Server computer.

System Requirements

  1. Moxie 6.10 or higher
  2. SQL Server 2008 R2 or higher
  3. Moxie Log Server component
  4. SQL credentials with sysadmin role (required during installation to create the log system databases and jobs)
  5. SQL credentials with DBOwner role applied to the log system databases

SQL authentication requirements and process

When installing Moxie, there is the option to install a Log Server. If this option is selected, then during the installation process a companion installation tool - the Moxie Log Server Configuration (MLSC) wizard - is started at the appropriate time. This tool configures the Moxie log system.

This configuration does not have to be completed at the time of Moxie Log Server installation if there is no immediate requirement for logging functionality.

Note: In order for the MLSC to be installed on the system, Log Server must be selected from the Which components do you want to install page of the Moxie installation wizard.

The MLSC can be started at any time after the initial installation of Moxie in order to add log functionality to a Moxie system or to reconfigure the existing Moxie log system. The MLSC is located, as a standalone application, in the Moxie installation directory's Tools folder.

The MLSC requires two sets of SQL Server credentials for the configuration and use of the log system:

  1. Credentials for the creation of the databases and jobs required by the Moxie log system. These credentials are discarded after the database tables have been created.
  2. Credentials for the exclusive use of the Moxie Log Server in order for it to access SQL Server for the insertion of log data. We recommend these credentials do not change, and are exempt from typical password expiration policies.

Setting up the Moxie log system

  1. Start the Moxie installation wizard.
  2. Select Log Server from the component selection page of the Moxie installation wizard.

  3. Proceed through the installation wizard. When appropriate, the Moxie Log Server Configuration wizard starts. This wizard is required to configure the Moxie log system.

  4. Proceed through the Moxie Log Server Configuration wizard until you reach the Configure the SQL Server database page.
  5. Enter the credentials for the initial setup of databases and creation of SQL jobs. SQL jobs are used to:
    1. move data between Stage1 and Stage2 databases
    2. transform logs into a normalized form when the data is moved between the Stage2 and Log databases

Note: SQL jobs only get created when using the full version of SQL Server. If you are using SQL Server Express, you must configure the jobs manually. We recommend using Task Scheduler to run two stored procedures: p_MoveFromStage1 (located in State 2 database) and p_MoveFromStage2 (located in the Log database). By default, these jobs are configured to execute 10,000 records every 10 seconds.

  1. The supplied credentials must be of the sysadmin role type. These credentials are discarded after the creation of the required databases, tables and SQL jobs.
  2. When the credentials and the configuration are verified, click Next. The Database discovery and actions page of the wizard appears. On a first-time installation, database creation is required.
  3. Click Perform database actions to begin the creation of the required databases. Messaging indicates if database creation was successful.

  4. Click Next. The Configure SQL Server account for use by the Log Server page of the wizard appears. At this point of the installation, it is recommended that the configuration wizard be left running but put off to the side while SQL Management Studio is started.
  5. In SQL Server Management Studio, configure a second set of user credentials that will be assigned the DBOwner role for the four log system databases created. These are credentials used by Moxie every time new logs require processing and insertion into the databases. To grant required permissions in SQL Server:
    1. Start SQL Server Management Studio and connect to your instance of SQL Server.
    2. In the left pane, click Databases, select one of the log databases, click Security > Users.
    3. In the Users pane, right-click the user requiring permissions and click Properties.
    4. In Database role membership, select db_owner privileges, and click OK.
  6. Return to the Moxie Log Server Configuration wizard.

  7. Enter the credentials just created.
  8. Click Verify credentials.
  9. Click Next. The Confirm the Log Server configuration page appears.
  10. Click Finish to save the configuration and restart the Log Server.

At this stage in the installation process, configuration of the Moxie Log Server is complete and it is ready to receive log data.

Activating content to generate log data

Moxie log functions can be activated to generate logs in a variety of content constructs such as:

  • layouts
  • individual layout items
  • playlists
  • individual playlist items

If the content being logged has user-defined metadata, that metadata is included in the log.

The following diagram illustrates where log generation is enabled in Moxie modules:

Log system architecture

When designing the architecture of a digital signage network that captures proof-of-play logs, it is important to build toward your requirements. The following examples explain the architecture for three common scenarios.

Mission-critical logging

In this scenario, a log system is a business requirement. Log data verifies that content was displayed on a Player. Logging is configured and activated for most or all of the Players on a network. It is not unusual for this scenario to rapidly generate a large volume of log data. To accommodate the large volume of data the Moxie Data Server and Log Server have been placed on separate servers. This eliminates the likelihood that the services starve each other for resources as they operate.

The SQL Server is separate and could be part of a SQL cluster. In many organizations, the SQL cluster containing the Log Server is included as part of the back-up and data protection policies critical to the business. This ensures that information contained by the SQL cluster is well-managed by the resident DBA.

Part-time logging

In this scenario, logging is activated on a subset of network Players or only during limited time periods. Logging is not a critical business component. Data is utilized when available and log data collection is a secondary goal of this system.

This architecture takes into account the possibility that the Log Server may or may not get semi-continual use and the recorded log data may require preservation over a long period of time. This example has the Moxie Data Server and the Log Server installed on the same physical computer.

Again, the SQL Server is separate and could be part of a SQL cluster. In many organizations, the SQL cluster containing the Log Server is included as part of the back-up and data protection policies critical to the business. This ensures that information contained by the SQL cluster is well-managed by the resident DBA.

Forensic logging

In this scenario, log functions are used as a means of troubleshooting the playback of content on a system. Logging is typically not active on the content or Players, however it can be activated for a limited time, as needed.

In the following schematic, the Moxie Data Server, Log Server and SQL database are installed on the same server. The Moxie database and Log Server database are both storing data on the same SQL Server. This reduces costs and architectural complexity for a part of the system which experiences light use. If at any point additional logging is required, the Log Server can be migrated to a separate server machine in order to spread the data and communications load.

Monitoring

Moxie versions 6.10 and higher have an option called Proof-of-Play, which displays statistics about the flow of logs from a Player to the Log Server. These statistics, updated every 15 seconds, include:

  • connection status
  • logs processed
  • failures

The button to activate the Proof-of-Play option is located in the Statistics command group of the Player Manager ribbon.

When troubleshooting an issue related to logging, the Proof-of-Play statistics should be examined first.

Example Proof-of Play statistics:

    Status generated at: 11/13/2013 09:44:42
    Log Server last contacted: 11/13/2013 09:44:38

    Containers sent to Log Server: 119
    Container send failures: 0
    Corrupt containers: 0

    Logs processed: 2874
    Logs successfully written to log database: 2874
    Corrupt logs: 0

The statistics generated and visible in the Player Manager module are written to a text file located on the Player computer. The log file location is: C:\ProgramData\Omnivex\Moxie\Logs\CLAStatistics.txt

More in-depth monitoring of logs can be performed using the functions of SQL Management Studio. The Moxie Installation Guide has a listing of all the default views created in the log database and a description of their function. These views can be used to display the most common metrics of log filtering. Further manipulation and examination of log data can be performed using queries.

Log system backup

Creating a backup can minimize loss of log data. The Log Server is a separate functional entity from the Moxie Data Server and it requires its own back up procedure to maintain log data.

Log Database

The most important element of the log system requiring backup is the Log database. The Log database appears in the SQL Server with the prefix defined when configuring the log system.

Note: OmnivexLog is the default prefix, by default the SQL Server is named OmnivexLog_Log.

The Log database stores the logs which have been processed by the log system. The log data is in a format that can be filtered by a reporting system. This database is the single, most important component requiring back up.

Perform a basic back up

To perform a basic back up of the database, start Microsoft SQL Management Studio and connect to the database instance using the same account credentials used by the Log Server.

  1. Right-click the Log database.
  2. Click Tasks.
  3. Click Back Up…

  4. From the Back Up menu, type a name for the database backup.
  5. Browse to the location for the storage of the backup.
  6. Click OK. The backup starts.

Scheduled backups can be created by using a SQL job. For more information about creating automated SQL database backups, contact your local SQL Database Administrator.

Long-term storage

The amount of database storage used for content log records is dependent on a number of factors, including the:

  • amount of content that is logged
  • frequency at which logged content plays
  • number of players generating content logs

In the following example calculation, 100 Players are logging content every 30 seconds and no user-defined metadata exists for the logged content.

    100 records = ~16Kb*

    # of records created every hour = 100 Players * (60 minutes * 2 times per minute) = 12,000

    Size on disk = 12000 / 100 * 16Kb = 1920Kb or 1.9Mb

    # of records created every day = 12000 records/hour * 24 hours = 288,000

    Size on disk = 1920Kb/hour * 24 hours = 46080Kb or 46Mb

    # of records created every month= 288000 records per day * ~30days = 8,640,000

    Size on disk = 46Mb per day * ~30 days = 1380Mb

    # of records created every year= 8640000 records/Month * 12 months = 103,680,000

    Size on disk = 1380Mb per month * 12 months = 16,560MB (16.5GB)

* This value represents the log data in the Log table in the log database. It does not take into account user-defined metadata that may be associated with the content. Extrapolation of this estimate can be used as a baseline from which the size on disk can grow, based on individual requirements.