CUCCX 8.0.x and agent/skill report

[raziel78kain]

Hello,

we would try to get on a CUCCX 8.0.x this kind of agent/skill report:

	Skill 1	Skill 2	...	Skill m
Agent 1	Competence Level 1,1	Competence Level 1,2	...	Competence Level 1,m
Agent 2	Competence Level 2,1	Competence Level 2,2	...	Competence Level 2,m
...	...	...	...	...
Agent n	Competence Level n,1	Competence Level n,2	...	Competence Level n,m

That is, a matrix with the agents in the rows, the skills in the columns and the competence levels in the intersections.

Is it possible to get this kind of report, automatically? We want to avoid to open all the agents, one at a time, in the Cisco Unified CCX Administration (Subsystems -> RmCM -> Resources) and get the single printable reports to see the various competence levels for every skill...

TIA and regards.

[Aaron Harrison]

Hi

There's no nice report for that as far as I'm aware.

I use this query up to v7 of UCCX:

select

r.resourceLoginID, r.extension, r.resourceFirstName,

r.ResourceLastName,

t.teamname,

s.skillname,

rsm.competencelevel

from resource r

inner join resourceskillmapping rsm on r.resourceskillmapid = rsm.resourceskillmapid

inner join skill s on rsm.skillid = s.skillid

inner join team t on r.assignedteamid = t.teamid

where r.dateInactive is  null

and rsm.active = 1

and s.active = 1

order by r.resourceloginid
select
r.resourceLoginID, r.extension, r.resourceFirstName,
r.ResourceLastName,
t.teamname,
s.skillname,
rsm.competencelevel
from resource r
inner join resourceskillmapping rsm on r.resourceskillmapid = rsm.resourceskillmapid
inner join skill s on rsm.skillid = s.skillid
inner join team t on r.assignedteamid = t.teamid
where r.dateInactive is  null
and rsm.active = 1
and s.active = 1
order by r.resourceloginid

You can bungle that into informix syntax and execute with a 'run uccx sql' command if you like... the DB schema is the same.

Regards

Aaron

[Aaron Harrison]

For completeness.. something like this from the server CLI/SSH:

run uccx sql db_cra select s.skillname, rsm.competencelevel, r.resourceLoginID, r.extension, r.resourceFirstName, r.ResourceLastName,t.teamname from skill s inner join

resourceskillmapping rsm on s.skillid = rsm.skillid inner join resource r on rsm.resourceskillmapid = r.resourceskillmapid join team t on r.assignedteamid = t.teamid

where s.active = 't' and r.active = 't' order by s.skillname, competencelevel, resourceloginid

Aaron Harrison

Principal Engineer at Logicalis UK

Please rate helpful posts...

[Peter Coggle]

very helpful reply. +5 Thanks.

[Jim Jones]

Bump this again, anybody have a good solution for this type of report in UCCX 9?

[Aaron Harrison]

Hi Jim

What's wrong with the above suggestion? 

If it's just that you want something your CC staff can run, you can put together custom reports to output that info, whether you are using HRC or CUIC; PM me if you need someone to do this for you...

Aaron

[Lisandro Quinteros]

Hi Aron, let me ask you a question... You said that its possible to make with custom report... So I'll need CUIC premium for UCCX to do that? Do you have a snapshot or something of that report so I could see if attend my needs..

Thanks

Lisandro

[Aaron Harrison]

Hi

CUIC is a reporting engine. You would use it to build reports that suit your needs - those reports don't exist until you write them, so no one can provide you a snapshot.

It requires general dev/SQL skills to produce something useful.

Aaron

[Lisandro Quinteros]

Hi Aaron first thanks for you reply, I know how CUIC works and that the reports should be build according the db schema..

But as far as I know its required a premium CUIC license to create custom reports I am not sure if its a premium CUIC license or just premium uccx seat license..

In my case we only got enhanced uccx seats, I thought you already have a template that why I was asking of the snapshot so I could show that snap to the pearsons who dicied if it value or not to buy premium to show them what its possible to do..

Thanks

Lisandro

[Aaron Harrison]

Hi Lisandro

Regardless of what UCCX edition you have (standard, premium or enhanced), you need CUIC Premium to author custom reports. 

I don't have a report of this type, but it would be quite simple to create. It would appear much like any other CUIC report - very similar to a spreadsheet, so you could mock up what it would look like with Excel. That's generally how I get my customers to specify what they need in a report when I'm creating them..

Aaron

[bvanbenschoten]

Thanks, this is awesome and works great.

Question: is there a way to format the output to make it comma delimited ?

I did a pretty good job of copying the output from the SSH session, , saving in a text file and importing it into excel using the "space" as a delimiter.  This worked pretty well but messed up where some of our skill names had spaces.

Still saved me a ton of time.  Thanks for sharing.

[Aaron Harrison]

Hi

Not from the CLI AFAIK. Nothing to stop you building a report in CUIC/HRC or just querying the DB directly, and using a proper SQL tool to export to CSV though.

Aaron

[Kleid Gjataj]

Anyone has an updated query for UCCX 9. It seems that the DB structure changed.

 
admin:show uccx dbtable schema db_cra resource
List of columns in table 'resource' in database 'db_cra' is -
 resourceid (int)
 profileid (int)
 resourceloginid (nvarchar)
 resourcename (nvarchar)
 resourcegroupid (int)
 resourcetype (smallint)
 active (boolean)
 autoavail (boolean)
 extension (nvarchar)
 orderinrg (int)
 dateinactive (datetime year to fraction(3))
 resourceskillmapid (int)
 assignedteamid (int)
 resourcefirstname (nvarchar)
 resourcelastname (nvarchar)

 Command successful.
admin:show uccx dbtable schema db_cra skill
List of columns in table 'skill' in database 'db_cra' is -
 skillid (int)
 profileid (int)
 skillname (nvarchar)
 active (boolean)
 dateinactive (datetime year to fraction(3))