Cisco UCM CMR filter

[astrakid]

in reference to #70 i want to add further information to homer by retrieving data of cisco CMR files.

the structure is as follwos:

column name | type | example

cdrRecordType | INTEGER | 2
globalCallID_callManagerId | INTEGER | 1
globalCallID_callId | INTEGER | 1549027
nodeId | INTEGER | 2
directoryNum | VARCHAR(50) | 11335
callIdentifier | INTEGER | 36951877
dateTimeStamp | INTEGER | 1593545181
numberPacketsSent | INTEGER | 119316
numberOctetsSent | INTEGER | 12076047
numberPacketsReceived | INTEGER | 119311
numberOctetsReceived | INTEGER | 16490570
numberPacketsLost | INTEGER | 0
jitter | INTEGER | 20
latency | INTEGER | 0
pkid | UNIQUEIDENTIFIER | dd934380-4391-409e-82bc-7d71ce4e8bd3
directoryNumPartition | VARCHAR(50) | Eingeloggt
globalCallId_ClusterID | VARCHAR(50) | StandAloneCluster
deviceName | VARCHAR(129) | CSF11335
varVQMetrics | VARCHAR(600) | CS=0;SCS=0
duration | INTEGER | 2387
videoContentType | VARCHAR(10) |  
videoDuration | INTEGER |  
numberVideoPacketsSent | INTEGER |  
numberVideoOctetsSent | INTEGER |  
numberVideoPacketsReceived | INTEGER |  
numberVideoOctetsReceived | INTEGER |  
numberVideoPacketsLost | INTEGER |  
videoAverageJitter | INTEGER |  
videoRoundTripTime | INTEGER |  
videoOneWayDelay | INTEGER |  
videoReceptionMetrics | VARCHAR(600) |  
videoTransmissionMetrics | VARCHAR(600) |  
videoContentType_channel2 | VARCHAR(10) |  
videoDuration_channel2 | INTEGER |  
numberVideoPacketsSent_channel2 | INTEGER |  
numberVideoOctetsSent_channel2 | INTEGER |  
numberVideoPacketsReceived_channel2 | INTEGER |  
numberVideoOctetsReceived_channel2 | INTEGER |  
numberVideoPacketsLost_channel2 | INTEGER |  
videoAverageJitter_channel2 | INTEGER |  
videoRoundTripTime_channel2 | INTEGER |  
videoOneWayDelay_channel2 | INTEGER |  
videoReceptionMetrics_channel2 | VARCHAR(600) |  
videoTransmissionMetrics_channel2 | VARCHAR(600) |  
localSessionID | VARCHAR(128) | 725db2ec00105000a00000e0c51c2dc0
remoteSessionID | VARCHAR(128) | 20682f0300105000a00000e0c51c2cc0
headsetSN | VARCHAR(129) |  
headsetMetrics | VARCHAR(1024) |  

[astrakid]

the file is simple csv, that means the example given looks in csv like this:

2,1,1549027,2,"11335",36951877,1593545181,119316,12076047,119311,16490570,0,20,0,"dd934380-4391-409e-82bc-7d71ce4e8bd3","Eingeloggt","StandAloneCluster","CSF11355","CS=0;SCS=0",2387,"",,,,,,,,,,"","","",,,,,,,,,,"","","725db2ec00105000a00000e0c51c2dc0","20682f0300105000a00000e0c51c2cc0","",""

[astrakid]

first line of each csv contains column names, second line column types, line 3 ff contains data.

[astrakid]

to clarify as in #70:
in our setup every hour a new file is created with x datasets. each row contains one dataset. each file has always column names in first row, type-description in 2nd row and datasets in the following rows.

[lmangani]

Alright the job here is slightly different - i see mostly session stats we COULD perhaps convert to a HEP qOS report, but none is filled. Could you find an example with more of the values available?

[astrakid]

which values do you mean? most values are video, which we hardly use. all other calls will have at least jitter, latency and packet loss.
here further examples:

2,1,2019806,11111,41387961,1605856474,207976,32029071,207896,32035435,77,16,0,bb843e8b-ee08-40d0-b1d2-c14042881fac,Eingeloggt,StandAloneCluster,CSF11111,CS=0.119;SCS=0.079,4161,,,,,,,,,,,,,,,,,,,,,,,,,57a4bc0400105000a00000e0c51c329a,43b4ebeb00105000a00000e0c51c3f36,,,
2,1,2019806,22222,41387960,1605856474,208128,32072599,208069,32044435,56,9,0,85969a7e-f2ef-4820-a90e-48ee8dfb8946,Eingeloggt,StandAloneCluster,CSF22222,CS=0;SCS=0,4162,,,,,,,,,,,,,,,,,,,,,,,,,43b4ebeb00105000a00000e0c51c3f36,57a4bc0400105000a00000e0c51c329a,,,
2,1,2019997,33333,41389303,1605856563,45241,5762053,17925,828318,3,17,0,e0cf069b-90ba-409b-867f-ba100f28a419,Eingeloggt,StandAloneCluster,CSF33333,CS=0.08;SCS=0.08,308,main,0,0,0,0,0,0,0,0,0,RxCodec=H264;RxReso=0x0;RxFrameRate=0,TxCodec=H264;TxReso=0x0;TxFrameRate=0,,,,,,,,,,,,,2c568e5c00105000a00098e7434cd239,000079ea00105000a0009829a680d391,,,

first example has packetloss 77, jitter 16, latency 0, varVQMetrics are filled as well (0.119/0.079 whatever that means, didn't check yet).

does that help?

[lmangani]

This is the JSON object you should map data to - taken from HEPGen.js which does the same thing:

 {
                // media report
                  rcinfo: {
                          type: 'HEP',
                          version: 3,
                          payload_type: 'JSON',
                          captureId: 2001,
                          capturePass: 'myHep',
                          ip_family: 2,
                          protocol: 17,
                          proto_type: 34,
                          srcIp: pub_ip,
                          dstIp: peer_ip,
                          srcPort: 0,
                          dstPort: 0,
                          correlation_id: call_id
                  },
    		  pause: 400,
		  payload: '{"CORRELATION_ID":"'+call_id+'","RTP_SIP_CALL_ID":"'+call_id+'","DELTA":19.983,"JITTER":0.017,"REPORT_TS":'+new Date().getTime()/1000+',"TL_BYTE":0,"SKEW":0.000,"TOTAL_PK":1512,"EXPECTED_PK":1512,"PACKET_LOSS":0,"SEQ":0,"MAX_JITTER":0.010,"MAX_DELTA":20.024,"MAX_SKEW":0.172,"MEAN_JITTER":0.005,"MIN_MOS":4.032, "MEAN_MOS":4.032, "MOS":4.032,"RFACTOR":80.200,"MIN_RFACTOR":80.200,"MEAN_RFACTOR":80.200,"SRC_IP":"'+pub_ip+'", "SRC_PORT":26872, "DST_IP":"'+peer_ip+'","DST_PORT":51354,"SRC_MAC":"00-30-48-7E-5D-C6","DST_MAC":"00-12-80-D7-38-5E","OUT_ORDER":0,"SSRC_CHG":0,"CODEC_PT":9, "CLOCK":8000,"CODEC_NAME":"g722","DIR":0,"REPORT_NAME":"'+pub_ip+':26872","PARTY":0,"TYPE":"PERIODIC"}'
}

[astrakid]

great, thanks. didn't expect you to do all my work! 🥇
thx a lot, will test it on monday!