Introduction
This document describes how to find and manage recordings through Media Sens's CLI. Recordings can only be retrieved through the MediaSense Application Programming Interface (API), with an application like Search and Play or a third party appliction. However, there are scenarios in which the MediaSense CLI can provide useful information regarding recording metadata.
Contributed by Cisco TAC Engineers.
Problem
There are instances in which the CLI can be easily used to find information about recordings. There are a few common operations that are particularly useful:
- Find a recording with known date and time
- Find total count of recordings
- Find count of pruned recordings
- Delete error call recordings
Solution
MediaSense allows you to query metadata database through CLI. Log in to Media Sense server via Secure Shell (SSH) with platform Administrator account, and use the command run ora_sql with the name of the database you want to use, which is meta. Note that the SQL query needs to be surrounded by double quotes.
The table that stores all recording sessions is called RecordingSession. This table has several columns. Here are the names and discriptions of important columns:
column |
description |
pkid |
A unique, MediaSense identifier for a recording. This is the session ID for a recording, as seen in Search and Play and through the API service. |
startdatetime |
The time the recording session started, in epoch time (milliseconds since 1-1-1970). |
duration |
Length of the recorrding session in milliseconds. |
state |
The final state of the recording.
1 = ACTIVE
2 = CLOSED_NORMAL
3 = CLOSED_ERROR
4 = DELETED
5 = INITIALIZING
6 = UPLOADING
7 = PROCESSING
8 = IMPORTING
|
sipserver
|
The MediaSense node ID where the SIP signalling communication was processed. |
captureserver |
The MediaSense node ID where this recording was captured and stored. |
callcontrollertype |
A value of 1 indicates the recording was initiated by CUCM. A value of 2 indicates CUBE. |
ccid |
In CUBE forking, the gateway call's CCID included in the SIP messaging and stored in this metadata. Can be used to correlate recordings with gateway calls. |
callcontrollerip |
The CUCM or CUBE where the recording was initiated. The initial SIP INVITE was sent from this IP. |
errorcode |
Introduced in Version 11 to improve serviceability. CLOSED_ERROR recordings now have a specific error code giving a hint to the cause of the failure.
1 = UNKNOWN 2 = MEDIA_SERVER_ERROR 3 = MEDIA_SERVER_TIMEOUT 4 = SIP_SIGNALING_ERROR 5 = SIP_CANCEL_RECEIVED 6 = NO_MEDIA_RECEIVED 7 = ORPHANED 8 = UNSUPPORTED_CODEC 9 = MEDIA_FORMAT_ERROR
|
removable |
- |
isarchived |
This column is set to true for each recording that has been archived to SFTP using basic archiving feature. |
errordetail |
- |
Step 1. Find a Recording with Known Date and Time.
For example, to play back a recorded call on February 6th started between 18:00 and 18:10 in Greenwich Mean Time (GMT) -5.
First, timeframe must be converted to Unix/Epoch timestamps in milliseconds. Naviagte to http://www.epochconverter.com/ or use a similar tool to obtain Unix time.
In the example after convertion, timestamp values are 1391727600000and1391728200000.
Run this command:
run ora_sql meta "select pkid from recordingsession where startdatetime between 139172600000 and 1391728200000"
With this pkid, run this command:
run ora_sql meta "select path, protocol, port, nodeid from recordingurl where pkid='<yourpkidfromabove>'"
Use this path and nodeID where the recording resides. you can formulate the URL needed to stream the audio from the path given.
For example, rtsp://<IP Address of NodeID returned>/<path returned>.
Step 2. Fine Total Count of Recordings.
This command gives total count of recordings that are presented in MediaSense server.
run ora_sql meta “select count(*) from recordingsession”

Step 3. Find Count of Pruned Recordings.
In order to find out total count of pruned recordings on medisense server use this command with Unix time stamp:
run ora_sql meta “select count(*) from recordingsession where state='4' and startdatetime < 1460572844000"

Step 4. Delete Error Call Recordings From MediaSense.
The prune policy works only on successfully recorded calls. Therefore, there is a need to delete error call recordings manually.
According to MediaSense, every successful call is a call which has recorded media. If one track was recorded and no Real-time Transport Protocol (RTP) was received for the other track, the call is considered CLOSED_NORMAL. This call is considered as a successful call since media exists for the call.
Pruning is an activity that attempts clearance of old recordings to make a way for new ones in the disk.
When a call ends with an ERROR, it essentially has no data recorded. ( i.e. there is no media recorded, hence, there is no reason to delete that recording).
So, error recordings metadata are not deleted from MediaSense as part of pruning polocy. They continue to be seen in the MediaSense Search and Play.
Here is an example image from MediaSense Search & Play (sorted with oldest to newest), all the error recording are present and not Pruned.

Now, if you want to delete error recordings metadata, the MediaSense API can be used to achieve that requirement.
The API's name is deleteSessions, and here is a sample usage (Sessions can be a list). You can refer to the MediaSense Developer Guide for more detail.

Run this command to delete the recording session completely from MediaSense. This can be done to delete one session or a list of sessions.
Run this command to find error sessions
admin:run ora_sql meta "select pkid from recordingsession where state='3'"
To limit the output on the screen and to view only 10 recordings at a time, run this command:
admin:run ora_sql meta "select LIMIT 10 pkid from recordingsession where state='3'"

Use the instructions in Step 4 to delete the error recordings metadata.