SyncCDRDB (TWC4)

Description

The SyncCDRDB task copies CDR's & Attribute Logs (change log for status, PUM,...) from the SOP database to the SMP and do some transformations so that data is available for those Advanced reports: ReportCDR, ReportCDR_Q, ReportQDR, ReportSDR and ReportSTR.

Release notes

Version 4.0.1 - Early deployment
  • Feature: generate new SDR records (sdr2) required by ReportStatusDuration report (MCH-319)
  • Dependency:
    • SMP >= 5.10.12
    • Database Schema module >= 3.2.2 (or backport in 2.10.3)
    • ReportQDR >= 2.0.0

Version 3.1.1 - Early deployment
  • Feature: allow to get agent name from FCC (PC-1547)
  • Dependency:
    • SMP >= 5.10.12
    • Database Schema module >= 3.2.2 (or backport in 2.10.3)
    • ReportQDR >= 2.0.0

Version 3.0.4 - Early deployment
  • Bugfix: Incomplete instance in attribute_log broke the task (TS-833)
  • Dependency:
    • SMP >= 5.10.12
    • Database Schema module >= 3.2.2 (or backport in 2.10.3)
    • ReportQDR >= 2.0.0

Version 3.0.3 - Early deployment
  • Bugfix: incorrect soprep was used to detect callflows (M25309)
  • Dependency:
    • SMP >= 5.10.12
    • Database Schema module >= 3.2.2 (or backport in 2.10.3)
    • ReportQDR >= 2.0.0

Version 3.0.2 - Early deployment
  • Bugfix: Dst first name/last name and agent device owner were missing for a cluster config (M23609)
  • Dependency:
    • SMP >= 5.10.12
    • Database Schema module >= 3.2.2 (or backport in 2.10.3)
    • ReportQDR >= 2.0.0

Version 3.0.1 - Early deployment
  • Bugfix: CDR of calls to extension looking like a callflows id were dropped (M23230)
  • Dependency:
    • SMP >= 5.10.12
    • Database Schema module >= 3.2.2 (or backport in 2.10.3)
    • ReportQDR >= 2.0.0

Version 3.0.0 - Early deployment
  • Feature: Synchronize queue log events (M19497)
  • Improvement: Removed dependencies on attribute log and net.Supervisor (M20219)
  • Improvement: Added first name + last name behind the destination number (M20643)
  • Dependency:
    • SMP >= 5.10.12
    • Database Schema module >= 3.2.2 (or backport in 2.10.3)
    • ReportQDR >= 2.0.0

Version 2.5.2 - Deprecated
  • Bugfix: incorrect soprep was used to detect callflows (M25309)
  • Deprecated: Old queue reporting is no more working with new Communication Server (TS-833)
  • Dependency:
    • SMP >= 5.2.0 (Optional, needed if username in attribute log is needed)
    • SMP >= 5.3.3 (Optional, needed if QueueAccountCode is needed)
    • Database Schema module >= 3.2.2 (or backport in 2.10.3)
    • net.Supervisor 1.6.0+ if you want queue_account_code information.
    • SOP API < 4.0.0 or net.Supervisor >= 1.4.4 for QDR

Version 2.5.1 - Deprecated
  • Bugfix: CDR of calls to extension looking like a callflows id were dropped (Backport) (M23230)
  • Deprecated: Old queue reporting is no more working with new Communication Server (TS-833)
  • Dependency:
    • SMP >= 5.2.0 (Optional, needed if username in attribute log is needed)
    • SMP >= 5.3.3 (Optional, needed if QueueAccountCode is needed)
    • Database Schema module >= 3.2.2 (or backport in 2.10.3)
    • net.Supervisor 1.6.0+ if you want queue_account_code information.
    • SOP API < 4.0.0 or net.Supervisor >= 1.4.4 for QDR

