Shoretel CDR Database Schema Upgrade 19.XX to 21.82

Schema Upgrade Script Before Import Old Records

Immediately after upgrading from Shoreware 19.XX to Shoreware 21.82, you will want to import your old CDR records.  Before you can do that, you must upgrade the old CDR records by adding some new fields.  My upgrade script will take your old CDR and add those fields.  To use it, import your old CDR into a stand-alone mysql instance and then upgrade it using my handy script, then export it out, finally import the records into your Shoreware director.

Create a Stand-alone Instance of the CDR database


1
2
3
4
5
drop database shorewarecdr;
CREATE DATABASE shorewarecdr;
GRANT ALL ON shorewarecdr.* TO st_cdrreport@localhost IDENTIFIED BY 'passwordcdrreport';
GRANT ALL ON shorewarecdr.* TO `root`@`127.0.0.1` IDENTIFIED BY 'passwordcdrreport';
FLUSH PRIVILEGES;

Upgrade the old CDR


 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
Alter table `agentactivity` Add `TenantID` int(11) NOT NULL DEFAULT '1' AFTER `Archived`;
ALTER TABLE `agentactivity` Add `ClusterID` int(11) DEFAULT '0' AFTER `TenantID`;

Alter table `audio_attendee` Add `TenantID` int(11) NOT NULL DEFAULT '1' AFTER `is_host`;
ALTER TABLE `audio_attendee` Add `ClusterID` int(11) DEFAULT '0' AFTER `TenantID`;

Alter table `call` Add `TenantID` int(11) NOT NULL DEFAULT '1' AFTER `ConfAdjDuration`;
Alter table `call` Add `StartTimeUTC` datetime DEFAULT NULL AFTER `TenantID`;
Alter table `call` Add `EndTimeUTC` datetime DEFAULT NULL AFTER `StartTimeUTC`;
ALTER TABLE `call` Add `ClusterID` int(11) DEFAULT '0' AFTER `EndTimeUTC`;
ALTER TABLE `call` Add `RelatedCallGUID` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL AFTER `ClusterID`;

ALTER TABLE `connect` ADD  `TenantID` int(11) NOT NULL DEFAULT '1'  AFTER `AOC_E`;
ALTER TABLE `connect` ADD  `ProfileID` int(11) DEFAULT '0'          AFTER `TenantID`;
ALTER TABLE `connect` ADD  `AccountID` int(11) DEFAULT '0'          AFTER `ProfileID`;
ALTER TABLE `connect` ADD  `StartTimeUTC` datetime DEFAULT NULL     AFTER `AccountID`;
ALTER TABLE `connect` ADD  `EndTimeUTC` datetime DEFAULT NULL       AFTER `StartTimeUTC`;
ALTER TABLE `connect` ADD  `FPGSync` tinyint(4) DEFAULT '0'         AFTER `EndTimeUTC`;
ALTER TABLE `connect` ADD  `FCLSync` tinyint(4) DEFAULT '0'         AFTER `FPGSync`;
ALTER TABLE `connect` ADD  `DMSync` tinyint(4) DEFAULT '0'          AFTER `FCLSync`;
ALTER TABLE `connect` ADD  `SwitchName` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL  AFTER `DMSync`;
ALTER TABLE `connect` ADD  `ClusterID` int(11) DEFAULT '0'  AFTER `SwitchName`;
ALTER TABLE `connect` ADD  `PartyDID` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL AFTER `ClusterID`;
ALTER TABLE `connect` ADD  `ExternalSIPCallID` varchar(512) COLLATE utf8_unicode_ci DEFAULT NULL  AFTER `PartyDID`;

ALTER TABLE `mediastream` ADD `TenantID` int(11) NOT NULL DEFAULT '1' AFTER `CallLegType`;
ALTER TABLE `mediastream` ADD `ClusterID` int(11) DEFAULT '0' AFTER `TenantID`;

ALTER TABLE `queuecall` ADD  `TenantID` int(11) NOT NULL DEFAULT '1' AFTER `ServerName`;
ALTER TABLE `queuecall` ADD  `ClusterID` int(11) DEFAULT '0' AFTER `TenantID`;

