Custom SQL Queries
This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.
Use the custom SQL filters in Analytics and Tuning Studio views to query log data from Speech Server applications. The custom SQL filters are available according to view hierarchy, as listed in the following table.
|View||Custom SQL Filter|
Session Reports, Session List, and Session Detail views
Custom session SQL filter
Task Reports, Task List, and Task Detail views
Custom session SQL and Custom task SQL filters
Turn Reports, Turn List, and Turn Detail views
Custom session SQL, Custom task SQL, and Custom turn SQL filters
Sample SQL queries are listed in the following sections.
Selecting Turns Containing Audio
To only select turns for which audio is available in the database, add the following code to the Custom turn SQL filter.
TurnInfo.SpeechRequestId IN (SELECT EngineRecoAudio.SpeechRequestId from EngineRecoAudio WHERE EngineRecoAudio.SpeechRequestId IS NOT NULL)
Selecting Sessions Likely From One Caller
To select sessions with the same automatic number identification (ANI), in other words sessions likely to be from the same caller, for the same application within a window of three days, add the following code to the Custom session SQL filter.
SessionInfo.SessionInstanceId IN ( SELECT B.SessionInstanceId FROM SessionInfo AS A INNER JOIN SessionInfo AS B ON A.SourceDeviceNumber = B.SourceDeviceNumber -- The SourceDeviceNumbers must match AND A.ApplicationID = B.ApplicationID -- The sessions must be for the same application AND A.SessionInstanceId <> B.SessionInstanceID -- Session A is a different session to session B AND A.TimeStamp <= B.TimeStamp -- Session A must have occured before session B AND (B.TimeStamp - A.TimeStamp) < 3 -- Session A must have occurred within 3 days before B )
Filtering Turns by Language
To filter on turns in a particular language, use the Language field in a Turn view. For example, English (United States) turns can be selected by adding the following code to the Custom turn SQL filter.
TurnInfo.Language = 'en-us'
Similarly, use es-us for Spanish (United States), de-de for German (Germany), and so on.
Querying On Task Duration
To filter the task views to show only tasks with a duration of at least a certain period, add the following code to the Custom task SQL filter where n is the duration value in milliseconds above which tasks should be returned.
TaskInfo.TaskDuration > [n]
Querying On Transcriptions
To filter a set of turn results on the transcriptions entered for a turn, add the following code to the Custom turn SQL filter where phrase is the complete transcription.
TurnInfo.Transcription = '[phrase]'
To apply a similar filter using a partial match on the transcription, for example a particular word within the transcribed phrase, add the following code to the Custom turn SQL filter where word is the required partial match.
TurnInfo.Transcription LIKE '%[word]%'
Finding Sessions Containing Particular ApplicationDataEvent Properties
To query sessions that contain particular ApplicationDataEvent properties, add the following code to the Custom session SQL filter where class is a string with the value logged in the Class property, subclass is the value logged in the Subclass property, and data is the value logged in the Data property.
SessionInstanceId IN ( SELECT DISTINCT(ADI.SessionInstanceId) FROM ApplicationDataInfo AS ADI WHERE ADI.Class = ???[class]??? AND ADI.SubClass = '[subclass]' AND ADI.Data = ???[data]???)
Finding Turns That Use a Particular Grammar
To select turns that use a particular grammar, add the following code to the Custom turn SQL filter where GrammarURI is any part of the grammar path, name, or rule, such as MyGrammar.cfg or MyPath/MyGrammar.grxml#MyRule.
TurnInfo.TurnInstanceId IN ( SELECT TurnInstanceId FROM TurnInfo TI INNER JOIN SpeechGrammarUsage SGU ON TI.SpeechRequestId = SGU.SpeechRequestId WHERE SGU.GrammarId IN ( SELECT GrammarId FROM Grammars WHERE URI LIKE '%[GrammarURI]%'))
Finding Turns for Which a Complete Grammar Is Available in the Database
Grammar Tuning Advisor cycles are more useful if they are executed only on turns for which a grammar is complete and available in the database. To select turns for which the grammar is complete in the database, add the following code to the Custom turn SQL filter.
TurnInfo.SpeechRequestId IN (SELECT SpeechGrammarUsage.SpeechRequestId FROM SpeechGrammarUsage INNER JOIN Grammars ON SpeechGrammarUsage.GrammarId = Grammars.GrammarId WHERE Grammars.TotalLength > 0)