How to add custom fields to Logging to the DB?

Topics: Logging Application Block
Dec 5, 2011 at 7:30 AM

Hey there,

I want to ask you one question as the latest thread about this action i ask that if by this i will add this custom field to " FormattedMessage " as new line OR will add new column to the " Logging " table.

 

Urgently i want to know that

Dec 5, 2011 at 9:56 AM

If you have custom information that is populated by using a formatter (e.g. Extended Properties or a formatted timestamp) then that
information will be logged in the database in the FormattedMessage column.

If that is acceptable then you could create a custom formatter to add your own custom data which would be inserted into the
FormattedMessage column and you wouldn't have to change the database schema. 

--
Randy Levy
Enterprise Library support engineer
entlib.support@live.com

Dec 5, 2011 at 12:19 PM
randylevy wrote:

If you have custom information that is populated by using a formatter (e.g. Extended Properties or a formatted timestamp) then that
information will be logged in the database in the FormattedMessage column.

If that is acceptable then you could create a custom formatter to add your own custom data which would be inserted into the
FormattedMessage column and you wouldn't have to change the database schema. 

--
Randy Levy
Enterprise Library support engineer
entlib.support@live.com

as you said that i can add my own additional data to just one column " FormattedMessage " what i need to add my special column e.g. username,pagename,.... to let me to generate reports like " select pagename from logging where time == '11:00' "

Dec 5, 2011 at 10:43 PM
Edited Dec 6, 2011 at 7:04 AM

FormattedMessage is a free form text column formatted according to the formatter.  If you want to query on the FormattedMessage column then you will have to use LIKE or parse the column data to locate what you are interested in.  E.g. you could store XML in the FormattedMessage and use SQL XQuery or retrieve into .NET and query using LINQ To XML.

If you have  data in the FormattedMessage column in a format you know then you could query it.  Assume that you store pageName in FormattedMessage as [[PageName]]=myPage.aspx; then you could query it:

select * from [log] where Timestamp > '11-21-2011'and FormattedMessage LIKE '%[[PageName]]=' + 'myPage.aspx;%'

If you wanted to extract the PageName then you would have to parse the FormattedMessage text.

Of course, this is not particularly efficient or elegant.

If you create a custom DatabaseTraceListener with a custom column then you can query on that column as well as having easy access to the column data in your query result.

If you go with a normalized ExtendedProperty table (as per the example I sent you) then you could just query data like this:

SELECT ep.Value as PageName 
FROM [log] AS l 
  LEFT OUTER JOIN ExtendedProperties AS ep ON ep.LogID = l.LogID WHERE l.TIMESTAMP > '11-21-2011' 
  AND ep.[Key] = 'PageName'

Or create some views so you don't have to specify the joins and the view will return properties you are interested in.

--
Randy Levy
Enterprise Library support engineer
entlib.support@live.com

Dec 7, 2011 at 6:42 AM

thanks alot Randy for your great effort, this is rich info that I know

but i asked if it possible to add some additional columns to log table and can to insert some data to it.