Comparing timesheet and project assignments actual data

From to time to time you may need to check out that your Project Server’s timesheet and assignments actual data are in sync. In order to achieve that you need to build a SQL query that connects and compares the timesheet and assignments data. The final report can be a cube, excel file or whatever report you may need.

In the example below I am going to create 3 SQL views based on the reporting database of Project Server 2007:

• The _Tasks_All view which combines assignment actual data from different tables in a human readable form
• The _Timesheet_All view which combines timesheet actual data from different tables in a human readable form
• The _Timesheet_Diff view which compares the other two custom views and calculates the differences

Connect to your database server where you are hosting your Project Server databases and expand the Project Server reporting database. Right click the views folder and select new view. On the add table popup dialog click “close” and paste the query below in the view builder

SELECT dbo.MSP_EpmAssignment.AssignmentUID,dbo.MSP_EpmResource.ResourceUID,dbo.MSP_EpmResource.ResourceName,dbo.MSP_EpmAssignmentByDay.TimeByDay,
dbo.MSP_EpmAssignmentByDay.AssignmentActualWork - dbo.MSP_EpmAssignmentByDay.AssignmentActualOvertimeWork AS 'AssignmentActualRegularWork',
dbo.MSP_EpmAssignmentByDay.AssignmentActualOvertimeWork,
dbo.MSP_EpmProject.ProjectUID,dbo.MSP_EpmProject.ProjectName,
dbo.MSP_EpmTask.TaskUID,dbo.MSP_EpmTask.TaskName,dbo.MSP_EpmTask.TaskOutlineNumber
FROM dbo.MSP_EpmAssignmentByDay INNER JOIN
dbo.MSP_EpmTask ON dbo.MSP_EpmAssignmentByDay.TaskUID = dbo.MSP_EpmTask.TaskUID INNER JOIN
dbo.MSP_EpmProject ON dbo.MSP_EpmAssignmentByDay.ProjectUID = dbo.MSP_EpmProject.ProjectUID INNER JOIN
dbo.MSP_EpmAssignment ON dbo.MSP_EpmAssignmentByDay.AssignmentUID = dbo.MSP_EpmAssignment.AssignmentUID INNER JOIN
dbo.MSP_EpmResource ON dbo.MSP_EpmAssignment.ResourceUID = dbo.MSP_EpmResource.ResourceUID

Save the view as _Tasks_All and close the builder.

Repeat the preceding procedure to create the _Timesheet_All view

SELECT dbo.MSP_TimesheetResource.ResourceUID, dbo.MSP_TimesheetResource.ResourceName,dbo.MSP_TimesheetLine.AssignmentUID,
dbo.MSP_TimesheetStatus.Description,dbo.MSP_TimesheetTask.TaskUID,
dbo.MSP_TimesheetTask.TaskName, dbo.MSP_TimesheetProject.ProjectUID,dbo.MSP_TimesheetProject.ProjectName,
dbo.MSP_TimesheetActual.TimeByDay,dbo.MSP_TimesheetActual.ActualWorkBillable,
dbo.MSP_TimesheetActual.ActualWorkNonBillable,
dbo.MSP_TimesheetActual.ActualOvertimeWorkBillable,
dbo.MSP_TimesheetActual.ActualOvertimeWorkNonBillable, dbo.MSP_TimesheetActual.PlannedWork
FROM dbo.MSP_Timesheet INNER JOIN
dbo.MSP_TimesheetResource ON dbo.MSP_Timesheet.OwnerResourceNameUID = dbo.MSP_TimesheetResource.ResourceNameUID INNER JOIN
dbo.MSP_TimesheetLine ON dbo.MSP_TimesheetLine.TimesheetUID = dbo.MSP_Timesheet.TimesheetUID INNER JOIN
dbo.MSP_TimesheetPeriod ON dbo.MSP_Timesheet.PeriodUID = dbo.MSP_TimesheetPeriod.PeriodUID INNER JOIN
dbo.MSP_TimesheetStatus ON dbo.MSP_Timesheet.TimesheetStatusID = dbo.MSP_TimesheetStatus.TimesheetStatusID INNER JOIN
dbo.MSP_TimesheetTask ON dbo.MSP_TimesheetLine.TaskNameUID = dbo.MSP_TimesheetTask.TaskNameUID INNER JOIN
dbo.MSP_TimesheetProject ON dbo.MSP_TimesheetLine.ProjectNameUID = dbo.MSP_TimesheetProject.ProjectNameUID INNER JOIN
dbo.MSP_TimesheetActual ON dbo.MSP_TimesheetResource.ResourceNameUID = dbo.MSP_TimesheetActual.LastChangedResourceNameUID AND
dbo.MSP_TimesheetLine.TimesheetLineUID = dbo.MSP_TimesheetActual.TimesheetLineUID INNER JOIN
dbo.MSP_TimesheetPeriodStatus ON dbo.MSP_TimesheetPeriod.PeriodStatusID = dbo.MSP_TimesheetPeriodStatus.PeriodStatusID INNER JOIN
dbo.MSP_TimesheetClass ON dbo.MSP_TimesheetLine.ClassUID = dbo.MSP_TimesheetClass.ClassUID

