BlackBelt K2

Logging Framework - Log to a SQL Table

I'm sure many of you have heard about the K2 [blackpearl] Logging framework by now or have seen Matt's post on how to create a custom logging event.

So today we're going to look at one of the options in the logging framework to log directly to a DB table. This has the advantage that you can quite easily filter and report on the results, and can make it much easier to reach the results in a distributed or secured environment. What I mean by this is that text log files dropped into a folder on your production box is not always easily accessible by anybody other than your administrator. So what if the developers need to troubleshoot an issue or you want to monitor for a certain logging event, then text files are just not good enough. But with your results dropped in a DB table it is easy to create some front-end view over that table to present the results in a browser or some other tool, and still maintain DB security etc. Some options (and we'll probably go into more detail on these in follow up posts) are to create an ASP.net page with a data grid that points to the DB table. You could even use the Dynamic SQL Service Object and expose this table as a Smart Object, if you had a good reason to do that.

So lets enable logging:

1. Browse to you hostserver/bin folder (usually located at C:\Program Files\K2 blackpearl\Host Server\Bin\HostServerLogging.config) and open the file.
2. The first area that you will be interested in today is the <Extention name="ArchiveExtension"...> section. This contains the two properties that will define the connection to the DB where you want to log to, and also the database name. Note that by default it uses the HostServer database, so it gets connection string to that from the K2HostServer.config file located in the same folder as above. 
3. If you want to use a different database than the HostServer DB then you will also have to place this connection string in the K2HostServer.config file, but the catch is that all the connection strings in this file are encrypted. So unless you speak fluent triple DES or whatever, you will have a hard time writing your own connection string in there. So here's the trick:
• Backup your K2HostServer.config file and then comment out the current <ConnectionStrings> section, so that there are no <ConnectionStings/> sections available.
• Now copy the <ConnectionStrings> placeholder part that is commented out at the beginning of the file and replace the values of the SQL server with the correct value for where your real host server is located.
• Add one additional line of the <add name="CustomLogDB" connectionString="... bla bla"/> in between the <ConnetionStrings><ConnetionStrings/> section pointing to your custom DB so it looks roughly like this:
• <connectionStrings>
        <add name="HostserverDB" connectionString="Data Source=mySQLServer;Initial Catalog=HostServer;Integrated Security=SSPI" providerName="System.Data.SqlClient" />
        <add name="CustomLogDB" connectionString="Data Source=mySQLServer;Initial Catalog=CustomLogDB;Integrated Security=SSPI" providerName="System.Data.SqlClient" />
  </connectionStrings>
• When the K2 HostServer is started up it will automatically encrypt these config settings for us and store them securely, so we don't have to worry about doing any encryption ourselves.
4. Note that IF you do use a different DB than the HostServer DB, then you will need a table called LogArchive with exactly the same fields as this table in HostServer. BUT, fear not because if your DB does not have such a table when the logging framework starts logging it will automagically create this table for you. But do not create an empty table called LogArchive in your DB and assume that the fields inside the table will be created for you. It will only check if that table exists and create it for you if it does not.
5. The next area that is of importance is the <LogLocationSettings/> where you will find the ArchiveExtension setting. To enable logging you have to change the Active key to "True" (note that it's case sensitive), and you can select what LogLevel you want to receive in your logging DB. The options are "All", "Debug", "Info", "Warning", "Ignore" and "Error".
6. For any changes in the config files to take effect you have to restart the HostServer service (no, not the whole machine), so go into Services and stop and start the "K2 [blackpearl] Server" service.

Once the service started running again you should be able to go check in the LogArchive table and see the messages being logged there. Lekker man! From there it's up to your imagination on how you can display this in some nice and user friendly UI to authorized users. Remember that there are other logging options also, including the MSMQ option which could also be quite handy, so we'll investigate that in another post.