Cisco Meeting Server - CMS

CMS CDRs are in JSON format. We want them in CSV format to import into SQL Server.

Reference: https://www.cisco.com/c/dam/en/us/td/docs/conferencing/ciscoMeetingServer/Reference_Guides/Version-2-9/Cisco-Meeting-Server-CDR-Guide-2-9.pdf

• Gather your CDRs into one folder and pipe to a single file e.g. type * > ../CDR-file.json
• Add an empty first line which contains all of the headers (as its the first line which the json2CSV checks to create the CSV file)

{
  "session_id": "",
  "record_type": "",
  "time_stamp": "",
  "correlator_index": "",
  "call_leg_id": "",
  "remote_party": "",
  "local_address": "",
  "remote_address": "",
  "call_leg_type": "",
  "direction": "",
  "call_id": "",
  "call_name": "",
  "call_type": "",
  "cospace": "",
  "call_correlator": "",
  "state": "",
  "group_id": "",
  "sip_call_id": "",
  "display_name": "",
  "remote_address": "",
  "call_leg_id": "",
  "reason": "",
  "remote_teardown": "",
  "duration_seconds": "",
  "unencrypted_media": "",
  "sip_call_id": "",
  "db_record_type": ""
 }

• The File can have some corruption on it. Check it is valid JSON using JQ - https://stedolan.github.io/jq/download/

 
C:\X-Temp>jq-win64 -c . CDRs1.json > CDR12.json
parse error: Expected value before ',' at line 85959, column 471

I found multiple lines with the invalid CDR ending with a double db_record_type. A simple find and replace of

"db_record_type": "cdr_acano"}, "db_record_type": "cdr_acano"}"

with

"db_record_type": "cdr_acano"}

fixes this issue.

• Even if the file is not corrupt - I found running it through JQ was necessary.

 son2csv -i CDR2021_1.json -o CDR2021_1_V2.csv 

• Use the output from this this as the input to the json2csv

• Install latest version of Node
• Install Json2Csv Package - https://www.npmjs.com/package/json2csv
• Convert the file to CSV using the Node json2csv

json2csv -i CDR12.json -o CDR12.csv

• Open CSV file in excel.
• Do a Find and Replace in the DateTime Column to get rid of the “T” - Find “T” - Replace with a <space>
• Do a Find and Replace in the DateTime Column to get rid of “Z” - Find “Z” - Replace with <blank>
• Save

• Import flat file into SQL Server
• 

SELECT 
      DATE = CONVERT(Datetime,(CONVERT(CHAR(10),[time_stamp],102)),102)
      ,TotalCalls = COUNT(*)
 
  FROM [CMS].[dbo].[CDR_Final]
  WHERE record_type = 'CallLegEnd'
 
  GROUP BY CONVERT(Datetime,(CONVERT(CHAR(10),[time_stamp],102)),102)
  ORDER BY CONVERT(Datetime,(CONVERT(CHAR(10),[time_stamp],102)),102)

How many calls disconnected in the same minute - typically will be the same call.

SELECT 
DateTime = CONVERT(Datetime,(CONVERT(CHAR(19),[time_stamp],20)),20)
,TotalCalls = COUNT(*)
 
  FROM [CMS].[dbo].[CDR_Final]
  WHERE record_type = 'CallLegEnd'
 
  GROUP BY CONVERT(Datetime,(CONVERT(CHAR(19),[time_stamp],20)),20)
  ORDER BY CONVERT(Datetime,(CONVERT(CHAR(19),[time_stamp],20)),20)