and _Timesheet_Diff view

SELECT dbo._Timesheet_All.Description as 'Approval',dbo._Timesheet_All.ProjectName, dbo._Timesheet_All.TaskName,ISNULL (dbo._Tasks_All.TaskOutlineNumber,'N/A'),
dbo._Timesheet_All.TimeByDay,dbo._Timesheet_All.ResourceName,
dbo._Tasks_All.AssignmentActualRegularWork AS 'TaskRegularWork',
dbo._Timesheet_All.ActualWorkBillable AS 'TSRegularWork',
dbo._Tasks_All.AssignmentActualOvertimeWork AS 'TaskOvertimeWork',
dbo._Timesheet_All.ActualOvertimeWorkBillable AS 'TSOvertimeWork',
dbo._Timesheet_All.ActualWorkBillable – ISNULL(dbo._Tasks_All.AssignmentActualRegularWork,0) AS 'RegularDiff',
dbo._Timesheet_All.ActualOvertimeWorkBillable - ISNULL (dbo._Tasks_All.AssignmentActualOvertimeWork,0) AS 'OvertimeDiff'
FROM dbo._Timesheet_All LEFT OUTER JOIN
dbo._Tasks_All ON
dbo._Timesheet_All.AssignmentUID = dbo._Tasks_All.AssignmentUID AND
dbo._Timesheet_All.TimeByDay = dbo._Tasks_All.TimeByDay
WHERE dbo._Timesheet_All.ProjectName <> 'Administrative' AND(dbo._Timesheet_All.Description <> 'Rejected')

What we’ve done so far? We have created the _Tasks_all view which reports back:

• AssignmentUID
• ResourceUID
• ProjectUID
• TaskUID
• ProjectName
• ResourceName
• TaskName
• TaskOutlineNumber
• TimeByDay
• AssignmentActualRegularWork
• AssignmentActualOvertimeWork

The _Timesheet_All view which reports back:

• AssignmentUID
• ResourceUID
• ProjectUID
• TaskUID
• ProjectName
• ResourceName
• TaskName
• TimeByDay
• ActualWorkBillable
• ActualWorkNonBillable
• ActualWorkOvertimeBillable
• ActualWorkOvertimeNonBillable
• Description (approval status)
  

And finally the comparison view _Timesheet_Diff which repots back:

• Approval
• ProjectName
• TaskName
• OutlineNumber
• TimeByDay
• ResourceName
• TaskRegularWork
• TSRegularWork
• TaskOvertimeWork
• TSOvertimeWork
• RegularDiff (ActualWorkBillable – AssignmentActualRegularWork)
• OvertimeDiff (ActualOvertimeWorkBillable – AssignmentActualOvertimeWork)

The _Timesheet_Diff view joins the _Timesheet_All and _Tasks_All views on AssignmentUID and TimeByDay fields. I have used a LEFT OUTER JOIN instead of INNER JOIN because I’ve seen that some timesheets cannot bind back to assignments, due to Project changes after timesheets creation by users.

