This document was generated from CDN thread
Created by: Gil Burns on 11-03-2011 05:23:50 PM
I'm trying to write a SQL query off the VXMLElement and VXMLElementDetail tables to get the call detail listed in the exact order that the elements were executed. I haven't been able to find a sequence number type column in any of the VXML tables that would indicate the order in which the elements were hit.
The closest I can get is to order by the VXMLElement.enterdatetime and VXMLElement.exitdatetime. The problem is that some of the elements are executed so quickly together that the timestamps are exactly the same, down to the millisecond. So the order in the output is not correct, its random when the elements have the same timestamp.
SELECT Call.callguid,
VXMLElement.enterdatetime,
VXMLElement.exitdatetime,
VXMLElement.elementname,
VXMLElementDetail.varvalue,
VXMLElement.sessionid,
VXMLSession.appname,
VXMLSession.sessionname
FROM VXMLElement
INNER JOIN VXMLElementDetail ON VXMLElement.elementid = VXMLElementDetail.elementid
INNER JOIN VXMLSession ON VXMLElement.sessionid = VXMLSession.sessionid
INNER JOIN Call ON VXMLElement.callguid = Call.callguid
WHERE (VXMLElementDetail.varname = 'value')
AND (VXMLElement.sessionid = 1001771294697602478)
ORDER BY VXMLElement.enterdatetime, VXMLElement.exitdatetime
Does anyone know of a better way to get the call flow detail in the order in which the elements were executed?
Thanks - Gil
Subject: RE: VXMLElement SQL Table Call Sequence
Replied by: Luis Yrigoyen on 14-03-2011 11:20:43 AM
Gil,
Have you checked the activity logs for the app? I believe that shows the order in which the call flow elements were traversed.
Subject: RE: VXMLElement SQL Table Call Sequence
Replied by: Gil Burns on 14-03-2011 11:30:24 AM
Thanks, yes we use the activity logfiles, and they do show the call flow elements in the correct order.
We were attempting to generate this from the reporting tables in case the activity file isn't available (if it is older than the purge deadline) or in case there were more details in the tables than are in the activity logfiles.
If we can't do this as a reporting table query, we'll just have to use the activity logfiles.