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.
• 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…..
Hope you’ve enjoyed it