Finally, an easy way to produce a report is to create an excel document with a pivot table based on the _Timesheet_Diff view. A more robust way is to create a Analysis Services Cube or a Reporting Services Report under SharePoint server.

Technorati Tags: From,Project,Server,_Tasks_All,_Timesheet_All,_Timesheet_Diff,Connect,SELECT,MSP_EpmAssignment,AssignmentUID,MSP_EpmResource,ResourceUID,ResourceName,AssignmentActualWork,AssignmentActualOvertimeWork,AssignmentActualRegularWork,MSP_EpmProject,ProjectUID,ProjectName,MSP_EpmTask,TaskUID,TaskName,TaskOutlineNumber,INNER,JOIN,Save,Repeat,MSP_TimesheetResource,MSP_TimesheetLine,MSP_TimesheetStatus,Description,MSP_TimesheetTask,MSP_TimesheetProject,MSP_TimesheetActual,ActualWorkBillable,ActualWorkNonBillable,ActualOvertimeWorkBillable,ActualOvertimeWorkNonBillable,PlannedWork,MSP_Timesheet,OwnerResourceNameUID,ResourceNameUID,TimesheetUID,MSP_TimesheetPeriod,PeriodUID,TimesheetStatusID,TaskNameUID,ProjectNameUID,LastChangedResourceNameUID,TimesheetLineUID,MSP_TimesheetPeriodStatus,PeriodStatusID,MSP_TimesheetClass,ClassUID,Approval,ISNULL,TaskRegularWork,TSRegularWork,TaskOvertimeWork,TSOvertimeWork,RegularDiff,OvertimeDiff,LEFT,OUTER,WHERE,Administrative,ActualWorkOvertimeBillable,ActualWorkOvertimeNonBillable,OutlineNumber,Analysis,Services,Cube,Report,SharePoint,Technorati,Tags,Timesheet,Tasks,Assignments,LiveJournal,actual,data,time,query,compares,excel,create,views,database,view,combines,tables,human,readable,form,click,table,builder,reports,back,comparison,timesheets,compare

Windows Live Tags: From,Project,Server,_Tasks_All,_Timesheet_All,_Timesheet_Diff,Connect,SELECT,MSP_EpmAssignment,AssignmentUID,MSP_EpmResource,ResourceUID,ResourceName,AssignmentActualWork,AssignmentActualOvertimeWork,AssignmentActualRegularWork,MSP_EpmProject,ProjectUID,ProjectName,MSP_EpmTask,TaskUID,TaskName,TaskOutlineNumber,INNER,JOIN,Save,Repeat,MSP_TimesheetResource,MSP_TimesheetLine,MSP_TimesheetStatus,Description,MSP_TimesheetTask,MSP_TimesheetProject,MSP_TimesheetActual,ActualWorkBillable,ActualWorkNonBillable,ActualOvertimeWorkBillable,ActualOvertimeWorkNonBillable,PlannedWork,MSP_Timesheet,OwnerResourceNameUID,ResourceNameUID,TimesheetUID,MSP_TimesheetPeriod,PeriodUID,TimesheetStatusID,TaskNameUID,ProjectNameUID,LastChangedResourceNameUID,TimesheetLineUID,MSP_TimesheetPeriodStatus,PeriodStatusID,MSP_TimesheetClass,ClassUID,Approval,ISNULL,TaskRegularWork,TSRegularWork,TaskOvertimeWork,TSOvertimeWork,RegularDiff,OvertimeDiff,LEFT,OUTER,WHERE,Administrative,ActualWorkOvertimeBillable,ActualWorkOvertimeNonBillable,OutlineNumber,Analysis,Services,Cube,Report,SharePoint,Technorati,Tags,Timesheet,Tasks,Assignments,LiveJournal,actual,data,time,query,compares,excel,create,views,database,view,combines,tables,human,readable,form,click,table,builder,reports,back,comparison,timesheets,compare

