Cisco Support Community
cancel
Showing results for 
Search instead for 
Did you mean: 
New Member

Overview Skills IPCC express v. 8.5

Hi,

we are using Cisco IPCC express in version 8.5.

Is there any possibility to generate a report / have a list of all the users with their different configured skills?

The only possibility which we found was "Subsystems -> RmCm -> Skills" -> but in this list for the different Skill we only found the user configured for a skill but not with the exact configured skill like 1-10.

Such a report of all the users with their actual configured skills would be extremly helpful.

BR

Michael

8 REPLIES
Silver

Overview Skills IPCC express v. 8.5

It's possible, you'll need to run some sql (easiest to do it from the command line). Check out the last post on this thread: https://supportforums.cisco.com/message/3450252#3450252

Brian

please rate all helpful posts!

New Member

Overview Skills IPCC express v. 8.5

Hi Brian,

thank you for your answer.

I just tried this SQL at UCCX server at getting a good output:

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

I just tried to use this SQL with a perl script which is perfectly working with CUCM to transfer the output into a CSV-file.

But this perl withc UCCX is not correclty working:

#!/Program Files/Cisco/perl

use strict;

use warnings;

use SOAP::Lite +trace => 'debug';

use Data::Dumper;

use MIME::Base64;

#define variables

my $uccxip;

my $axl_port = "8443";

my $user;

my $password;

my $desc;

my $error_text;

my $sql = "db_cra select s.skillname, rsm.competencelevel, r.resourceLoginID, r.extension, r.resourceFirstName, r.resourceFirstName,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";

#get CUCM IP, username and password

print "Communications Manager IP: ";

$uccxip = <>;

print "AXL Username: ";

$user = <>;

chomp($user);

print "AXL User Password: ";

$password = <>;

chomp($password);

#set up soap

my $cm = new SOAP::Lite

encodingStyle => '',

uri => 'http://www.cisco.com/AXL/API/1.0',

proxy => "https://$uccxip:$axl_port/axl/" ;

#disable certificate check

BEGIN { $ENV{PERL_LWP_SSL_VERIFY_HOSTNAME} = 0 }

#authenticate to CUCM

$cm = Login($cm,"$user","$password");

#axl request

my $axldata = SOAP::Data->name( "sql" => $sql );

my $res = $cm->executeSQLQuery( $axldata );

#show the returned value

my $file = "UCCX_skills.csv";

open(FH, "> $file") or die "\nCan`t open the file $file\n";

unless ($res->fault) {

my( $reply ) = $res->paramsall();

$desc = $reply->{row};

foreach(@$desc) {

print FH "$_->{skillname}\;";

print FH "$_->{competencelevel}\;";

print FH "$_->{resourceLoginID}\;";

print FH "$_->{extension}\;";

print FH "$_->{resourceFirstName}\;";

print FH "$_->{resourceFirstName}\;";

print FH "$_->{teamname}\;";

}

close(FH);

}

else

{

$error_text = $res->faultcode.' '.$res->faultstring."\n";

print $error_text

}

################################################

sub Login

{

$cm->transport->http_request->header (

'Authorization' => 'Basic ' . encode_base64("$user:$password", ''));

return $cm;

}

Do you know such perl and why it`s not working correcly with UCCX?

Or is there any other possibilty to get the SQL into a CSV-file?

BR

Michael

Silver

Overview Skills IPCC express v. 8.5

UCCX doesn't have the same AXL Soap Webservice as far as I'm aware, so this won't work (I assume that's what your perl script is using).

Try downloading the informix drivers and use a free SQL Management tool such as squirrel.

See this doc:

https://supportforums.cisco.com/docs/DOC-16595

Brian

Please rate helpful posts

Super Bronze

Overview Skills IPCC express v. 8.5

Hi All

From the server CLI (i.e. an SSH session) run this:

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

With putty you can log it to text and then import to Excel as a CSV for further manipulation.

Regards

Aaron

Aaron Please remember to rate helpful posts to identify useful responses, and mark 'Answered' if appropriate!
New Member

Overview Skills IPCC express v. 8.5

Dear both,

thank you for your answers.

Rgd. SQuirreL SQL Client I will have a look.

Output from putty I know and it`s working. Thanks.

The perl-script would have the advantage that we could schedule this.

But for the first manuel output from putty is also OK.

BR

Michael

Super Bronze

Overview Skills IPCC express v. 8.5

Hi

Unless you're very keen on perl, I would try the AxlSqlToolkit - download it from the Applications/Plugins page of CUCM where RTMT etc are.

It's some simple java code that you can use to submit SQL queries to the DB.

Aaron Harrison

Principal Engineer at Logicalis UK

Please rate helpful posts...

Aaron Please remember to rate helpful posts to identify useful responses, and mark 'Answered' if appropriate!
New Member

Overview Skills IPCC express v. 8.5

Hi,

as written perl with the SQL query is working fine with CUCM but not with UCCX.

BR

Michael

Super Bronze

Overview Skills IPCC express v. 8.5

Ah yes - post here from Wes Sisk (http://puck.nether.net/pipermail/cisco-voip/2011-December/025463.html) looks like it's not possible to query anything but the default DB via AXL.

Regards

Aaron

Aaron Please remember to rate helpful posts to identify useful responses, and mark 'Answered' if appropriate!
936
Views
0
Helpful
8
Replies
CreatePlease to create content