Version 2.5.0 - Deprecated
  • Bugfix: Some call could have two CDRs (M19908)
  • Deprecated: Old queue reporting is no more working with new Communication Server (TS-833)
  • Dependency:
    • SMP >= 5.2.0 (Optional, needed if username in attribute log is needed)
    • SMP >= 5.3.3 (Optional, needed if QueueAccountCode is needed)
    • Database Schema module >= 3.2.2 (or backport in 2.10.3)
    • net.Supervisor 1.6.0+ if you want queue_account_code information.
    • SOP API < 4.0.0 or net.Supervisor >= 1.4.4 for QDR

Version 2.4.3 - Deprecated
  • Bugfix: Disposition was not always correct (M18722)
  • Deprecated: Old queue reporting is no more working with new Communication Server (TS-833)
  • Dependency:
    • SMP >= 5.2.0 (Optional, needed if username in attribute log is needed)
    • SMP >= 5.3.3 (Optional, needed if QueueAccountCode is needed)
    • Database Schema module >= 2.9.0
    • net.Supervisor 1.6.0+ if you want queue_account_code information.
    • SOP API < 4.0.0 or net.Supervisor >= 1.4.4 for QDR

Version 2.4.2 - Deprecated
  • Bugfix: Filter escauccapi calls out of reports
  • Deprecated: Old queue reporting is no more working with new Communication Server (TS-833)
  • Dependency:
    • SMP >= 5.2.0 (Optional, needed if username in attribute log is needed)
    • SMP >= 5.3.3 (Optional, needed if QueueAccountCode is needed)
    • Database Schema module >= 2.9.0
    • net.Supervisor 1.6.0+ if you want queue_account_code information.
    • SOP API < 4.0.0 or net.Supervisor >= 1.4.4 for QDR

Version 2.4.1 - Deprecated
  • Bugfix: Remove comment from sync code(M0)
  • Deprecated: Old queue reporting is no more working with new Communication Server (TS-833)
  • Dependency:
    • SMP >= 5.2.0 (Optional, needed if username in attribute log is needed)
    • SMP >= 5.3.3 (Optional, needed if QueueAccountCode is needed)
    • Database Schema module >= 2.9.0
    • net.Supervisor 1.6.0+ if you want queue_account_code information.
    • SOP API < 4.0.0 or net.Supervisor >= 1.4.4 for QDR

Version 2.4.0 - Deprecated
  • Bugfix: Caller number change was not taken into account (M13837)
  • Feature: Add custom CDR fields "usertag" and "usernote" (M13662)
  • Deprecated: Broken sync code (M0)
  • Dependency:
    • SMP >= 5.2.0 (Optional, needed if username in attribute log is needed)
    • SMP >= 5.3.3 (Optional, needed if QueueAccountCode is needed)
    • Database Schema module >= 2.9.0
    • net.Supervisor 1.6.0+ if you want queue_account_code information.
    • SOP API < 4.0.0 or net.Supervisor >= 1.4.4 for QDR

Version 2.3.2 - Deprecated
  • Bugfix: Fix false error when doing apply change (M0)
  • Deprecated: Old queue reporting is no more working with new Communication Server (TS-833)
  • Dependency:
    • SMP >= 5.2.0 (Optional, needed if username in attribute log is needed)
    • SMP >= 5.3.3 (Optional, needed if QueueAccountCode is needed)
    • Database Schema module >= 2.1.0
    • net.Supervisor 1.6.0+ if you want queue_account_code information.
    • SOP API < 4.0.0 or net.Supervisor >= 1.4.4 for QDR

Version 2.3.1 - Deprecated
  • Bugfix: Caller number change was not taken into account (M13837)
  • Deprecated: Old queue reporting is no more working with new Communication Server (TS-833)
  • Dependency:
    • SMP >= 5.2.0 (Optional, needed if username in attribute log is needed)
    • SMP >= 5.3.3 (Optional, needed if QueueAccountCode is needed)
    • Database Schema module >= 2.1.0
    • net.Supervisor 1.6.0+ if you want queue_account_code information.
    • SOP API < 4.0.0 or net.Supervisor >= 1.4.4 for QDR