WordPress Tags: From,Project,Server,_Tasks_All,_Timesheet_All,_Timesheet_Diff,Connect,SELECT,MSP_EpmAssignment,AssignmentUID,MSP_EpmResource,ResourceUID,ResourceName,AssignmentActualWork,AssignmentActualOvertimeWork,AssignmentActualRegularWork,MSP_EpmProject,ProjectUID,ProjectName,MSP_EpmTask,TaskUID,TaskName,TaskOutlineNumber,INNER,JOIN,Save,Repeat,MSP_TimesheetResource,MSP_TimesheetLine,MSP_TimesheetStatus,Description,MSP_TimesheetTask,MSP_TimesheetProject,MSP_TimesheetActual,ActualWorkBillable,ActualWorkNonBillable,ActualOvertimeWorkBillable,ActualOvertimeWorkNonBillable,PlannedWork,MSP_Timesheet,OwnerResourceNameUID,ResourceNameUID,TimesheetUID,MSP_TimesheetPeriod,PeriodUID,TimesheetStatusID,TaskNameUID,ProjectNameUID,LastChangedResourceNameUID,TimesheetLineUID,MSP_TimesheetPeriodStatus,PeriodStatusID,MSP_TimesheetClass,ClassUID,Approval,ISNULL,TaskRegularWork,TSRegularWork,TaskOvertimeWork,TSOvertimeWork,RegularDiff,OvertimeDiff,LEFT,OUTER,WHERE,Administrative,ActualWorkOvertimeBillable,ActualWorkOvertimeNonBillable,OutlineNumber,Analysis,Services,Cube,Report,SharePoint,Technorati,Tags,Timesheet,Tasks,Assignments,LiveJournal,actual,data,time,query,compares,excel,create,views,database,view,combines,tables,human,readable,form,click,table,builder,reports,back,comparison,timesheets,compare

Auto-naming custom list definition

There are so many times where companies want to standardize their file naming process across document libraries. In order to achieve that, in a Sharepoint document library, you can use a couple of ways, workflow or custom list definition with event receivers.

I have created and uploaded a custom list definition, which saves files by using the following naming rule: 3 first uppercase letters of content type + REF + DOCID. For example a document of content type Correspondence with ID=33 will be named CORREF33.

The event receivers are fired up on the following events: ItemCreated, ItemUpdated, ItemCheckedIn. In all cases, if the content type is or inherited by the content type “folder”, the naming conversion is not taking place. If you have enabled the “Require documents to be checked out before they can be edited” option, the ItemCreated and ItemUpdated event receivers are not evaluated in order to avoid conflicts and items are named after you checked them in.

In order to install the custom list definition, extract the archive somewhere in your Sharepoint Server and execute “setup.bat /i /weburl http://mysite/myweb”. You should now be able to select the “Autonaming Document Library” when you try to create a new list or document library.

http://rapidshare.com/files/204782843/AutonamingListDef.zip.html

Simple Network Security Rules

What about it? You can just put a firewall and here you are! Completely Safe!

A few years ago maybe... But in nowadays the things are little bit different. Instant messaging, P2P networks, inhouse web and mail services, streaming, Trojans, worms, and many more, can open temporary or even permanent "doors" inside your network; even if you have a firewall installed. You should think your network security as a whole! You cannot consider your network safe if:

• everybody has administrative rights
• you have an expired antivirus
• you have not a perimeter security device
• you have not reduced your "attack surface"
• you have not design your "defense in depth" strategy appropriately
• you use weak passwords
• you do not use web filters
• you do not patch your applications and O/S
• you have not trained your users

Administrative Permissions Is Your Enemy

The first thing I do when I am hired by a company to secure their network, is to limit administrative permissions from users' workstations. These workstations are vulnerable when executing processes, like Internet explorer, with administrative rights. But you have to take care of two things first:

• Users' reactions! Most users won't like that kind of restriction. I always explain why I am proceeding to such action, to avoid inconvenience. I am also committed that I will take care of any special requests that may arise.
• Proprietary software side effects. You have to make sure that everything is working as expected.

Weak Passwords, Just Makes You Weak

Weak passwords is a common practice by many companies. Most users use a simple 4-8 character password; like their birthday, a simple word, etc. From the other hand, if you enforce long passwords with complexity, you will probably end up with stickers of hand written passwords on each of your users' monitor. If you have the budget try to introduce smartcards, biometrics or OTP tokens. If you can't, train your users to use passwords that comprise of long phrases like "I like to go for shopping 5 times a week!". These are strong and easy to remember.

