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
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)
See Also
Tasks
How to: View Analytics and Tuning Studio Reports