Version 2.3.0 - Deprecated
  • Feature: Add queueaccountcode to the related queue. (M10885)
  • Bugfix: Missing information on blind transfer in qdr (M10885)
  • Deprecated: Old queue reporting is no more working with new Communication Server (TS-833)
  • Dependency:
    • SMP >= 5.2.0 (Optional, needed if username in attribute log is needed)
    • SMP >= 5.3.3 (Optional, needed if QueueAccountCode is needed)
    • Database Schema module >= 2.1.0
    • net.Supervisor 1.6.0+ if you want queue_account_code information.
    • SOP API < 4.0.0 or net.Supervisor >= 1.4.4 for QDR

Version 2.2.1 - Deprecated
  • Bugfix: sync fails with substr outside of string error (TS-333)
  • Deprecated: Old queue reporting is no more working with new Communication Server (TS-833)
  • Dependency:
    • SMP >= 5.2.0 (Optional, needed if username in attribute log is needed)
    • Database Schema module >= 2.1.0
    • SOP API < 4.0.0 or net.Supervisor >= 1.4.4 for QDR

Version 2.2.0 - Deprecated
  • Bugfix: Keep last state for "probe" class in attribute_log table (M9058)
  • Bugfix: Delete data after 2 years and not 3 (M9058)
  • Bugfix: Check and compare row->{dst} with list of callflows, skip if it matches (M9488)
  • Bugfix: Records were missing when a call was leaving a queue when there are no members (M9160)
  • Deprecated: Old queue reporting is no more working with new Communication Server (TS-833)
  • Dependency:
    • SMP >= 5.2.0 (Optional, needed if username in attribute log is needed)
    • Database Schema module >= 2.1.0
    • SOP API < 4.0.0 or net.Supervisor >= 1.4.4 for QDR

Version 2.1.1 - Early deployment
  • Bugfix: Compatibility with baseline 8 (MS-418)
  • Dependency:
    • SMP >= 6.1.0
    • Baseline 8

Version 2.1.0 - Deprecated
  • Improvement: Synchronize Agent Device Owner for qdr (M8722)
  • Improvement: Keep the longest userfield when aggregate the CDR (M8960)
  • Bugfix: Fix Lua expression in timezone (M9552)
  • Bugfix: IAX device were not filled in when importing CDRs
  • Deprecated: Old queue reporting is no more working with new Communication Server (TS-833)
  • Dependency:
    • SMP >= 5.2.0
    • Database Schema module >= 2.1.0
    • SOP API < 4.0.0 or net.Supervisor >= 1.4.4 for QDR

Version 1.21.0 - Deprecated
  • Improvement: Synchronize Agent Device Owner for qdr (M8722)
  • Bugfix: Fix Lua expression in timezone (M9552)
  • Deprecated: Old queue reporting is no more working with new Communication Server (TS-833)
  • Dependency:
    • SMP >= 5.2.0
    • Database Schema module >= 1.24.0

Version 1.20.0 - Deprecated
  • Feature: Allow correct parsing of CALLED and CALLER Number for the CACPnP (M8119)
  • Deprecated: Old queue reporting is no more working with new Communication Server (TS-833)
  • Dependency:
    • SMP version 4.9.2
    • Database Schema module version 1.24.0

Version 1.19.1 - Deprecated
  • Bugfix: Translate correctly devices to firstname and extension for phone_queue class (M6381)
  • Deprecated: Old queue reporting is no more working with new Communication Server (TS-833)
  • Dependency:
    • SMP version 4.9.2
    • Database Schema module version 1.24.0

Version 1.19.0 - Deprecated
  • Improvement: Show name instead of resource-id (M6965)
  • Improvement: Replace extension by first name, last name (M7008)
  • Improvement: Change the "Last Agent" column for CDR_Q reports (M7007)
  • Deprecated: Old queue reporting is no more working with new Communication Server (TS-833)
  • Dependency:
    • SMP version 4.9.2
    • Database Schema module version 1.24.0

Version 1.18.1 - Deprecated
  • Bugfix: Some garbage calls where shown in the advanced report (M6552)
  • Deprecated: Old queue reporting is no more working with new Communication Server (TS-833)

Version 1.18.0 - Deprecated
  • Bugfix: No CDR generated when called contains * or # characters
  • Deprecated: Old queue reporting is no more working with new Communication Server (TS-833)

