In my last post, I gave you some tips on how pull CDRs out from CUCM using SQL commands (Cisco CUCM – CDR through SQL). Today, I’m going to show other useful reports you can get using SQL commands.
As we are getting all the information from a CLI command, you will need to export the data to an excel file to create something nice to be presented….or even use Python, PHP, to create something automatic for you.
Today I’m going to focus on one command, but with different variables and outputs: show risdb This command displays RIS database table information.
Parameters
• list: displays the tables that are supported in the Realtime Information Service (RIS) database. • query : displays the contents of the RIS tables
So, if you enter the command show risdb list, you will see a list of options in the table that you can explore.
The most common, and used, is the Phone. To access this table, you must use this command: show risdb query phone.
This command is so powerful and useful!!! Here we see everything related to your phones: DeviceName, Descr, Ipaddr, Ipv6addr, Ipv4Attr, Ipv6Attr, MACaddr, RegStatus, PhoneProtocol, DeviceModel, HTTPsupport, #regAttempts, prodId, username, seq#, RegStatusChg TimeStamp, IpAddrType, LoadId, ActiveLoadId, InactiveLoadId, ReqLoadId, DnldServer, DnldStatus, DnldFailReason, LastActTimeStamp, Perfmon Object.
In other words, you can have a list of devices in your Cluster, check each phone is currently Registered or Unregistered, and its information such as IP, Protocol, Model……an excellent Report
But, if you want to explore it a bit more, there are other interesting queries! For example, if you want to have a report about your SIP Trunks, you can use this command: show risdb query sip.
Here you have information about your SIP Trunk, such as name, IPs, descriptions, Status, Peer Status.
This is the Trunk on CUCM:
The Status column (in red) corresponds to the “Service Status” field visible near the top of CCMAdmin’s SIP Trunk page.
0 – No service (The Trunk peer is reachable via TCP, but SIP Options ping is failing) 1 – Full service (All Trunk peers are up and SIP Options ping is successful) 2 – Partial service (A subset of Trunk peers are unreachable) 3 – Unknown (The Trunk peer is unreachable via TCP, or SIP Options ping is not enabled)
The PeerStatus column (in blue) corresponds to the “Status” field for each peer on the SIP Trunk page (near the bottom).
0 – Down 1 – Up
Now it’s up to you to choose a query from RSIDB list and start to explore it. You will find interesting options there, like CTIs, Gateways…..
Today’s post is going to be quick, but may give you some good tips
Last week I got some requests from a Customer, and he needed to know which extensions were recently being used . In order to save licenses, he wanted to delete all phones/lines that weren’t being used.
CDRs on CUCM is a nightmare in my opinion. Mainly when you need to check lots of lines, for a long period.
That’s why I decide to pull this information out directly from SQL.
So here are some useful commands to get CDRs from SQL, and depending on your needs and knowledge, you can use Python or other language to built your own CDR Reporting
First of all, to use the commands you need to ensure that the following steps are taken on your CUCM system:
Activate the CDR Analysis and Reporting (CAR) service on the CUCM publisher node.
Go to System > Service Parameters and set the Cisco Call Manager service “Call Diagnostics Enabled” parameter to true on every cluster node that has the Call Manager service activated.
Now, going to SQL, this is the structure of any SQL Command on CUCM:
admin: run sql select [field list] from [table] where [expression]
The table we are going to use is tbl_billing_data. This table stores all of the elements we need to accomplish the task at hand.
So this is going to be our syntax: run sql select + column + from tbl_billing_data + where + column + (like,in,between,etc).
PS: Please not this command is only acceptable on Publisher.
In my example, I want to get Date (TimeStamp) , Calling and Called Number of all calls from extensions which have “702709” in their numbers and happened this month.
The date must be sent in TimeStamp mode. I use THIS SITE to convert normal date to Timestamp, and vice versa. If you were pulling CDR data into Excel then you can use the following formula (in a new cell) to do the conversion: =(((A1-(6*3600))/86400)+25569)
Right, so this is the command:
run sql car select datetimeOrigination,callingPartyNumber,finalCalledPartyNumber from tbl_billing_data where callingpartynumber like ‘%702709%’ and datetimeconnect > ‘1630486076’
And here is the result:
Well, now you can explore and play a bit more, depending on your needs. You can add more columns, like duration, destIpAddr, callingPartyNumber_uri, originalCalledPartyPattern,callingPartyNumberPartition,EU_SIP_SME_NOS….
Coming back to the DEV topics, today I’m going to give you a quick overview of Unity API. It’s very simple, but it will give different perspectives of what you can automate on Unity.
CUPI is a provisioning API for Cisco Unity Connection that has been designed to be stable and simple to use. It is based on leading industry standards for web-based API development, and provides access to the most commonly provisioned data on Connection systems (users, contacts, distribution lists, and call handlers).
By using CUPI, you can securely do the following:
Create, read, update, and delete users and user configurations
Reset passwords
Create, read, and update distribution lists
Create, read, update, and delete call handlers
Create, read, update, and delete contacts
Getting the Schema Details
All the schema details for all supported object types can be obtained by going to the REST schema page using the URL: http://{server name}/vmrest/schema
In the case of users the schema shows what will come back when fetching the full user data using a URL like this: http://{server name}/vmrest/users/{object_id}
Authentication
CUPI uses the same authentication and authorization scheme that the administration console uses. This means that the objects an administrator has access to when authenticated are determined by the roles to which the administrator is assigned.
Basic Operations
Searching For an user: This request gives us all details about an specific user, searching it by the alias.
You can have your results in XLM or JSON. It’s up to you. The only difference will be on how you manipulate that, to achieve your goal. In XML, you will see this:
If you want to change, or create something new, you need to send a PUT or a POST request. And, depending on your request, you have to search the user via user-objectID, instead of Alias. In this case, you have to send a request to get information from an user, save its ObjectID, and then use it in another request. See some samples below:
Listing User PIN Settings Shows information about user’s PIN:
The following is the result of the above DELETE request: 200 OK
These were only some samples of what you can do using CUPI. Of course, there are dozes, or hundreds of types of request. Any specific request you want to do and don’t know how, let me know in the comments.
Python Libraries (Zeep, urllib3 , requests – installed via PIP)
After installing Python and its libraries, let’s go to the codes!
To run my codes, I use PyCharm….but you can use any other software of your preference.
Firstly, you have to declare your libraries:
*Code will be passed at the end of the article
Now you have to enter your CUCM information, such as IP, username and password.
We are going to use ZEEP to create SOAP requests. In case of any fault, Zeep will show what SOAP envelope that was sent and the response from CUCM AXL.
If you’re not disabling SSL verification, host should be the FQDN of the server rather than IP.
To start with a simple request, I’ll show you how to list Phones.
Have in hands the Cisco DevNet AXL Schema Reference. It will help you to understand each request, which argument you must send as a searchCriteria and which arguments you must expect as returnedTags.
Only declared arguments in the returnedTags will be displayed. The rest will be showed as none.
For example, I want to list a phone, based on the Device Name, and want to have the arguments name, description, model and device pool being returned to me.
The code will look like this:
As I mentioned, if you don’t declare you want to have your argument being returned, it will be displayed as None.
Right. Now, you have to use your Python skills to take any action based on your output.
For example, if you want to isolate the returned tags to save them in a variable, you can use a For Loop to do something like that:
According to the Schema, you don’t have Search Criteria or Returned Tags in the addLine request.
So, the code you be like this:
This is the line we’ve just added:
As I always say…now, sky is the limit!
You can do whatever you want by following the Schema….like add/delete/list Phones, lines, Device Pool, Device Profile, etc, etc, etc…
Hope you liked it
See you!
Whole Code
from zeep import Client
from zeep.cache import SqliteCache
from zeep.transports import Transport
from zeep.exceptions import Fault
from zeep.plugins import HistoryPlugin
from requests import Session
from requests.auth import HTTPBasicAuth
from urllib3 import disable_warnings
from urllib3.exceptions import InsecureRequestWarning
from lxml import etree
transport = Transport(cache=SqliteCache(), session=session, timeout=20)
history = HistoryPlugin()
client = Client(wsdl=wsdl, transport=transport, plugins=[history])
service = client.create_service(binding, location)
def show_history():
for hist in [history.last_sent, history.last_received]:
print(etree.tostring(hist[“envelope”], encoding=”unicode”, pretty_print=True))
As promised in my post about ODBC Connection, (you can read it HERE), I’m going to show you how to create a basic Script using Python to query some information from UCCX, which can be useful to create some personalized Dashboards.
Even though we have many types of reports on CUIC, sometime they don’t meet our expectations by having too much unnecessary information or by lack of information.
I’ have decided to use Python, along with HTML, to create my own Dashboard. So I can have only information I know is 100% useful.
First of all, you have to create the ODBC connection to the server where you are going to place the script. Again, you can use THIS POST to help you out.
Once you have the ODBC Connection working, it’s time to work on your script.
To be able to connect your script to your ODBC, you need to have a PYODBC python Library installed. To be able to better manipulate date and time, I’m also using datetime library.
The first part of the script is used to establish a connection to your ODBC. So you need to fill all its information in the connection strings. It’s important to mention that pyodbc does not even look at the connection string. It is passed directly to the database driver.
To start off my code, I’ll call the libraries and use the command conn = pyodbc.connect to connect to my ODBC.
Connection is now ready! Now it’s time to choose a query to be sent. That query is sent using SQL commands. This means you can use your SQL skills to play with queries and create interesting reports
In the below example, I wanted to know how many licenses are being consumed daily. To do that, I’ll use the SQL command: ” {call sp_license_utilization(‘2021-05-05 00:00:01′,’2021-05-05 23:00:01′,’0′,’1’)}”. The line in the script will be like that:
Then, use Python to manipulate the results according to your needs. In my case, I’m using the datetime to get today’s date. I also created a list to save the values, as this code will check the license each hour, and give me the maximum as a final result.
For example, this is the SQL query to get a list of Agents by Team:select s.resourceLoginID,s.resourceFirstName,s.resourceLastName,s.extension, t.teamname from Resource s inner join team t on s.assignedTeamID = t.teamid where s.active = ‘t’ and t.active = ‘t’ and t.teamname = ‘UCCX_TEAM’ order by t.teamname, s.resourceloginid
Using a simple Select * from rtcsqssummary here csqname = ‘<CSQ Name>’ query you can display more information as this query will return the following information.
Today I’m going to talk about SOAP AXL. A powerful and useful type of communication model. Most of the Cisco Unified Communications Manager (CUCM) APIs are exposed via SOAP-based XML Web Services.
I’ve been using it to create some Dashboards for CUCM!
The Administrative XML Web Service (AXL) is a XML/SOAP based interface that provides a mechanism for inserting, retrieving, updating and removing data from the Unified Communication configuration database.
Developers can use AXL and the provided WSDL to Create, Read, Update, and Delete objects such as gateways, users, devices, route-patterns and much more.
SOAP provides an XML-based communication protocol and encoding format for communication. For example, to describe a phone using XML, you would define the following structure.
Now, how do you know what types of requests you are allowed to make, what types of data those requests require, and what type of response you expect to receive?
This is where the Web Services Description Language (WSDL) comes into play. A WSDL file (along with any associated XML schema files) can be used to fully describe the capabilities of a SOAP API.
Luckily CUCM provides a WSDL file for each of the SOAP-based APIs it supports and there are tools to read WSDL files and then make the SOAP API service methods available easily. The eventual goal is to leverage a programing language such as Python (I’ll cover that in future posts) to interface with the various SOAP API’s, but it helps to manually explore the API using a visual tool that can understand the WSDL file. One of these tools is SoapUI, and you can download it from here HERE.
Let’s see now step by step how to use SOAP and send some requests.
Step 1 – Download the AXL API WSDL File
The CUCM AXL API WSDL file is published on the CUCM server itself, as part of the Cisco AXL Toolkit plugin.
Access your CUCM
Navigate to Application → Plugins and click Find
Next to Cisco AXL Toolkit, click Download. The file axlsqltoolkit.zip is downloaded.
From your Downloads folder, extract this downloaded file (right-click Extract All…) to the default location (should be in the Downloads\axlsqltoolkit folder)
Once extracted, in the schema folder you will notice there are a number of folders. These are for various older CUCM versions. For this lab, we are interested in current. That folder contains the current CUCM’s AXL WSDL (AXLAPI.wsdl) and schema (.xsd) files.
Step 2 – Start SoapUI
Now you can load this WSDL into SoapUI, get things configured, and start sending queries. Follow these steps to load the WSDL into SoapUI.
Launch the SoapUI application.
Close any open Endpoint Explorer or other windows that may show up when launching SoapUI.
Click File → New SOAP Project
For the Project Name enter UCMSOAP
Below that field, for the Initial WSDL file, click Browse. Navigate to your current AXL WSDL file extracted earlier:
Step 3 – Run an AXL Request from SoapUI
Once the API is loaded, you must set some of the default parameters, specifically the CUCM hostname or IP address and the credentials so that they don’t have to be re-entered for every query.
In SoapUI, in the Navigator pane on the left, you’ll see the new project folder named UCMSOAP and the AXLAPIBinding object. Right-click on the AXLAPIBinding and click Show Interface Viewer (same as double-clicking or pressing Enter).
In the AXLAPIBinding properties, select the Service Endpoints tab.
Double-click on CCMSERVERNAME so it can be edited and replaced by the hostname of your CUCM.Press Enter
Double-click on the Username and Password to enter the credentials. Be sure to press Enter for the field to be saved.
Close the AXLAPIBinding window by clicking the X in the right of its blue title bar .
So now SOAP is all set up and ready for issuing queries.
I’ll give you now an example of how to do that.
For example, a basic thing as getting the CUCM Version:
Choose AXLAPIBinding
Scroll Down till getCCMVersion. Expand it and you will find Request 1.
Double-click to open it, and you will find its XML Request.
You will observe there is a ? in the processNodeName field. When a new request is created for an operation in SoapUI, all available options are presented, so there are often many that either need to be removed or filled in with valid data (instead of the default ? placeholder).
So, remove it, and click in the green button to send this request. The Response will show up at right:
You have successfully sent an AXL/SOAP request to CUCM and received a valid response!!
From now on you can start playing with other types of requisitions, like add, update or delete.