[cisco-voip] Remote access to the CDR SQL Database

[Andrius Kislas]

Hi, what I did was:

* In SQL server properties changed auth method to "SQL server and Windows".
* Added my own user to "CDR" database.
* Written a small Java program to do a work:

// import java.*;
import java.util.Date;
import java.util.Calendar;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.TimeZone;
import java.sql.Timestamp;
import java.io.*;



public class Connect {
    private java.sql.Connection  con = null;
    private final String url = "jdbc:microsoft:sqlserver://";
    private final String serverName= "1.1.1.1";
    private final String portNumber = "1433";
    private final String databaseName= "CDR";
    private final String userName = "myuser";
    private final String password = "mypassword";
    private final String selectMethod = "cursor";

    // Constructor
    public Connect(){}

    private String getConnectionUrl(){
        return
url+serverName+":"+portNumber+";databaseName="+databaseName+";selectMethod="+selectMethod+";";
    }

    private java.sql.Connection getConnection(){
         try{
              Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
              con =
java.sql.DriverManager.getConnection(getConnectionUrl(),userName,password);
              if(con!=null) System.out.println("Connection Successful!");
         }catch(Exception e){
              e.printStackTrace();
              System.out.println("Error Trace in getConnection() : " +
e.getMessage());
        }
         return con;
     }



    public void tryGET(){
        java.sql.DatabaseMetaData dm = null;
        java.sql.ResultSet rs = null;
        try{
            Calendar now = Calendar.getInstance();
            System.out.println(now.get(Calendar.YEAR));
            now.setTimeInMillis(1117534000000L);
            System.out.println(now.get(Calendar.YEAR));
            SimpleDateFormat formatter = new
SimpleDateFormat("yyyy-MM-dd HH:mm:ss");



            now.setTimeZone(TimeZone.getTimeZone("GMT+4"));

             con= this.getConnection();
             if(con!=null){
             	// PreparedStatement WSps = con.prepareStatement("SELECT
TOP 10 * FROM CallDetailRecord");
             	java.sql.Statement stmt = con.createStatement();

             	FileOutputStream out; // declare a file output object
                PrintStream p; // declare a print stream object

                out = new FileOutputStream("export.txt");

                // Connect print stream to the output stream
                p = new PrintStream( out );


            	
                String query = "SELECT TOP 50 callingPartyNumber,
callingPartyNumberPartition, " +
             			"finalCalledPartyNumber,
finalCalledPartyNumberPartition, duration, " +
             			"dateTimeOrigination, origDeviceName,
finalCalledPartyNumberPartition, DATEADD(ss, dateTimeOrigination +
10800, '19700101') AS DateTime " +
             			"FROM CallDetailRecord " +
             			"WHERE (dateTimeOrigination > 1118116800)";
             	rs = stmt.executeQuery(query);
             	long msec;
             	int duration;
             	String s;
             	String callingPartyNumber;
             	String finalCalledPartyNumber;
             	String finalCalledPartyNumberPartition;
             	int i=0;
             	while (rs.next()) {
             		
             		s = rs.getString("origDeviceName");
             		callingPartyNumber = rs.getString("callingPartyNumber");
             		finalCalledPartyNumber =
rs.getString("finalCalledPartyNumber");
             		finalCalledPartyNumberPartition =
rs.getString("finalCalledPartyNumberPartition");
             		duration = rs.getInt("duration");
             	    msec = rs.getInt("dateTimeOrigination")*1000L;
             	    now.setTimeInMillis(msec);
             	    //float n = rs.getFloat("PRICE");
             	    System.out.println(s + " " + msec);
             	   System.out.println(now.getTime());
             	  System.out.println(formatter.format(now.getTime()));
             	
             	  System.out.println("");
             	  if (duration != 0){
             	  	i++;
             	  	p.println (formatter.format(now.getTime()) + "||" +
callingPartyNumber + "||" + callingPartyNumber + "||" +
finalCalledPartyNumber + "||" + finalCalledPartyNumberPartition);
             	  }
             	}
             	System.out.println(i);
                p.close();

                  rs.close();
                  rs = null;
                  closeConnection();
             }else System.out.println("Error: No active Connection");
        }catch(Exception e){
             e.printStackTrace();
        }
        dm=null;
   }

    private void closeConnection(){
         try{
              if(con!=null)
                   con.close();
              con=null;
         }catch(Exception e){
              e.printStackTrace();
         }
    }
    public static void main(String[] args) throws Exception
      {
         Connect myDbTest = new Connect();
         myDbTest.tryGET();
         //prt();
         //doCalendarTimeExample();
         //doSimpleDateFormat();
         //doAdd();
         //doDateDifference();
         //doGetMethods();
      }

}


Andrius




Ruben Montes (EU) wrote:
> Hi,
>  
> I want to access the CDR sql database with an ODBC connector to retrieve
> the data the billing department needs. I'm using CCM 4.1(3), so the
> authentication method for SQL is Windows mode (the sa user is disabled).
> As Cisco recommends, my CallManager is not in the domain, it is in its
> own workgroup. What should I do to access to the database? Should I use
> the local CCM windows account or what? Is there any possibility to do
> some sort of scheduled job from the SQL enteprise manager to export some
> data (columns)  to a remote machine out of hours, for example?
> What method do you use to export from CallManager the info the billing
> department needs?
>  
> Thanks in advance,
>  
> Ruben
> 
> 
> ------------------------------------------------------------------------
> 
> _______________________________________________
> cisco-voip mailing list
> cisco-voip at puck.nether.net
> https://puck.nether.net/mailman/listinfo/cisco-voip