Event logging mechanisms in SharePoint and other .net applications

While developing business applications, it’s important to implement a logging infrastructure to help other developers, administrators and System support engineers to trace the issue. Many different approaches are in practise today, although in this blog I am going to write different scenarios and different approaches we have adopted within different projects so far.

Scenario 1:

Application type - ASP.NET application

In one of asp.net web application for hospital management, on an event of processing a pdf document, results and logs were inserted into database. This required implementing a custom logging mechanism.

Scenario2:

Application type - Windows forms

Custom migration tool for migrating from documentum to SharePoint, we used custom text file based logging mechanism using log4net.dll. This is very nice custom tool available handling multithreading and highly configurable to define file names, new file creation rule and much more. Visit http://www.codeproject.com/Articles/140911/log4net-Tutorial for more info.

Scenario3:

Application type - SharePoint 2007 custom build intranet application

While developing a custom content management system where support team and infrastructure administration teams were two different silos, a hybrid logging approach was implemented. Logs which can be useful at L3 and L2 support tickets, were written to custom SharePoint list. Also other logs were written to ULS logs.

Note: Building logs based on SharePoint list, requires a good trashing mechanism to delete logs older than 2 months.

Scenario4:

Application type- SharePoint 2010 publishing site based intranet application

There are couple of options implementing logging within SharePoint applications. Selecting one of these options depends on many factors like timelines, clients preference, existing organizations approved practices, etc.

1. SharePoint Guidance library: provides easy to reuse methods utility that can be easily employed in time crunched projects.
   http://msdn.microsoft.com/en-us/library/ff798385.aspx
2. Building custom LoggingService implemented by inheriting SPDiagnosticsServiceBase. This provides better classification of ULS logs over logs created using method SPDiagnosticsService.Local.WriteTrace
   http://blog.mastykarz.nl/logging-uls-sharepoint-2010

I haven’t got any opportunity to explore any new and better mechanism available with SharePoint 2013 so far.

Online learn application for html5, CSS3, jQuery

Recently came across a very good online academy to learn jquery, javacript, Html5 and CSS3. It’s very intuitive and gives quick starting for newbie's.
http://www.codecademy.com/learn
Thought of sharing it.

Release management: Need for custom script to Auto publish documents

In one the release, a custom sequential approval workflow on document library was upgraded to make use of Publishing approval mechanism

This enabled the process to make use of draft item security section to provision
• Check out- check in mechanism with a security filter (check out documents were shown to document author and approver alone)
• Previous approved versions of checked out document were searchable to all users

Enabling this to existing setup with live users has many challenges. And a proper release planning is required for such implementations.
This upgraded workflow and document library setting updates developed and tested well in Dev environment. This setup was to be updated to more than 6 production sites. Some discrepancies were found on these sites.

After analysing different project sites, following facts were identified:
• Few document libraries has checked out approved document.
• One document library had minor versions of the document – which are in approved state. (this was due to document library level settings wasn’t done appropriately)

These challenges were mitigated by executing custom scripts to undo checkout and auto publish documents to major version before associating the new upgraded workflow to the document library.

SPFile object provides below methods that were useful doing this: 1. Publish ( string comment);
2. Approve ( string comment);
3. UndoCheckOut ();

We need to be cautious that this script is ran when custom workflow is configured to not allow any new workflow instances on the specific document library. And again along with custom script building to support this task, as a part of stakeholder management, the document authors and site owners were engaged to explain the reason and communicate the actions taken on their documents as a System approved major versions.

Automating relaunching workflows

This is in continuation of previous blog where I promised to highlight more on process of relaunching workflows. When a new version of workflow is republished, the approval task item links get broken for the workflows already started. Instances of the workflows that are started after the workflow is re-published will use the updated custom task edit forms. Restarting workflow consists of steps to

1. Terminate existing workflow – This will also ensure deleting any tasks created for the workflow
2. Start new workflow

Other things to be considered before stating this process

1. Disabling outgoing email alert notifications on Tasks list (if any)
2. Our custom workflow utilises a configuration list where sending custom emails on task creation was one of the turn on and turn off kind of parameter.

