Heads Up :
The post you are writing will appear in a public forum. Please ensure all content is appropriate for public consumption. Review the employee guidelines for the community here.
Created by: null on 19-01-2005 09:59:42 PM I need to import Audiumâ¿¿s activity logs into SQL Server for reporting. I need to generate a report from the database very hour, so I need to update it every hour. Is there a way to make Audium generates a new activity log every hour instead of everyday? I am using Audium 3.4, Tomcat 4.1 and SQL Server 2000.
Thank you.
Subject: RE: Import activity log Replied by: Michael Bochynski on 20-01-2005 03:33:29 PM Hi Loung,
Currently Audium Call Services creates a single activity log per day per application. The workaround is to write a custom script on the OS level to rotate logs every hour.
However, we are currently investigating improvements to Call Services logging mechanism, which would include added flexibility and functionality.
Regards, Michael
Subject: RE: Import activity log Replied by: null on 14-03-2005 12:20:42 AM Hi,
Is the only reference to how it should be configured pages 65 and 66 of the User Guide? Could you provide more information on this process?
I set up the .properties file and running the .BAT file gives a one-line error about SQL server. This could be many things- although I do have the driver in the classpath, have created the database with user and password, and have connectivity to the Microsoft SQL Server.
How can I increase the level of trace out of the program so I can see what's wrong?
I assume the database "ActivityLogging" with a user and password should be created in advance. Does the import look to see if the tables exist, and if not, create them? Have you used the same functions to create tables that you used in User Management?
In other words, will the Microsoft SQL Driver work?
Is there a script to create the schema the import needs?
If there was, perhaps I could use this SQL script to create the tables and then the import would see that they were there and not try to create them, and then the Microsoft SQL Driver would work.
Regards, Geoff
Subject: RE: Import activity log Replied by: Michael Bochynski on 14-03-2005 09:48:55 PM Hi Geoff,
The database has to be created in advance, however the appropriate tables will be created by the script, if they don't exist. Note that the user defined in the properties file has to have access to this database and rights to create tables.
Please post here the full error that is being thrown executing this script.
Regards, Michael
Subject: RE: Import activity log Replied by: null on 15-03-2005 04:19:33 AM Thanks as always for your prompt reply. Evidence has been gathered.
The properties file is as follows: [code:1:a6de8c4988] application_name=Prompter database_type=SqlServer driver_path=C:\MSSQL_JAVA\DRIVER\mssqlserver.jar driver_class=com.microsoft.jdbc.sqlserver.SQLServerDriver database_url=jdbc:microsoft:sqlserver//ccs5:1433/ActivityLogging database_user=cvp database_password=cvp [/code:1:a6de8c4988]
The Microsoft driver lives in the matching directory as shown by a dir in a DOS window: [code:1:a6de8c4988] C:\Cisco\CVP\Server\admin>dir C:\MSSQL_JAVA_DRIVER Volume in drive C has no label. Volume Serial Number is 28EE-152D
I tested the SQL connection by using SQL Query Analyzer on a remote computer. I connected to the host as user cvp with password cvp. It shows that the default DB is ActivityLogging as expected. I have set up the permissions of user "cvp" so that the user can create tables. I test this as follows: [code:1:a6de8c4988]
CREATE TABLE [dbo].[TestList] ( [ListId] [int] NOT NULL) ON [PRIMARY]
SELECT * FROM TestList
DROP TABLE [dbo].[TestList] [/code:1:a6de8c4988]
With all that out of the way, here is what the tool says:
[code:1:a6de8c4988] C:\Cisco\CVP\Server\admin>importLogs.bat The log(s) could not be imported because an error occurred: com.microsoft.jdbc.sqlserver.SQLServerDriver Press any key to continue . . . [/code:1:a6de8c4988]
"an error" - not very informative. I'm wondering (as I said in my first post) if this is a sign of driver incompatibility.
Do you have an undocumented setting for trace? It doesn't seem to take a command line arg. Perhaps something in the properties file?
Regards, Geoff
Subject: RE: Import activity log Replied by: Michael Bochynski on 17-03-2005 12:08:31 AM Hi Geoff,
Microsoft's JDBC driver is not supported by Audium. The recommended driver is the i-net Opti JDBC driver (www.inetsoftware.de). This is the most mature, commercially available JDBC driver out in the market (even more so than Microsoft's free JDBC driver).
With that said, there are steps you can do to try to have Microsoft's JDBC driver to work. First of all, Microsoft's JDBC driver requires all three jar files to work properly, however in the property file you can specify only a single jar file. I would suggest unjaring all three jars, [i:42550baa4f]mssqlserver.jar, msbase.jar, msutil.jar[/i:42550baa4f], and re-package them as a single jar. Next you can set this newly created jar in the driver_path section in the properties file.
Hope this helps, Michael
Subject: RE: Import activity log Replied by: null on 18-03-2005 05:47:09 PM First of all, Microsoft's JDBC driver requires all three jar files to work properly, however in the property file you can specify only a single jar file. I would suggest unjaring all three jars, [i:c417497251]mssqlserver.jar, msbase.jar, msutil.jar[/i:c417497251], and re-package them as a single jar. Next you can set this newly created jar in the driver_path section in the properties file.[/quote:c417497251] That does not alter the error message. Can I turn on tracing?
Dumping the logs into a database is very important for reporting. The customer wants very detailed reports on the customer-Audium interactions. Can we get a little more detail on this process? How does the import facility choose the right (date stamped) log file to import?
I have solved my problem with import into Microsoft SQL Server using the free Java driver supplied by Microsoft. I'm posting here in case someone else finds this information useful.
I first installed MySQL in order to get a good baseline on the import process.
I installed version 4.1.10a-nt (the output of "select version();" from within mysql.exe). I also installed the Java driver mysql-connector-java-3.1.7-bin.jar. I created a database called ActivityLogging and set up the .properties file appropriately.
I was getting different results in the interactive mode from when the .properties file was in place, which was pretty odd. It took me a little while to see what was wrong, and I realised that this was due to not escaping the backslash for the .jar location in the .properties file.
Once I did that, importing into MySQL worked fine. My activity_log_import.properties file looks like this: [code:1:5e6f2c4a37] application_name=Prompter database_type=MySql driver_path=C:\\mysql-connector-java-3.1.7 mysql-connector-java-3.1.7-bin.jar driver_class=com.mysql.jdbc.Driver database_url=jdbc:mysql://dr756301:3306/ActivityLogging database_user=root database_password=mysql [/code:1:5e6f2c4a37] Note that the driver name is different to what is in the User Guide p.66 because I am using a later version of the connector. It's now called MySQL Connector/J rather than MM.MySQL.
Realising that I had made the same error in working with SQL Server (see my post above), I changed the .properties file and correctly escaped the backslashes. Note that I am working with the [b:5e6f2c4a37]one SQL jar[/b:5e6f2c4a37] I had built as Michael had advised.
Now I have a different (and more exact) error. [code:1:5e6f2c4a37] The log(s) could not be imported because an error occurred: [Microsoft][SQLServer 2000 Driver for JDBC]Unable to connect. Invalid URL. [/code:1:5e6f2c4a37]
Once again, I had made a mistake in the .properties file.
For SQL Server, the URL specification is different to MySQL and should be: [code:1:5e6f2c4a37] database_url=jdbc:microsoft:sqlserver//ccs5:1433;DatabaseName=ActivityLogging [/code:1:5e6f2c4a37] Note the semi-colon.
OK, activity log imported into Microsoft SQL just fine.
I can answer my own questions now. The import just looks for all files in the application\log directory that start with something like "activity_log" and imports them. It does not know about time stamps, and if you run the import again it will add the same rows a second time. The table has no primary keys, although session_id and activity_time are not allowed to be null. The columns are: [code:1:5e6f2c4a37] session_id - varchar(100) activity_time - datetime element - varchar(100) category - varchar(50) action - varchar(50) value - varchar(100) [/code:1:5e6f2c4a37]
I would want to schedule the import to run just after midnight when the file has rolled over to the new file with a different date, and move the file into a different directory. That's doable.
Regards, Geoff
Subject: RE: Import activity log Replied by: Michael Bochynski on 21-03-2005 04:18:20 PM Hi Geoff,
Thank you for the detailed information. I apologize for not mentioning the double slashes ( ) in the path for database driver. if you use Windows, it is required. However for Unix-based OSes, only one backslash in path, for expamle /usr/lib/driver.jar, is sufficient.
Note, however, that the URL for database_url parameter is strictly dependent on the driver implementation. While for Microsoft's driver the above URL works fine, not every driver requires parameters as name=value pair. I would strongly suggest referring to the driver's documentation for the exact database URL.
Thanks again, Michael
Subject: reporting Replied by: null on 05-12-2005 01:25:04 PM Hi Can you send me the code so that i can use in my reporting
Subject: RE: reporting Replied by: Michael Bochynski on 05-12-2005 02:10:58 PM Hi Mahesh,
If you are refering to the database driver configuration by mentioning "the code", I would strongly suggest refering to the DB driver documentation on how to set up the JDBC connection. IF you use Microsoft SQL Server, you might want to review this very thread for details how to set up database log importer.
Subject: RE: reporting Replied by: null on 06-12-2005 10:23:14 AM Hi
I want the that has been for reporting. not the database
Subject: RE: reporting Replied by: Michael Bochynski on 06-12-2005 03:54:26 PM Hi Mahesh,
Could you explain in more details what exactly are you referring to?
Regards, Michael
Subject: RE: reporting Replied by: null on 07-12-2005 12:14:09 PM Hi Source code for reporting
Subject: RE: reporting Replied by: Vance Vagell on 07-12-2005 03:56:28 PM Hi Mahesh,
In order to provide the best possible solution for your question, we need some additional information. Could you please provide the following:
How would you like to use your reporting system with Audium Call Services? For example, what kind of data would you like access to?
What reporting software are you using? Alternatively, do you have a custom reporting solution?
Is your question related to importing Audium logs into a database, as per the discussion in this thread? If not, please explain in detail how you would like to perform your reporting.
Once we have this additional information, we can better help you choose the best course of action.
Find answers to your questions by entering keywords or phrases in the Search bar above. New here? Use these resources to familiarize yourself with the community: