Working with SharePoint updates versions in Excel

Recently one of my client encountered some errors with his Project Server 2010 farm (EPM). First question from EPM’s experts, could you check if updates between SharePoint 2010 and Project Server 2010 are at same levels?

According to the following Technet article, running queries against SharePoint config DB and EPM DB is not a valid method to check if products are at same levels.

The right path to get this kind of information can be found directly from: central admin → « Upgrade and Migration » → « Check product and patch installation status »

Unfortunately, like many consultant, I don’t have direct access to central admin to get this information. I need to deal with SharePoint farm admins guys. After few discussions, I received the following information (see print screen below).

Pretty hard for me to work with this kind of information in my Excel:

• All data are located in the same cell.
• Random carriage return.

Probably the guy select information from central admin and pasted directly in Excel.

After some research, I found a better way to format them.

If you would like to use the following procedure, your PC where Excel is installed (tested with Excel 2010), must able to connect to central admin.

1. Open Excel
2. Go to « Data » tab
3. Click on « From Web »
4. In « New Web Query » window enter the following address: http://YourServerName:YourTCPPort/_admin/PatchStatus.aspx and enter farm admin credentials.
5. Central admin display now on your screen. Click on yellow box like mentioned in next print screen, and click on « Import » button.

   

6. Data are now arranged in more convenient way (see print screen), for working with excel (filters).

   

   This kind of technique can be very useful if you have a multiple servers and or SharePoint product installed in your farm.

   I hope this post will help someone.

Monitor SharePoint Kerberos sites with f5 Big IP out of the box monitors

Introduction.

Few weeks ago, one of my client asked me to monitor authenticated SharePoint Kerberos sites with f5 Big IP load balancer. I tried to find something around the web without any result. However you can find many blogs (including on f5 site) to monitor authenticated SharePoint NTLM sites with Big IP. I’m pretty new with the Big IP platform, but I have some experiences with SharePoint! Why I cannot use Web App extension to resolve my monitoring problem ? Requirements:

• SharePoint 2010 (not tested with SharePoint 2013 but probably work!!!)
• f5 Big IP 11.4
• The following article should be used for intranet scenario (security reasons).

Basics installation steps:

1. Create a new AD account for read access to your SharePoint site.
2. Create new DNS alias (only for Big IP monitoring not users).
3. Create a new site collection.
4. Extend web app to monitor.
5. Modify or create Big IP Virtual Server.
6. Create your Big IP custom monitor.

Step 1: Create AD service account to monitor your SharePoint site.

Create new Active Directory account for my SharePoint monitoring.

• Account Name : BP_SP_Monitoring
• Password : 3QQ*VsF@dH&C (strong password)
• Additional setting: Password never expires.

Step 2: Create new DNS alias.

Next, create a new DNS alias and assign the same IP address to the URL you would like to monitor.

	New alias settings	URL to monitor (already in place)
Name	cy26yovmgbdd	epm
Fully qualified domain name (FQDN)	cy26yovmgbdd.mydomain.com	epm.mydomain.com
IP address	23.92.225.12	23.92.225.12

I strongly suggest to create the new URL with a complex name (even for an intranet scenario) then you will discourage your SharePoint client to use it. This address must be used only by the Big IP monitor.

Step 3: Create a new site collection.

Now create a new site collection under the Web App to monitor, I strongly recommend keeping this site empty (do not add any sensitive content).

• Web App : epm.mydomain.com
• Site template : Blank site (I used this template to reduce the number of feature activated by default on a SharePoint site)
• Site collection administrator : SharePoint Farm Admin
• Add Active Directory account created previously (f5_SP_monitoring) in the SharePoint reader group.

Step 4: Extend web app to monitor.

Now it’s time to extend the Web App to monitor with Big IP. From the SharePoint Central Administration site choose the web app to monitor with Big IP and extend it :

• Create a new IIS web site : EPM – Monitoring
• Port : 80
• cy26yovmgbdd.mydomain.com (created in step 2)
• Claim Authentication Types or Classic mode (depend your config)
  • Enable Windows Authentication : Enabled
  • Integrated Windows authentication : NTLM

Leave default values for others settings.

Step 5: Create your Big IP custom monitor.

Now connect to your Big IP using the Web UI and navigate to: « Local Traffic » → « Monitors » → « Create » Create a new monitor with settings provided bellow (leave default values for settings not mentioned).

• Name : monitor_sp2010_epm
• Description : My Big IP monitor for EPM Web App (SharePoint 2010)
• Type : HTTP
• Parent Monitor : http (default)
• Configuration : Basic (default)
• Interval : 5 seconds (default)
• Timeout 16 seconds (default)
• Send String : GET sites/_layouts/recyclebin.aspx HTTP/1.1\r\nHost: cy26yovmgbdd.mydomain.com (created in step 2).
• Receive String : 200 OK
• User : f5_monitoring@mydomain.com (created in step 1)
• Password : 3QQ*VsF@dH&C
• Alias Address : 23.92.225.12
• Alias Service Port : 80 → HTTP

Step 6: Modify Application Service (iApps).

When I created my first VS for SharePoint (Kerberos) I used iApps. Now I just need to add the new monitoring URL to it. From the Big IP Web UI navigate to: iApps → Application Services → Your Application Service → Reconfigure What FQDNs will clients use to access the servers? : Add a new Host (the same in step 2) « cy26yovmgbdd.mydomain.com » Leave other fields with the current value

Step 7: Modify or create Big IP Virtual Server.

Finally, assign custom monitor created in step 5 to your Big IP Pool. From the Big IP Web UI navigate to: Local Traffic → Pools → Pool List → Choose pool hosting your VS In Health Monitors section select monitor created in step 5 I hope this post will help someone.