ALTER TABLE `queuedepth` ADD  `TenantID` int(11) NOT NULL DEFAULT '1'  AFTER `QueueDepth`;
ALTER TABLE `queuedepth` ADD  `ClusterID` int(11) DEFAULT '0'  AFTER `TenantID`;

ALTER TABLE `queuestep` ADD  `TenantID` int(11) NOT NULL DEFAULT '1' AFTER `Hunting`;
ALTER TABLE `queuestep` ADD  `ClusterID` int(11) DEFAULT '0' AFTER `TenantID` ;

/* WE DONT USE WEB_ATTENDEE AND WEB_SESSION */

Export the newly upgrade CDR


1
2
mysqldump --user=st_cdrreport --password=passwordcdrreport   --no-create-info  shorewarecdr call connect mediastream queuecall queuedepth queuestep > dump.sql
mysqldump --user=st_cdrreport --password=passwordcdrreport  shorewarecdr agentactivity > dump2.sql


Location: Laguna Beach, CA, USA

Comments

Post a Comment

Popular posts from this blog

Microsoft Visio 2010 Premium Product Keys

Image
Visio KMS client key from Microsoft You can use these with AutoKMS tool such as Office Toolkit 2.4.9 (office 2010/2013) which will re-arm them once every 180 days. So you are always in the grace period.  At the bottom of this page are keys for Visio 2019.  If you are here to figure out how to get a free copy of Visio activated on your computer, without paying Microsoft for it, then God or Allah will judge you later for your sins. Your soul will be sent to the underworld to be tormented by Satan for these sins. Please pay for your software! Visio Standard 2010 767HD-QGMWX-8QTDB-9G3R2-KHFGJ Visio Professional 2010 7MCW8-VRQVK-G677T-PDJCM-Q8TCP Visio Premium 2010 D9DWC-HPYVV-JGF4P-BTWQB-WX8BJ Keys from our Russian friends. you can only install with these keys, you cannot activate. ========================================================================= Visio Premium 2010: ========================================================================= C383V-HPHMC-Y6KWM-B
Read more

Mercedes Benz Diesel CDI EGR Emulator Circuit Diagrams

Image
Mercedes EGR Emulator Circuits for Electric Wahler EGR on Diesel Engine For any car fitted with a MAF you can't just block the  EGR valve  as the MAF detects the reduced airflow when the valve operates. If it fails to report this reduction the ECU throws a code, turns on the MIL light and engages Limp Home mode. The  EGR valve  is operated in varying degrees whenever the engine is in cruise mode. A circuit has to be designed that reduces the MAF output voltage whenever the  EGR valve  is called to actuate. Having seen a mod on another forum I decided to give it a go and installed it last night. The circuit is really simple. The  EGR valve  transducer is fed with constant 12v which is proportionately shunted to Gnd to create the potential to activate the transducer, to engage the EGR solenoid and allow hot gasses into the intage via the  EGR valve  in varying degrees. The MAF is in the cold air intake. the EGR gasses enter further downstream. the MAF then monitors the red
Read more

Fix: The Diagnostic Service Host service failed to start due to the following error. [ solved, no kidding ]

Image
Lots of these Messages about the Diagnostic Service Host in the System Log On Windows 7/10 Machines Attached to A Domain The Diagnostic Service Host service failed to start due to the following error: A privilege that the service requires to function properly does not exist in the service account configuration. You may use the Services Microsoft Management Console (MMC) snap-in (services.msc) and the Local Security Settings MMC snap-in (secpol.msc) to view the service configuration and the account configuration.  The good news is that you can fix it using active directory group policy or the Local Security Policy and just adding a few things to the Local Policies.  Same fix applies to both Windows 7 and Windows 10.  To Fix this using any Policy, you must be running a Professional or Enterprise version of Windows. If you have Home Edition, sorry. Here's How to Fix It You see this in your system logs repeatedly, and its dragging down your computer and making it run slow. 
Read more