Version 1.17.1 - Deprecated
  • Improvement: don't sync phone registration events to save disk space and increase speed (M0)
  • Deprecated: Old queue reporting is no more working with new Communication Server (TS-833)

Version 1.17.0 - Deprecated
  • Bugfix: very slow to sync queue data
  • Deprecated: Old queue reporting is no more working with new Communication Server (TS-833)

Version 1.16.2 - Deprecated
  • Bugfix: destination extension was not set correctly when using MapNumber to an extension in the internal directory.
  • Deprecated: Old queue reporting is no more working with new Communication Server (TS-833)

Version 1.16.1 - Deprecated
  • Bugfix: Site code was not synced correctly (M5431)
  • Deprecated: Old queue reporting is no more working with new Communication Server (TS-833)

Version 1.16 - Deprecated
  • Bugfix: fax server cdr's are not synced
  • Bugfix: cdr's in E164 format are not synced
  • Bugfix: Don't erase accountcode by device's accountcode if accountcode already present in SOP DB (M3232)
  • Deprecated: Old queue reporting is no more working with new Communication Server (TS-833)

Version 1.15 - Deprecated
  • Bugfix: sync hourly doesn't sync some CDRs that were ongoing during the sync
  • Deprecated: Old queue reporting is no more working with new Communication Server (TS-833)

Version 1.14 - Deprecated
  • Feature: Added cleaning of dbs
  • Deprecated: Old queue reporting is no more working with new Communication Server (TS-833)

Version 1.13 - Deprecated
  • Bugfix: CDR for new resource are not imported (e.g. PolycomIP331 is of type SDPA)
  • Bugfix: exclude KeyboardStatus in cdrdb sync (M0001100)
  • Deprecated: Old queue reporting is no more working with new Communication Server (TS-833)

Version 1.12 - Deprecated
  • Improvement: removed redundant attribute between CDR and attribute log (class: channel + class phone, attribute: state)
  • Deprecated: Old queue reporting is no more working with new Communication Server (TS-833)

Version 1.11 - Deprecated
  • Improvement: optimization: extract queue data from STRs instead of SDRs which is more robust
  • Deprecated: Old queue reporting is no more working with new Communication Server (TS-833)

Version 1.10 - Deprecated
  • Improvement: optimization: optionally don't enrich CDRs with SDR data
  • Deprecated: Old queue reporting is no more working with new Communication Server (TS-833)

Version 1.9 - Deprecated
  • Bugfix: required for clustered smp
  • Deprecated: Old queue reporting is no more working with new Communication Server (TS-833)

Version 1.8 - Deprecated
  • fixes device chopped at 8 chars
  • limit feature

Version 1.7 - Deprecated
  • Bugfix: fixes if no uniqueid in source cdrdb
  • Deprecated: Old queue reporting is no more working with new Communication Server (TS-833)
  • Dependency:
    • SMP >= 1.4

Version 1.6 - Deprecated
  • Bugfix: no update schema (use DB module)
  • Bugfix: don't run on multiple SOPs (confusing with cluster)
  • Bugfix: don't run on HA standby when using HA 2.0 / stable-4
  • Deprecated: Significant improvements have been done to newer versions
  • Dependency:
    • SMP >= 1.4

Version 1.5 - Deprecated
  • Bugfix: correctly set accountcode, etc when in cluster mode
  • Deprecated: Significant improvements have been done to newer versions

Version 1.4 - Deprecated
  • Bugfix: support nondefault smp (different sopdomain)
  • Feature: Last Supported version for SMP <= stable-3
  • Feature: Allows to set limit on number of attribute_log / cdrs to process
  • Feature: Works around no uniqueid in cdr (untested)
  • Deprecated: Old queue reporting is no more working with new Communication Server (TS-833)

Version 1.2 - Deprecated
  • Bugfix: sdr generation + better debugging
  • Deprecated: Significant improvements have been done in a higher version

Version 1.1 - Deprecated
  • Improvement: Minor improvements
  • Deprecated: Significant improvements have been done in a higher version

