Cisco Cisco Customer Voice Portal 8.0(1)

Events for these calls (such as start and end) are recorded in the 

Sample Query and SIP Calls

Detail for a SIP call could be retrieved using the following query:

SELECT Call.*, CallEvent.*

  FROM Call, CallEvent

 WHERE Call.CallGUID=CallEvent.CallGuid

   AND Call.CallGuid='CallGuid';

Where CallGuid is replaced by the value of the CallGuid for which information is desired.

Trunk Utilization

Trunk utilization is a record of state messages from various devices linked to the reporting server

and their current status. The frequency that these messages are written is controlled by the IOS

Gateway (Gateway Utilization). This data captures a point-in-time over time. It is laid out in a

fact table (

) with three dimensions, Resource, Device, and Time.

Since time is not likely to be consistent across all devices, the Usage table has not been codified

as an official dimension table, but rather as a date and time. Queries for usage should aggregate

from this table.

Sample Queries, Trunk Utilization

SELECT avg(ResourceUsed)

  FROM Usage, Resource

 WHERE Resource.ResourceID=Usage.ResourceID

   AND Resource= 'CPU'

   AND Usage.EventDateTime between '2009-05-01 00:00:00' AND '2009-05-31 23:59:59';

Note that BETWEEN is inclusive. This query can also be written as:

AND Usage.EventDateTime >=  '2009-05-01 00:00:00' AND Usage.EventDateTime <=  '2009-05-31

 23:59:59';

SELECT Device, Resource, count(*)

  FROM Device, Resource, Usage

 WHERE Resource.ResourceID=Usage.ResourceID

   AND Device.DeviceID=Usage.DeviceID

   AND Usage.ThresholdReached= 'Y' 

   AND month(Usage.EventDateTime) = 5

 GROUP BY Device, Resource;

Note the use of the Month() function in AND month (Usage.EventDateTime) = 5.

Reporting Guide for Cisco Unified Customer Voice Portal Release 8.0(1)