Programming w/ Rizzi Frank: July 2017

Sitecore log4net ADO Logging Part 2: The ASR This is part of a two part post on logging Sitecore to a database using ADO.
This post is split into two parts:

Part 1: The custom appender for log4net
Part 2: The ASR report for viewing the logs including a custom detail screen (this post)

Now that I have a log of all of the errors, it was time for me to come up with a way to view it. My first instinct was to create a custom angular listing page - and I still may do this - but the easier way was to use something existing and the Advanced System Reporter was a good fit.

The Code

The first step (after, of course, installing the ASR package) was to create a custom Scanner. To do this, I inherited from the BaseScanner, as follows:

    public class AdoNetLogScanner : ASR.Interface.BaseScanner {

Created a reference to the Sitecore SqlServerApi (using our log4net connection string):

        private SqlServerDataApi dataApi = new SqlServerDataApi(
            Sitecore.Configuration.Settings.GetConnectionString("log4net")
        );

Then overriding the Scan method:

public override System.Collections.ICollection Scan() {

I can then return a list of objects. In this case I created a reader and iterated through creating result LogItem instances:

string query = @"SELECT [ID], [Date], [Thread], [Level], [Logger], [Message], [Exception], [MachineName], [CurrentUser], [Roles],  [SitecoreItemID], [SitecoreItemName], [Language], [IpAddress], [ForwardedIpAddress], [HttpReferrer], [HttpUrl], [HttpMethod], [FormVariables], [HttpUserAgent], [HttpQueryString], [HttpCookies]     from dbo.[log]";

    string whereClause = "";
    if (!string.IsNullOrEmpty(Level)) {
    whereClause += string.Format(" and [Level] = '{0}'", Level);
    }

    if (!string.IsNullOrEmpty(FromDate)) {
        whereClause += string.Format(" and [Date] >= '{0} 00:00:00.000'", FromDate);
    }

    if (!string.IsNullOrEmpty(ToDate)) {
        whereClause += string.Format(" and [Date] <= '{0} 23:59:59.999'", ToDate);
    }

    if (whereClause.Length > 0) {
whereClause = " where " + whereClause.Substring(5, whereClause.Length - 5);
}

Sitecore.Data.DataProviders.Sql.DataProviderReader reader =
dataApi.CreateReader(query + whereClause + " order by [Date] desc"); //,

    List<LogItem> resultList = new List<LogItem>();
while (reader.Read()) {
        var result = new LogItem(
            reader.InnerReader.GetInt64(0),
            reader.InnerReader.GetDateTime(1),
            reader.InnerReader.GetString(2),
            reader.InnerReader.GetString(3),
            reader.InnerReader.GetString(4),
            reader.InnerReader.GetString(5),
            reader.InnerReader.GetString(6),
            reader.InnerReader.GetString(7),
            reader.InnerReader.GetString(8),
            reader.InnerReader.GetString(9),
            reader.InnerReader.GetString(10),
            reader.InnerReader.GetString(11),
            reader.InnerReader.GetString(12),
            reader.InnerReader.GetString(13),
            reader.InnerReader.GetString(14),
            reader.InnerReader.GetString(15),
            reader.InnerReader.GetString(16),
            reader.InnerReader.GetString(17),
            reader.InnerReader.GetString(18),
            reader.InnerReader.GetString(19),
            reader.InnerReader.GetString(20),
            reader.InnerReader.GetString(21)
        );
        resultList.Add(result);
    }
    return resultList;

The LogItem is just a simple POCO:

    public class LogItem {
        public long Id { get; private set; }
        public DateTime Date { get; private set; }
        public string Thread { get; private set; }
        public string Level { get; private set; }
        public string Logger { get; private set; }
        public string Message { get; protected set; }
        public string Exception { get; protected set; }
        public string MachineName { get; private set; }
        public string CurrentUser { get; private set; }
        public string Roles { get; private set; }
        public string SitecoreItemId { get; private set; }
        public string SitecoreItemName { get; private set; }
        public string Language { get; private set; }
        public string IpAddress { get; private set; }
        public string ForwardedIpAddress { get; private set; }
        public string HttpReferrer { get; private set; }
        public string HttpUrl { get; private set; }
        public string HttpMethod { get; private set; }
        public string FormVariables { get; private set; }
        public string HttpUserAgent { get; private set; }
        public string HttpQueryString { get; private set; }
        public string HttpCookies { get; private set; }

        public LogItem(long id, DateTime date, string thread, string level, string logger,
string message, string exception, string machineName, string currentUser,
string roles, string sitecoreItemId, string sitecoreItemName, 
string language, string ipAddress, string forwardedIpAddress, 
string httpReferrer, string httpUrl, string httpMethod,
        string formVariables, string httpUserAgent, string httpQueryString, 
string httpCookies) {
            Id = id;
            Date = date;
            Thread = thread;
            Level = level;
            Message = message;
            Exception = exception;
            Logger = logger;
            MachineName = machineName;
            CurrentUser = currentUser;
            Roles = roles;
            SitecoreItemId = sitecoreItemId;
            Language = language;
            IpAddress = ipAddress;
            ForwardedIpAddress = forwardedIpAddress;
            HttpReferrer = httpReferrer;
            HttpUrl = httpUrl;
            HttpMethod = httpMethod;
            FormVariables = formVariables;
            HttpUserAgent = httpUserAgent;
            HttpQueryString = httpQueryString;
            HttpCookies = httpCookies;
        }
    }

Next we need a Viewer. Feel free to add any columns or additional icons:

public class AdoNetLogViewer : ASR.Interface.BaseViewer {
    private readonly string ICON_WARN = "Applications/32x32/warning.png";
    private readonly string ICON_ERROR = "Applications/32x32/delete.png";
    private readonly string ICON_INFO = "Applications/32x32/information2.png";
    private readonly string ICON_AUDIT = "Applications/32x32/scroll_view.png";

    public override string[] AvailableColumns {
get { 
return new string[] { 
"Id", "Level", "Date", "Message", "User", "SitecoreItemId"
}; 
}
    }

    public override void Display(DisplayElement dElement) {
        Debug.ArgumentNotNull(dElement, "element");

        var logElement = dElement.Element as LogItem;
        if (logElement == null) return;

        dElement.Icon = GetIcon(logElement);

        foreach (var column in Columns) {
            switch (column.Name) {
                case "id":
                    dElement.AddColumn(column.Header, logElement.Id.ToString());
                    break;
                case "level":
                    dElement.AddColumn(column.Header, logElement.Level.ToString());
                    break;
                case "date":
                    dElement.AddColumn(column.Header, logElement.Date.ToString("yyyy-MM-dd HH:mm:ss"));
                    break;
                case "message":
                    dElement.AddColumn(column.Header, logElement.Message.Substring(0, Math.Min(logElement.Message.Length, 200)));
                    break;
                case "user":
                    dElement.AddColumn(column.Header, logElement.CurrentUser);
                    break;
                case "sitecoreitemid":
                    dElement.AddColumn(column.Header, logElement.SitecoreItemId);
                    break;
            }
        }
        dElement.Value = logElement.Id.ToString();
    }

    private string GetIcon(LogItem logElement) {
        switch (logElement.Level) {
            case "AUDIT":
                return ICON_AUDIT;
            case "WARN":
                return ICON_WARN;
            case "INFO":
                return ICON_INFO;
            case "ERROR":
                return ICON_ERROR;
        }
        return string.Empty;
    }
}

The Sitecore Changes

Create a new item for the Scanner:

/sitecore/system/Modules/ASR/Configuration/Scanners/Ado Net Log Scanner
Assembly: Mindshift.SC.AdoLogging
Class: Mindshift.SC.AdoLogging.Reporting.AdoNetLogScanner
Attributes: Level={LogLevel}|FromDate={FromDate}|ToDate={ToDate}

Create a new item for the Viewer:

Item: /sitecore/system/Modules/ASR/Configuration/Viewer/Ado Net Log Viewer
Assembly: Mindshift.SC.AdoLogging
Class: Mindshift.SC.AdoLogging.Reporting.AdoNetLogViewer
Attributes: 
Columns:
* Id - id
* Level - level
* Date - date
* Message - message
* User - user
* Sitecore Item Id - sitecoreitemid

Create a new item for the Report itself:

/sitecore/system/Modules/ASR/Reports/Ado Net Log

Then choose the Scanner and Viewer from above.
You should then be able to run the report and see your records.

Wednesday, July 5, 2017

Sitecore log4net ADO Logging Part 2: The ASR

The Code

The Sitecore Changes