I am trying to replicate some stuff I did in UCCX 7 (premiuim) where I was querying directly against the UCCX db_cra to get information about users and prompts. I can't do that directly in UCCX 8 since Informix isn't a supported database integration type (which puzzles me). In any case, I have had some initial success building a linked server in an SQL server, building views and pointing back to the System DSN which isn't directly supported by UCCX. Still that has a bunch more moving parts, so I would like to be able to make the query of the UCCX server directly. Here are the queries I am trying to replicate.
First, I have a query that determines the team name of a resource.
select teamname
from db_cra.dbo.resource resource, db_cra.dbo.team team
where resource.dateinactive is null
and resource.resourceloginid = $UserName and
resource.assignedteamid = team.teamid
I use this for larger installations where there are multiple teams with different schedules, holidays, and such. I keep database tables with the holiday and some status information. I can use a single application to do this when I can determine the team that the user who authenticates to the application (using IPCC extension and CCM PIN). I would love to be able to query UCCX directly via SOAP to get this information.
The second thing is a work in progress. Same scenario where there are multiple teams within a single installation. I keep the prompts for applications in a directory that is named the same as the team. I am trying to replicate the Unity Express style of prompt management where it walks through a directory, plays the prompts, and gives you the option to re-record them. Towards that end, I have isolated the locations in the prompt table. Here is a query that will find all the files in a particular directory (valid in UCCX 7.0.1)
select fileName
from db_cra_repository.dbo.promptsfiletbl
where parentFolderID in
(
select folderID
from db_cra_repository.dbo.promptsfoldertbl
where foldername = 'Marketing'
and parentFolderID in
(
select folderID
from db_cra_repository.dbo.promptsfoldertbl
where foldername = 'en_US'
)
)
In case it isn't obvious, this is a folder that is directly underneath the parent folder for the english (en_us) langauge. I can figure out how to do the rest if I can use SOAP to pull this query from UCCX. It would be fine if it had to be 3 queries. I wrote it a correlated sub-query because that worked fine in query analyzer.
.jpg "VIP Super Bronze"){kind=icon}
