Mar 12, 2012 You can run the query from the CLI using run sql insert query select d.name,d.description,n.dnorpattern as DN from device as d inner join devicenumplanmap as dmap on dmap.fkdevice=d.pkid inner join numplan as n on dmap.fknumplan=n.pkid where d.tkclass=1 order. Cucm Sql Query For Mac 4,8/5 7320 votes. I had to assist a few users with an odd local chat history issue with Cisco Jabber recently where some message.
Contents
Introduction
It is sometimes useful to search in the Call Detail Record (CDR) for calls that are made after a certain time or to a specific number. This document provides the procedures for when you use Query Analyzer in order to search for a call made after a certain date and time, and to specific numbers.
Prerequisites
Requirements
Readers of this document need to be knowledgeable of these topics:
Cisco CallManager 3.x and 4.x administration
SQL database management
With such little resources available, every megabyte counts. However, there are plenty of PowerMac G3’s, iBooks and Powerbooks used by Mac OS 9 fans and some of those models top out at 512 MB or even as low as 160 MB. One of my favorite Classic Macs is the PowerBook nicknamed “.” It’s 500MHz and has only 512MB of memory as one of the RAM slots is broken. NOTICE: Intego’s Mac OS 9 compatible products are available and free to use.
That is assuming the memory was upgraded; in its default configurations, some of those machines had just 32 or 64 MB of memory!
Components Used
The information in this document is based on these software and hardware versions:
Cisco CallManager 3.x
Cisco CallManager 4.x
Note: 10.10.5 download. Although there is a different version of the SQL database in Cisco CallManager 4.x (namely SQL Server 2000), this document still applies.
The information in this document was created from the devices in a specific lab environment. All of the devices used in this document started with a cleared (default) configuration. If your network is live, make sure that you understand the potential impact of any command.
Conventions
Refer to the Cisco Technical Tips Conventions for more information on document conventions.
Open SQL Query Analyzer
All of the examples given in this document are run using Query Analyzer. Complete these steps in order to open Query Analyzer.
Select Start > Programs > Microsoft SQL Server > Query Analyzer.
Log into the server (your local server). For Cisco CallManager 3.x, select Use SQL Server authentication, and enter your username and password. For Cisco CallManager 4.0 and later, select Use Windows NT authentication. Windows NT Authentication is recommended, although the system supports SQL Authentication. Setting Cisco CallManager for mixed mode authentication in release 4.0 and later is not supported. Upgraded earlier versions fail with SQL Server authentication and the system needs to be changed back to Windows NT authentication.
Note: When third party applications need to access the Cisco CallManager database, SQL authentication might be required, but this is not supported by Cisco.
From the database pull-down (top right-hand side of window) select the CDR Database.
Enter a query in the panel.
Date Format in the CDR Database
In order to select all calls after a certain date, you need to convert the date you want into a value in universal time and in seconds since January 1st, 1970.
For example, 973995954 translates to 11/12/00 2:25 AM. Complete these steps in order to decipher the time stamp.
Go to Microsoft Excel.
In cell A1 type the number that is found in the last record for dateTimeOrigination.
In cell A2, paste the formula =A1/86400+DATE(1970,1,1).
Right-click on cell A2 and select format cells.
Under the Number tab select Time where the format is 3/14/98 130 PM.
The result is the actual time in readable format.
Complete these steps in order to convert the day you want into the date.
Enter the date in cell B2 in the format MM/DD/YYYY 00:00 AM.
In Cell A enter the formula =(B2-DATE(1970,1,1))*86400.
Right-click on cell B1 and select format cells.
Under the Number tab, select Number.
This number is the universal time in seconds that is used in the formula.
In order to get the correct time, download the CiscoCDRTime Converter-0.1.0.0.zip.
Caution: This tool is not officially supported by Cisco Systems. If you download, install, or use this tool, you do so at your own risk. Cisco Systems, Inc. is not responsible for correcting problems that may arise as a result of using this unsupported tool.
Search for Calls Made After or Between Two Dates/Times
In order to select all calls after a certain date, you need to convert the date you wish into a value in universal time and in seconds since January 1st, 1970. See the Understanding the Date Format in the CDR Database section of this document.
Go to the window in SQL Query Analyzer and enter the formula select * from CallDetailRecord where dateTimeConnect > '973995954' for all calls made after a date.
If you want to look at all calls that happen between two times, enter the formula select * from CallDetailRecord where dateTimeConnect > '973995954' and datetimeconnect < '1063574868'.
In order to execute the script, click on the check mark at the top of the page and then click the play button.
Search for Calls Made to a Specific Number
In some situations, you might need to find out the extension which called 911 on a specific date. Complete these steps in order to search for calls made to a specific number such as 911.
In order to search for calls that are made to a specific number, you can run a different query.
For example, if you want to look for calls to 911 you can enter the query select * from CallDetailRecord where finalcalledpartynumber='911'.
The bottom of the page displays all the entries in the CDR that have the final called party number equal to 911.
Add a date to this query in order to refine it.
For example, for all calls to 911 after August 1st, 2003, run the query select * from CallDetailRecord where finalcalledpartynumber='911' and datetimeconnect > '1059696000'.
The bottom of the page displays all the entries in the CDR that have the final called party number equal to 911 made after August 1st, 2003. '1059696000' is the universal time in seconds for August 1st, 2003.
In order to see who calls 911, run the query select callingpartynumber from CallDetailRecord where finalcalledpartynumber='911'.
In order to save the results in CSV format, see the Saving the Results of Your Query section of this document.
Note: If you try to find the number of calls made to a number, which is a translation pattern, it does not work. CDR does not record translation pattern; it only monitors DNs and Route Patterns.
Save the Results of Your Query
Once you run the query, if you want to save the data you have into a .CSV file that you can open in Excel, highlight the results as shown in this procedure.
Click on the first column and drag your mouse to the last column, or, click on the grey square above the number 1.
Select File > Save As.
Save it as a .CSV file, and view it in Excel or in Notepad.
Related Information
- Author: admin
- Category: Category
Mar 12, 2012 You can run the query from the CLI using run sql insert query select d.name,d.description,n.dnorpattern as DN from device as d inner join devicenumplanmap as dmap on dmap.fkdevice=d.pkid inner join numplan as n on dmap.fknumplan=n.pkid where d.tkclass=1 order. Cucm Sql Query For Mac 4,8/5 7320 votes. I had to assist a few users with an odd local chat history issue with Cisco Jabber recently where some message.
Contents
Introduction
It is sometimes useful to search in the Call Detail Record (CDR) for calls that are made after a certain time or to a specific number. This document provides the procedures for when you use Query Analyzer in order to search for a call made after a certain date and time, and to specific numbers.
Prerequisites
Requirements
Readers of this document need to be knowledgeable of these topics:
Cisco CallManager 3.x and 4.x administration
SQL database management
With such little resources available, every megabyte counts. However, there are plenty of PowerMac G3’s, iBooks and Powerbooks used by Mac OS 9 fans and some of those models top out at 512 MB or even as low as 160 MB. One of my favorite Classic Macs is the PowerBook nicknamed “.” It’s 500MHz and has only 512MB of memory as one of the RAM slots is broken. NOTICE: Intego’s Mac OS 9 compatible products are available and free to use.
That is assuming the memory was upgraded; in its default configurations, some of those machines had just 32 or 64 MB of memory!
Components Used
The information in this document is based on these software and hardware versions:
Cisco CallManager 3.x
Cisco CallManager 4.x
Note: 10.10.5 download. Although there is a different version of the SQL database in Cisco CallManager 4.x (namely SQL Server 2000), this document still applies.
The information in this document was created from the devices in a specific lab environment. All of the devices used in this document started with a cleared (default) configuration. If your network is live, make sure that you understand the potential impact of any command.
Conventions
Refer to the Cisco Technical Tips Conventions for more information on document conventions.
Open SQL Query Analyzer
All of the examples given in this document are run using Query Analyzer. Complete these steps in order to open Query Analyzer.
Select Start > Programs > Microsoft SQL Server > Query Analyzer.
Log into the server (your local server). For Cisco CallManager 3.x, select Use SQL Server authentication, and enter your username and password. For Cisco CallManager 4.0 and later, select Use Windows NT authentication. Windows NT Authentication is recommended, although the system supports SQL Authentication. Setting Cisco CallManager for mixed mode authentication in release 4.0 and later is not supported. Upgraded earlier versions fail with SQL Server authentication and the system needs to be changed back to Windows NT authentication.
Note: When third party applications need to access the Cisco CallManager database, SQL authentication might be required, but this is not supported by Cisco.
From the database pull-down (top right-hand side of window) select the CDR Database.
Enter a query in the panel.
Date Format in the CDR Database
In order to select all calls after a certain date, you need to convert the date you want into a value in universal time and in seconds since January 1st, 1970.
For example, 973995954 translates to 11/12/00 2:25 AM. Complete these steps in order to decipher the time stamp.
Go to Microsoft Excel.
In cell A1 type the number that is found in the last record for dateTimeOrigination.
In cell A2, paste the formula =A1/86400+DATE(1970,1,1).
Right-click on cell A2 and select format cells.
Under the Number tab select Time where the format is 3/14/98 130 PM.
The result is the actual time in readable format.
Complete these steps in order to convert the day you want into the date.
Enter the date in cell B2 in the format MM/DD/YYYY 00:00 AM.
In Cell A enter the formula =(B2-DATE(1970,1,1))*86400.
Right-click on cell B1 and select format cells.
Under the Number tab, select Number.
This number is the universal time in seconds that is used in the formula.
In order to get the correct time, download the CiscoCDRTime Converter-0.1.0.0.zip.
Caution: This tool is not officially supported by Cisco Systems. If you download, install, or use this tool, you do so at your own risk. Cisco Systems, Inc. is not responsible for correcting problems that may arise as a result of using this unsupported tool.
Search for Calls Made After or Between Two Dates/Times
In order to select all calls after a certain date, you need to convert the date you wish into a value in universal time and in seconds since January 1st, 1970. See the Understanding the Date Format in the CDR Database section of this document.
Go to the window in SQL Query Analyzer and enter the formula select * from CallDetailRecord where dateTimeConnect > '973995954' for all calls made after a date.
If you want to look at all calls that happen between two times, enter the formula select * from CallDetailRecord where dateTimeConnect > '973995954' and datetimeconnect < '1063574868'.
In order to execute the script, click on the check mark at the top of the page and then click the play button.
Search for Calls Made to a Specific Number
In some situations, you might need to find out the extension which called 911 on a specific date. Complete these steps in order to search for calls made to a specific number such as 911.
In order to search for calls that are made to a specific number, you can run a different query.
For example, if you want to look for calls to 911 you can enter the query select * from CallDetailRecord where finalcalledpartynumber='911'.
The bottom of the page displays all the entries in the CDR that have the final called party number equal to 911.
Add a date to this query in order to refine it.
For example, for all calls to 911 after August 1st, 2003, run the query select * from CallDetailRecord where finalcalledpartynumber='911' and datetimeconnect > '1059696000'.
The bottom of the page displays all the entries in the CDR that have the final called party number equal to 911 made after August 1st, 2003. '1059696000' is the universal time in seconds for August 1st, 2003.
In order to see who calls 911, run the query select callingpartynumber from CallDetailRecord where finalcalledpartynumber='911'.
In order to save the results in CSV format, see the Saving the Results of Your Query section of this document.
Note: If you try to find the number of calls made to a number, which is a translation pattern, it does not work. CDR does not record translation pattern; it only monitors DNs and Route Patterns.
Save the Results of Your Query
Once you run the query, if you want to save the data you have into a .CSV file that you can open in Excel, highlight the results as shown in this procedure.
Click on the first column and drag your mouse to the last column, or, click on the grey square above the number 1.
Select File > Save As.
Save it as a .CSV file, and view it in Excel or in Notepad.