Version 1.0 - Deprecated
  • Improvement: Minor improvements
  • Deprecated: Significant improvements have been done in a higher version

Version 0.3 - Deprecated
  • Improvement: More debugging options
  • Deprecated: Significant improvements have been done in a higher version

Version 0.2 - Deprecated
  • Improvement: optimize avoiding query in loop
  • Deprecated: Significant improvements have been done in a higher version

Version 0.1 - Deprecated
  • Feature: Include lib (for queues)
  • Deprecated: Significant improvements have been done in a higher version

Version 0.0 - Deprecated
  • Deprecated: Significant improvements have been done in a higher version

Resource configuration interface

GUI unavailable.

Task configuration parameters provided by SMP 5.3+

Warning: Can't find topic CustomerDocs.GenSmpDocCommonTaskParametersRGb55

Resource configuration parameters

  • Debug level: Choose the debug level you want among "nothing", "critical errors only", "interesting info" and "debugging"
  • Sync Attribute Logs: Copy Attribute Logs, also known as State Transition Records (STR) from SOP to SMP. This also creates SDR's (State Duration Records). You can limit the amount of Attribute Logs to synchronize but pay attention in case you want to enrich CDR's with queue data. See note below.
  • Sync CDRs: Copy CDR's from SOP to SMP. It only copies those CDR's that are newer than the newest existing on the smp. You can limit the amount of new CDR's to synchronize. Following transformations are done to the cdrs:
    • CDR's having same calldate and channel are grouped together because they represent the same call. Fields are taken from the appropriate record.
    • Those fields are generated based on the data: srcdevice, dstdevice, accountcode, site, callflows. See note below regarding site field.
    • Price is calculated for new CDR's
  • Enrich CDRs with queue data (slow): For each CDR, use the previously synchronized STR's to generate the Queue Data Records (QDR) and enrich the CDR with queue data. This will permit to create QDR and CDR_Q reports. See note below.
  • Clean: Remove data older than 3 years on the SMP
  • Replace extension or device information by first name and last name, when possible (slow): See note below
  • Sync Queue Logs: Copy logged queue events from SOP (/var/log/asterisk/queue_log) to SMP. Archived files are also processed. 3 options are available :
    • all pending: Import only queue events that are newer than the newest existing on the smp.
    • none: Don't import anything
    • process all (slow): Process all queue events.

Notes

Attribute Logs & queue data for CDR's

If you enable "Enrich CDR's with queue data", Attribute logs (STR) need to be available for those CDR's on the SMP before synchronizing the CDR's. Attribute Logs are synchronized first by the task but pay attention if you limit the amount of Attribute Logs to synchronize and also configured to synchronize CDR's.

It is not possible to add queue data for CDR's that were already synchronized without the option enabled or in case Attribute Logs were not available when the task was executed.

In SyncCDRDB >= 3.0.0, QDR records are not created anymore because queue data are already stored in queue log. Dependency on net.Supervisor is also dropped.

Site code

ALERT! The site code of the CDR's is determined in this order:
  1. If the resource used to make the call has a site code set in the parameters, this site code is used, overriding any other site codes.
  2. If the resource has no site code set, the site code of the extension used as the caller ID will be set.
  3. If the resource is assigned to multiple extensions, a random extension will be chosen.

Device owner, ...

For some reports (CDR, CDR_Q, SDR) it is now possible to show information about the device owner (the first name, the last name and the extension between "<" ">", for example "John Doe" <1001> ) in addition of the device ID. This information is added by SyncCDRDB to the database stored on the SMP. The task will try to link a device with an extension by searching if an extension has this device as primary or secondary phone, or if there is an outgoing extension configured in the resource. If such an extension exists, it will take its first name and last name. If more than one extension are found, the result will be "More than one owner".

Obviously this information is computed when the task is running. This can have some consequences that need to be taken into account. If a call has been made from a device (for example SDPA0001, primary phone of John Doe, extension 1001) this morning, and if this device has been reassigned to another user during the day (for example, SDPA0001 is now primary phone of Jane Doe, extension 5000), SyncCDRDB will consider Jane Doe as the device owner of SDPA0001.

Copyright © Escaux SA