After analysing the number of workflows to be relaunched and availability of change window to execute any custom script on the server, decision about automating this relaunching process can be taken.
For SharePoint 2010 implementation - Here is a good source for power shell commandlets script for restating workflows.
Useful commandlet:

• [Microsoft.SharePoint.Workflow.SPWorkflowManager]::CancelWorkflow($wf);
• $SPList.Parentweb.site.workflowmanager.startworkflow($item, $WFAssociation, $WFAssociation.AssociationData)

Here below is a good example available : http://oszakiewski.net/eric/stop-and-restart-specific-item-workflows-in-sharepoint-2010-powershell For SharePoint 2007 implementation - a custom console application can be created using SP object model using below methods

• SPWorkflowManager.RemoveWorkflowFromListItem(SPWorkflow workflow)
• SPWorkflowManager.StartWorkflow(SPListItem item, SPWorkflowAssociation association,string eventData)

Managing SharePoint Workflow releases

Couple of months ago, I was in working on project where SharePoint based workflows were enhanced in phases and republished in multiple releases. Such projects with multiple releases in tight timelines, release planning plays an important role. And hence thought of making a checklist of things to be considered while doing such multiple releases.

1. Identification and bookings for change windows for deployment process
2. Identification of number of workflows in progress
3. Identification of process to relaunch the workflows which are in progress status
4. Engaging Change management team to getting communications for
   • Change in the workflow process
   • System outages if any
   • Relaunch of the workflows, broken approval links

In coming blogs will try to list down the challenges faced and workarounds used for them.

Comparing PL-SQL with TSQL

Recently I came across a project where I need to connect to oracle database for fetching my result set. Having always involved in microsoft technology stack projects, and very familier with the T-SQL, I find working with PL-SQL wasnt that different. Although I had to google for some function names corresponding to sql functions I was comfortable with.

functions comparison between PL-SQL and T-SQL

Function	T-SQL	PL-SQL
Current date	GetDate() GetUTCDate()	Sysdate Current_date
Current timestamp	SysDateTime	Current_TimeStamp systimestamp
Get maximum value of the column	Max()	max()
Get year from date field	Year(DOB)	to_char(DOB, ‘yyyy’)
Get Month from the date field	Datename(mm,DOB) Other formats m, month	to_char(DOB, ‘MON’) Other formats can be MM, MONTH, mon
Get Day of week	Datename(dw, DOB)	to_char(DOB, ‘DAY’)
Specific Date format	SELECT CONVERT(VARCHAR(8), GETDATE(), 3) AS [DD/MM/YY]	To_char(DOB, ‘dd/mm/yyyy’)
Finding index of (Location of text)	CHARINDEX ( expressionToFind ,expressionToSearch [ , start_location ] ) e.g. CHARINDEX('@','someone@somewhere')	Instr(expressionToSearch, expressionToFind [, start_position [, nth_appearance ] ] ) e.g Instr('someone@somewhere', '@')

Finding tables with given column name

Not in all projects, enough Knowledge transfer is provisioned to understand complete database architecture. In such scenarios, we end up exploring the database tables and columns to understand their relationships or to just find out if such column exists and in which table. In SQL server, we have tables called sys.Tables and sys.Columns and by writing a simple query like below, it can be done.

USE AdventureWorks
GO
SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%EmployeeID%'
ORDER BY schema_name, table_name;

After googling and altering the query, I came up with the PL-SQL query as below:

select a.table_name, column_name,DATA_TYPE,DATA_LENGTH from all_tab_columns a,USER_ALL_TABLES u
where a.TABLE_NAME=u.TABLE_NAME
and column_name like 'FAMILY_TYPE%'

[Note: default T-SQL like clause works as case-insensative, while with PL-SQL like clause works as case-sensative]

First Sunday of October month (Daylight saving ending date)

In SQL server, this requires a bit of query while in PL-SQL a simple function is available

Declare @D Datetime
Set @D = DateTime(‘01/10/2013’)
Select DateAdd(day, (8-DatePart(weekday,
DateAdd(Month, 1+DateDiff(Month, 0, @D), 0)))%7,
DateAdd(Month, 1+DateDiff(Month, 0, @D), 0))

With in PL-SQL

Select next_day(to_date('01/10/2013','dd/mm/yyyy'), 'Friday') from dual