Differences

This shows you the differences between two versions of the page.

--- vendors:cisco:uc:cms [2021/10/16 15:41] – [Import into SQL] gerardorourke
+++ vendors:cisco:uc:cms [2021/10/20 16:15] (current) – [Verify JSON format of CDR file and correct if necessary] gerardorourke
@@ Line 3: / Line 3: @@
 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
 ===== Import CMS CDRs into SQL ======
@@ Line 8: / Line 10: @@
 ==== Verify JSON format of CDR file and correct if necessary ====
   * 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)
+<code>
+{
+  "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": ""
+ }
+</code>
   * The File can have some corruption on it. Check it is valid JSON using JQ - https://stedolan.github.io/jq/download/
-  *
 <code>
 C:\X-Temp>jq-win64 -c . CDRs1.json > CDR12.json
@@ Line 22: / Line 57: @@
 fixes this issue.
-Confirm all good by re-running with JQ.
+  * Even if the file is not corrupt - I found running it through JQ was necessary.
+<code> son2csv -i CDR2021_1.json -o CDR2021_1_V2.csv </code>
+  * Use the output from this this as the input to the json2csv
 ==== JSON2CSV ====
@@ Line 56: / Line 92: @@
   GROUP BY CONVERT(Datetime,(CONVERT(char(10),[time_stamp],102)),102)
   ORDER by CONVERT(Datetime,(CONVERT(char(10),[time_stamp],102)),102)
+</code>
+==== Number of calls per conf? ====
+How many calls disconnected in the same minute - typically will be the same call.
+<code sql>
+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)
 </code>