Analytics in kaltura CE4

Analytics help in monitoring data on your kaltura server. In management console , you can easily check analytics by clicking on the analytics tab.Analytics help you in amount of bandwidth used by publisher,total size of data uploaded and also you can check in which reason your video was played.

Edit In file

vi /opt/kaltura/crontab/kaltura_crontab

At line 4 and 5 add kaltura in path after crond so that path looks like

/opt/kaltura/app/alpha/crond/kaltura/storage_update.sh
/opt/kaltura/app/alpha/crond/kaltura/www_logs_insert.sh

Edit In file

/opt/kaltura/app/alpha/crond/kaltura/www_logs_insert.sh

At line 3:

Change path from /opt/kaltura/logs to /opt/kaltura/log

At line 10:

    Change format of date. Remove “-” from date format

At line 20:

    Change DB from pa-db to localhost

At line 23:

after zcat change kaltura-apache log path to kaltura_apache_access.log   

and

correct path of “billing_summary_www.php” to “/opt/kaltura/app/scripts/billing_summary_www.php”

Do same for the above path in line :24

At Line 25:

change kaltura user and password according to your mysql.(user=root,pass=somepass)

Edit In file

/opt/kaltura/app/plugins/onprem/scripts/reportUsageTrackingIfOptIn.php

Comment line : 30 and add this line below it $dbh = new PDO('mysql:host=127.0.0.1;dbname=kalturadw', 'etl', 'etl');

After doing this Grant all permissions to user etl with password etl

  GRANT ALL PRIVILEGES ON *.* TO 'etl'@'localhost' IDENTIFIED BY 'etl';

Import a table as

mysql -u root -p kalturadw_ds < /opt/kaltura/dwh/ddl/migrations/deployed/20110328_4257_storage_usage_kuser_sync/20110105_updated_kusers_storage_usage.sql

Execute this command in mysql to create a missing table

DROP TABLE IF EXISTS `dwh_aggr_partner`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `dwh_aggr_partner` (
`partner_id` int(11) NOT NULL DEFAULT '0',
`date_id` int(11) NOT NULL DEFAULT '0',
`sum_time_viewed` decimal(20,3) DEFAULT NULL,
`count_time_viewed` int(11) DEFAULT NULL,
`count_plays` int(11) DEFAULT NULL,
`count_loads` int(11) DEFAULT NULL,
`count_plays_25` int(11) DEFAULT NULL,
`count_plays_50` int(11) DEFAULT NULL,
`count_plays_75` int(11) DEFAULT NULL,
`count_plays_100` int(11) DEFAULT NULL,
`count_edit` int(11) DEFAULT NULL,
`count_viral` int(11) DEFAULT NULL,
`count_download` int(11) DEFAULT NULL,
`count_report` int(11) DEFAULT NULL,
`count_media` int(11) DEFAULT NULL,
`count_video` int(11) DEFAULT NULL,
`count_image` int(11) DEFAULT NULL,
`count_audio` int(11) DEFAULT NULL,
`count_mix` int(11) DEFAULT NULL,
`count_mix_non_empty` int(11) DEFAULT NULL,
`count_playlist` int(11) DEFAULT NULL,
`count_bandwidth` bigint(20) DEFAULT NULL,
`count_storage` bigint(20) DEFAULT NULL,
`count_users` int(11) DEFAULT NULL,
`count_widgets` int(11) DEFAULT NULL,
`flag_active_site` tinyint(4) DEFAULT '0',
`flag_active_publisher` tinyint(4) DEFAULT '0',
`aggr_storage` bigint(20) DEFAULT NULL,
`aggr_bandwidth` bigint(20) DEFAULT NULL,
`count_buf_start` int(11) DEFAULT NULL,
`count_buf_end` int(11) DEFAULT NULL,
`count_open_full_screen` int(11) DEFAULT NULL,
`count_close_full_screen` int(11) DEFAULT NULL,
`count_replay` int(11) DEFAULT NULL,
`count_seek` int(11) DEFAULT NULL,
`count_open_upload` int(11) DEFAULT NULL,
`count_save_publish` int(11) DEFAULT NULL,
`count_close_editor` int(11) DEFAULT NULL,
`count_pre_bumper_played` int(11) DEFAULT NULL,
`count_post_bumper_played` int(11) DEFAULT NULL,
`count_bumper_clicked` int(11) DEFAULT NULL,
`count_preroll_started` int(11) DEFAULT NULL,
`count_midroll_started` int(11) DEFAULT NULL,
`count_postroll_started` int(11) DEFAULT NULL,
`count_overlay_started` int(11) DEFAULT NULL,
`count_preroll_clicked` int(11) DEFAULT NULL,
`count_midroll_clicked` int(11) DEFAULT NULL,
`count_postroll_clicked` int(11) DEFAULT NULL,
`count_overlay_clicked` int(11) DEFAULT NULL,
`count_preroll_25` int(11) DEFAULT NULL,
`count_preroll_50` int(11) DEFAULT NULL,
`count_preroll_75` int(11) DEFAULT NULL,
`count_midroll_25` int(11) DEFAULT NULL,
`count_midroll_50` int(11) DEFAULT NULL,
`count_midroll_75` int(11) DEFAULT NULL,
`count_postroll_25` int(11) DEFAULT NULL,
`count_postroll_50` int(11) DEFAULT NULL,
`count_postroll_75` int(11) DEFAULT NULL,
`count_streaming` bigint(20) DEFAULT '0',
`aggr_streaming` bigint(20) DEFAULT '0',
PRIMARY KEY (`partner_id`,`date_id`),
KEY `date_id` (`date_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (date_id)
(PARTITION p_201001 VALUES LESS THAN (20100201) ENGINE = MyISAM) */;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `dwh_aggr_partner`
--
LOCK TABLES `dwh_aggr_partner` WRITE;
/*!40000 ALTER TABLE `dwh_aggr_partner` DISABLE KEYS */;
/*!40000 ALTER TABLE `dwh_aggr_partner` ENABLE KEYS */;
UNLOCK TABLES;

Analytic not showing correctly or they are missed

• Log in to your mysql and execute this command

UPDATE kalturadw_ds.locks set lock_state = 0;

To make geo-analytics work on new machine. Follow these steps

• yum install perl-libwww-perl.noarch
• vi /opt/kaltura/dwh/etlsource/scripts/ip2location/update_ip2location.sh

Replace line 21 with the code below

perl $ROOT_DIR/etlsource/scripts/ip2location/download.pl -package DB7 -login [email protected] -password S47CW89L >> $LOGFILE 2>&1