Defense!

You have to reduce your attack surface:

• Uninstall unnecessary software
• Disable unnecessary services
• Limit the accounts that are domain administrators
• Configure local firewalls to servers
• Configure local Intrusion Prevention Systems to servers (most times it is part of a firewall)
• Take care of expired antivirus and antispyware systems
• Regularly patch your applications and O/S

Another Brick On The Wall

Choose your security device wisely. Although Cisco and MS ISA Server are safe choices for large organizations; For SMBs may not be. -And why is that? -Budget! If you decide to install an ISA server, for example, you will get an excellent stateful firewall, with excellent proxy capabilities, no web categorization, no antivirus, no antispyware, limited IPS. You have to add web filtering, antivirus, antispyware, IPS with extra cost. If you can, then it is an excellent choice with unique capabilities, if you can't, it would be wise to purchase an all-in-one solution even if it is not state of the art.

Limit The Noise

Try to reduce the dropped packets "noise" from your firewall logs by setting simple filtering rules to you Internet routers:

• Drop private networks, broadcasts and multicasts
• Setup NAT and/or PAT to your public interface

Test, Test and Test

• Purchase a software and run security audits to your servers and workstations
• Purchase a software and try to penetrate your firewalls from the inside and from the outside
• Check the logs of your perimeter security devices. Is there anything unusual?
• Check the logs of your local firewalls. Is there anything unusual?
• Compare perimeter and local firewalls' logs. Is something passing through the perimeter device and logged to the local firewall?

Train Your Users

Simple rules like don't open "strange" email messages and don't press yes to any warnings that may appear to your screen can make the difference.

Altering the default containers for creating users and computers in AD

In the AD structure of the MS windows server product line, the default containers for creating the security accounts for users and computers are of object class CN instead of OU which is more desirable. OUs have significant advantages over CN since they can support Group Policy assignments directly to the OU level, delegation, protection against accidental deletion and better recovery scenarios.

Creating the OU

In order to gain those advantages of the OU structure you need to create one first. To create an OU you simply have to:

1. Open Active directory users and computers (DSA.MSC)
2. Right click on the domain name (Note that OUs can be nested)
3. Select New –> Organizational Unit
4. Enter a name for the OU and press OK.

In the simplest scenario you could create an OU with your company’s name (like MyCompany) and inside that OU one called Computers and one called Users. Now you are able to move the users and the computers accounts to the respective OUs to gain the advantages mentioned previously.

Altering the default container

To complete the procedure, you need to redirect the default containers for creating users and computers. This will save you time and frustration when creating user and computer objects through command line utilities like net user, net computer, netdom add, etc and the domain join user interface in widows operating systems.

Note, that in order to redirect the default users and computers containers your domain must be at least at Windows 2003 functional level.

To alter the default container for your users account (redirusr.exe) to an OU called Users inside an OU called MyCompany to the Mydomain.local domain, type:

c:\windows\system32\redirusr ou=Users,ou=MyCompany,dc=Mydomain,dc=local

To alter the default container for your computers account (redircmp.exe) to an OU called Computers inside an OU called MyCompany to the Mydomain.local domain, type:

c:\windows\system32\redircmp ou=Computers,ou=MyCompany,dc=Mydomain,dc=local

Selling IT Services (Part 1)

Preface

In the modern commercial world selling your product and services is challenging at most of the times. I have seen many colleagues struggling to sell their services, in many cases, without success. I have watched many situations in which numerous sales people are trying to sell products to companies they don’t actually need. They don’t analyze needs, wants and criteria and they don’t take into account the company’s budget.

I am not a salesman. Actually, I am a systems engineer and solution architect. But since I am a free lancer I have to sell my services on my own. I am going to share a summary of a process that I studied in a few sales books and works pretty well for me.

Prepare

One thing that I am sure about is that preparing and meeting the customers’ needs, wants and criteria makes the selling process much easier. Usually, I am following the following preparation steps before I meet a potential customer:

• I gather information about the company: Products, services, organizational charts, stakeholders, offices, etc.
• I make sure that I know pretty well about my products and services.
• I make sure that I know the major competitors’ advantages, disadvantages and prices.
• I plan for Frequently Asked Questions that may arise
• I Plan for any presentation and visual aids I might need
• I mentally rehearsal the sales process and my presentations

It is not wise to go unprepared to a customer’s meeting. By gathering information about the client I can prepare my hooks (I will talk about it later), and potential opportunities that may fit to their organization.

By knowing my products and services I can respond to any questions and concerns that may arise and by knowing the competition I am prepared to deal with potential alternatives.

FAQ planning is very useful to inoculate against customer’s concerns smoothly.

First Impression, hooks and rapport

You have only one opportunity to make a first impression and that’s why it is so important. Most of the times is as simple as smiling, maintaining a good eye contact, offering a firm handshake and properly introducing yourself. Many people are messing that up!

The purpose of hooks is to get people’s attention and make them focus on you. Hooks are supposed to:

• Answer the question: Why should I listen to you?
• Make them to ask: What is it?
  

A hook example for a MS SharePoint solution I often use:

Mr. Doe, You know how difficult is to manage and organize your electronic documents, which makes complex to efficiently exchange corporate information across employees and customers? One of the many benefits of the solution we offer is that it will make the management of the electronic documents an easy task for your company, which means that information exchange will be adequate; leading to better corporate performance and customer satisfaction.

Rapport is one of the most important features or characteristics of unconscious human interaction. It is commonality of perspective, being in "sync", being on the same "wavelength" as the person with whom you are talking. Techniques of gaining rapport include the following:

• Matching your body language
• Using same phrases
• Speech pace
• Breathing rate
• Matching voice tone
• Maintaining eye contact
• Analyzing needs and criteria

Analyzing needs and criteria

It is a very important step in the selling process. Nobody cares about your products or services if they don’t need or want them. So, it is wise to understand what customer wants, needs and what it is important for him in your products or services. Needs analysis is as simple as asking questions and listening to the answers:

• What problems are you experience in information exchange currently in your organization?
• How long does it usually take to find out all the relative information about a project, a subject or a customer? Is this important to you?
• What else would be nice to be delivered in your solution?

Another important element to analyze is the customers’ criteria in selecting products or services. Some criteria examples are service availability, product stability, ease of use, helpdesk support, etc. Understanding customers’ criteria should be stated later in the sales presentation in order to increase the possibility of success.

Understanding customer’s budget

It won’t be in my advantage if a customer expects a solution of 5.000€ and I propose a solution of 25.000€, isn’t that right? I prefer to find out about the customer’s budget before presenting my solution. This saves a lot of time for me and the customer. Asking the question “Have you setup a budget for dealing with those problems?” can lead to three possible answers: No, yes XXX€, yes but I am not willing to share it.

If I get the answer yes XXX€, I have easily overcome my solution’s pricing pitfall. In the other two cases I have to take a different approach in order to find out something about their budget. I ask: “Ok that’s common. Let’s take a look at some round numbers. I have a 5,000€ solution that will solve some of your problems and a 25,000€ solution which will cover most of them. Which approach would you like to follow?”.

In most cases the customer will come up with a number, usually in the low budget range. This can be a strategic move to lower the price. In order to overcome such cases I can respond: “Hmm… We won’t go first class with that budget. Is it possible to trim some of the features?”. This will make them understand:

• that I know about my services and I am not willing to waste my time
• that a cheap solution will not solve all their major problems
  

Finally, I am trying to understand the decision making process. I need to know who and how are going to evaluate my proposal. I need to take into account the concerns of all the decision makers or decision influencers. Later, in my solution presentation, I should also deal with the concerns of those people in order to maximize my possibilities of success.

Summarizing

At this point I have analyzed customer’s needs, wants, criteria and budget. In order to go ahead with my proposal or sales presentation I am using a summarizing bridge:

