Powershell: Speaking with Exhange 2007 and SQL Server

Recently I completed a script which picks up message tracking logs from Exchange 2007 and dumps them into a SQL Server. We are creating Analytical reports from the SQL Server e.g. Top Senders/Receivers, monitoring tools and the number of emails sent by them etc. Normally this process of dumping message tracking logs used to take us around 30-60 manual minutes per week. Now it takes around 5 minutes. And if in the future, as planned, I am able to put the script in a task scheduler I would be able to further reduce it to 0 minutes.

Sharepoint 2010 - Recovering Deleted Pages

Murphy's law states that if 'anything can go wrong, it WILL'. And it did for me recently, while I was uploading a file to my knowledge base document library hosted in Sharepoint 2010. While providing the location of where to save the file, I just copy/pasted the URL from my browser, where I had previously opened my knowledge base document library. As soon as I clicked on 'Save'. I knew that I have done something wrong. 

SharePoint 2010 – Workflow History

SharePoint 2010, has a powerful feature called Workflows. I am sure you have heard about it. It is a feature which depending upon your programming intellect, can be used in the out-of-the-box mode, or you can write complex codes and routines to fulfil your heart’s desire.

Receiving Blank Emails From SharePoint Workflow

 
I had designed a workflow in SharePoint 2010, which is sending us reminder emails one hour before the scheduled start time of an Operational Calendar (list of type calendar) in our SharePoint team site. The feature is working quite well, but on and off, we were receiving some emails with blank values.

Workflow Design
It is a simple workflow which starts on every new item created or any existing item modified. The workflow will start but pause till the ‘Start Time’ minus one hour. Once the specific time comes, the workflow will send an email with the details of the activity e.g. Activity Start Time and some other fields from our Operational Calendar list.

Problem
The feature is working fine but sometimes we receive an email with blank values e.g.
 

Dear Team,
Please note the below mentioned activity is scheduled to start in the following time. Kindly take appropriate steps.
Description
Start Time: 1/1/0001 12:00:00 AM
DCA #:
Agility Ticket #:
Vendor Ticket #:
Location:
Activity Status:
On completion of the activity make sure to mark the event as 'Completed'.
Thanks,

The only clue is that the email is generated from the workflow linked with the Operational Calendar. The operational calendar has many entries and since no relevant details were mentioned in the email therefore it is impossible to find out the specific entry causing the generation of this alert/email.

Problem Found
I found that some users, after creating an entry in the operational calendar and starting the workflow, were deleting the entry. However, I want to share the diagnostics which I did, so that anyone can resolve this particular or related issues.

Diagnostics Done
1. Through research it was found that SharePoint 2010 makes a hidden list for every team site called ‘Workflow History’. In this list, it keeps the details for all generated workflows with their results which are linked with the site. Therefore all workflows running on site, document library or list level, make an entry in this list whenever they are executed.
2. The workflow history can be accessed, by appending ‘lists/workflow history’ to the site URL.
3. However since the list contains the results of all workflows for all lists/document library etc, therefore I needed to find the entries for all the workflows generated for the list ‘Operational Calendar’.
4. In order to find the List ID, we open the site in SharePoint Designer. Then we go to the ‘Lists  & Libraries’ > Operational Calendar. Once there we can view the ‘List ID’ in the ‘List Information’ box. Check the below screen shot.



5. Then I filtered the ‘Workflow History’ list by this specific list id. There are many common ways to filter a SharePoint list, you can use anyone of them.

6. Then I searched the date/time of when the blank email was received in the ‘Date Occurred’ column of the ‘Workflow History’ list. ‘Date Occurred’ contains the date/time of when the workflow was generated and the ‘Description’ field contains the result of the generated workflow.
7. E.g. the email was received on Sat 29/06/2013 01:01 AM, so while searching the ‘Workflow History’ table, I found the below workflows which were executed on Sat 29/06/2013 01:01 AM (please note that there can be a difference of 1 minute)



8. Normally one email will correspond to one entry, but on Sat 29/06/2013 01:01 AM I received two blank emails. Therefore I found two entries in the ‘Workflow History’ table.
9. I searched the ‘Primary Item ID’ for the each entry in the ‘ID’ column of the ‘Operational Calendar’ and failed to find both in my list ‘Operational Calendar’. This means that the entries were deleted from the ‘Operational Calendar’.
10. I filtered the ‘WorkFlow History’ by the ‘Primary Item ID’ of the deleted item. E.g. 1097, which gave me two entries



11. From the above screenshot it can be deduced that the entry was created on ‘5/23/2013 10:35 PM’ and paused. Sometimes later the entry was deleted (not shown here) but when the time came for the workflow to end the pause, it executed as scheduled.

Root Cause
Once an entry is created in the ‘Operational Calendar’, the workflow is initiated with the ‘Description’ as ‘Pausing Until <start time>’.
Deleting the entry from the ‘Operational Calendar’ does not mean that the initiated workflow will be stopped/removed as well. So when the specified time (start time) comes, the workflow tries to execute but finds no corresponding entry in the ‘Operational Calendar’, therefore instead of putting relevant values, it puts the blank values and sends the email.

Solution Applied
The users have to be informed that whenever they are going to delete an entry in the Operational Calendar, they have to stop/terminate the workflow before they delete the entry. It can be done through the below steps

1. Open the entry and click on workflows



2. Under ‘Running Workflows’ click on the single entry of the workflow which you need to terminate.


3. Click on ‘Terminate the Workflow now’



4. Press okay to confirm the delete. Immediately under the ‘WorkFlow History’ section you will find the workflow with the status as ‘Cancelled’.

5. Similarly in the ‘Workflow History’ list, you will find the corresponding ‘Description’ as ‘Workflow OC-WorkFlow was canceled by Amad Uddin Malek.’

Authorities That Be

Recently during my study time, I remembered one story that happened during my MBA MIS course and I couldn’t help smiling. I thought that I should write about it and see what morals I can pick from there. So the below is a write up which I can say with my limited memory is 80% based on 100% true facts :)

If Scientists Designed Lamps, They'd Look Like This

These are some cool looking lamps!




If Scientists Designed Lamps, They'd Look Like This | Wired Design | Wired.com:

SSRS-Putting Date Range in Footer Area

Add a text box, placeholder in the footer area. Double click on ''. Put the below code in the expression. 'MonthFromDate' and 'MonthToDate' are variables which are accepting values entered by the user. 

=FormatDateTime(Parameters!MonthFromDate.Value, DateFormat.longdate) " - " FormatDateTime(DateAdd("d", -1, Parameters!MonthToDate.Value), DateFormat.LongDate)