Mr Doe, can I summarize what we have discussed so far? We talked about the problems of managing your company’s electronic documents and how this affects the exchange of information across your organization and your customers. You said that this is a major problem for your company since misunderstandings are very common due to outdated and invalid data exchange, is that right? We also discussed that a smart search feature across your electronic documents will save many hours of work and frustration. It is important for you and your company to implement a nice user interface, an easy to use search feature and to be possible to brand it with your corporate logo. We also discussed that the available budget to address those problems is around 15,000€ and that you prefer to pay 30% upon proposal acceptance and the rest 70% to span it on the delivery of each phase of the project, correct? Shall we close an appointment for the next week in order to discuss about the final proposal?

In the next part: Writing and presenting the proposal, closing the sale, following up.

Archiving Exchange 2007 mailboxes. The cheap way...

Did you ever come up with the situation to export your Exchange mailboxes to PST files? From time to time, an Exchange administrator needs to export some or all of his organization’s mailboxes out of the exchange database.

In previous versions of exchange sever a tool called ExMerge.exe was able to assist in such cases. In the 2007 version of Exchange server this tool has been replaced by the export-mailbox cmdlet. The export-mailbox cmdlet can be used to export or move the contents of a mailbox, between mailboxes or to a PST file (SP1+). Although export-mailbox is an Exchange server 2007 tool, the source and target mailboxes can be on one of the following:

• Exchange 2007 server
• Exchange Server 2003 SP2 or later
• Exchange Server 2000 SP3 or later

To export data to a .pst file, you must execute the export-mailbox cmdlet from a 32-bit computer that has the 32-bit version of Exchange management tools installed, along with MS Outlook 2003 SP2+ or MS Outlook 2007. Note that export-mailbox cmdlet cannot export data between mailboxes in different forests.

Since export-mailbox is a powershell cmdlet, the output of other cmdlets like get-recipient or get-mailbox can be piped into the tool to streamline functionality. The export-mailbox cmdlet can filter against:

• Subject Keywords (-SubjectKeywords)
• Content keywords (-ContentKeywords)
• Keywords found in subject, body or attachments (-AllContentKeywords)
• Filenames of attachments. You can also include wildcard characters like *.pdf or Article*.txt (-AttachmentFilenames)
• Sender and/or recipient keywords (-SenderKeywords & -RecipientKeywords)
• Specified timeframe (–StartDate & –EndDate)
• Messages locale (-Locale)
• Included or excluded mailbox folders (-IncludeFolders & -ExcludeFolders)

Also, you can choose if you want to just export the contents of a mailbox or move them and delete the exported data from the source (-DeleteContent).

A very common example is an organizational policy which dictates that all employee mailboxes should be exported in an annual basis to offline media for archiving purposes. In order to achieve that goal an exchange administrator should be able to move organizational mailbox data from source mailboxes to PST files using a date filter:

Get-mailbox export-mailbox -EndDate “01/01/2007” -ExcludeFolders “\Contacts”,”\Journal”,” \Junk E-Mail”,”\Deleted Items”,”\Calendar”,”\Sync Issues”,”\Drafts”,”\Notes”,”\Tasks”,”\Outbox” –PstFolderPath c:\MyPSTs –DeleteContent –confirm:$false

If we analyze the above command:

• The Get-Mailbox command returns all mailboxes in the organization
• The Export-Mailbox cmdlet gets its input from the get-mailbox command
• The EndDate switch filters out all items after 01/01/2007
• The ExcludeFolder switch excludes all folders we don’t want to include in our archiving procedure, such as Tasks and Contacts
• The PstFolderPath switch directs the PST files folder location to c:\MyPSTs
• The DeleteContent switch deletes exported items from source mailboxes
• The Confirm:$False global switch disables the confirmation dialog during the mailbox export procedure

The above command can be used with variations in order to include more advance filters like exporting mailboxes with specific subject keywords from a single organizational unit:

Get-Mailbox –OrganizationalUnit “ou=Accounting,ou=Structure,dc=mydomain,dc=local” export-mailbox –SubjectKeywords “Payroll” –PstFolderPath c:\MyPSTs –DeleteContent –confirm:$false

Note that under the –PstFolderPath location multiple files will be created, in the form of accountname.pst, for each respective user defined by the export command.

Auto Configuring Proxy Settings

Did you ever wondered what is this “automatically detect settings” option in LAN settings under connection tab in Internet Explorer? By setting this option you enable the web proxy auto discovery (WPAD) protocol functionality of the web browser. Using this protocol you are directing your web browser to use a special configuration file to automatically set its proxy settings. The benefit from the use of WPAD is the ability to instruct all web browsers in an organization to use the same policy, without configuring each of them manually.

Where is the configuration file?

The configuration’s file location can be published by using two alternative methods: DNS or DHCP. A web browser configured for WPAD, before fetching its first page sends a DHCPINFORM query to its local DHCP server in order to get the URL of the configuration file in the DHCP reply. If DHCP does not provide the desired information, the web browser will try to fetch the configuration file by using DNS resolution. For example if the FQDN of the client computer is computer.subdomain.domain.local, the web browser will try to fetch the configuration file from the following locations:

1. http://wpad.subdomain.domain.local/wpad.dat
2. http://wpad.domain.local/wpad.dat
3. http://wpad.com/wpad.dat (in incorrect implementations)

Hosting the wpad.dat

Since the web browser is trying to fetch the configuration file (wpad.dat) by using the HTTP protocol, the hosting server should be able to do so. The hosting web server must be also set to serve .dat files as “application/x-ns-proxy-autoconfig” mime types and the wpad.dat file should be located at the web site’s root directory. For example in an IIS configuration, you should do the following:

• Go to Start --> settings --> control panel --> administrative tools --> Internet Information Services (IIS) Manager
• Right click the web site node in which you are going to host the wpad.dat file (for example Default Web Site) and select properties
• Select the HTTP Headers tab and press MIME Types button
• In the “MIME Types” dialog box press NEW, type .dat in the extension field and application/x-ns-proxy-autoconfig in the MIME Type field, and press OK.
• Return back to IIS Manager and right click the web site node in which you are going to host the wpad.dat file (for example Default Web Site) and select explore.
• Right click somewhere in the right pane of the IIS snap-in and select new --> text document.
• Rename the document to wpad.dat.
  

Editing the wpad.dat file

The wpad.dat file you have created in a previous step should be populated with a javascript in order to instruct the web browser how to configure its proxy settings. A sample configuration is illustrated below:

function FindProxyForURL(url, host) {
// our local URLs from the domains below mydomain.com don't need a proxy:
if (shExpMatch(url,"*.mydomain.com/*")) {return "DIRECT";}
if (shExpMatch(url, "*.mydomain.com:*/*")) {return "DIRECT";}

// Client computers within this network are accessed through
// port 8080 on proxy1.mydomain.local:
if (isInNet(MyIPAdress(), "192.168.0.0", "255.255.255.0"))
{return "PROXY proxy1.mydomain.local:8080";
}
// All other requests go through port 8080 of proxy2.mydomain.local.
// should that fail to respond, go directly to the WWW:
return "PROXY proxy2.mydomain.local:8080; DIRECT";
}

In the example above, you are directing the web browser to use proxy1.mydomain.local on port 8080 in case the client computer belongs to 192.168.0.0/24 network (script marked with red). In case the client does not belong to the 192.168.0.0/24 network, all web traffic will go through proxy2.mydomain.local and if proxy2 fails to respond, it will try to go directly (script marked with orange). Finally, we instruct the web browser to bypass proxies in case the URL contains the .mydomain.com string (script marked with green). Note that you can add more rules by just adding lines to your configuration file.

Publishing the file location

To publish the file location you need to either setup a DHCP option or setup a DNS record. To setup the DHCP option in a windows DHCP server you need to do the following:

• Go to Start --> settings --> control panel --> administrative tools --> DHCP
• Right click the DHCP server name and select “set predefined options”
• In the Predefined options dialog box press “add”
• In the option type dialog box set the following values:
  Name: WPAD
  Data Type: String
  Code: 252
  Description: WPAD Auto Config Key
• Go back to DHCP snap-in and right click either your scope or server options.
• Select “Configure Options…”
• In the scope options dialog box select the 252 option and in the string value type your wpad.dat file location (like http://wpad.mydomain.local/wpad.dat) and press OK.

Finally, to configure your DNS server, you need to add a WPAD A or CNAME record for the server hosting your wpad.dat file (for example wpad.mydomain.local).