Enable Microsoft Update in Windows server 2008 R2

If the windows is configured to receive only the windows update, the below steps can be used to enable Microsoft/office updates via automatic windows update:

  1. Add Microsoft.com in in the trusted sites of internet explorer on the server.
  2. Browse the link: http://update.microsoft.com/microsoftupdate/v6/default.aspx?ln=en-US
  3. Click install, even if the install doesn’t work the Microsoft updates would be enabled on the server.

Configure Custom SQL port for SharePoint

The article is taken from the below link:

https://letitknow.wordpress.com/2013/04/04/cannot-connect-to-database-master-at-sql-server-checklist/#no9

I don’t take any credit for this information.

1. Check you typed the correct name

Well, this is very straightforward but make sure you set up everything on the correct server and you typed that name here, also be careful with typos.

2. Check your instance name

Do you want to use a named instance other than the default one, submit it in the following order: Servername\instancename Make sure it has the same name on SQL server as well among services.

3. Name resolution check

Start a command prompt, ping your machine and review the IP address. Now check the IP addresses on your SQL machine with ipconfig or on the interfaces. If they are matching jump on the next point.

My SP machine resolves this name to an incorrect IP
On my SharePoint machine: resolves this name to an incorrect IP
My SQL machine does not have an IP like that
On the SQL machine: it does not have an IP like that (10.1.1.111)

Otherwise try troubleshooting this name resolution problem. If your SQL server has multiple cards make sure DNS resolves it to the correct one. Review your records in DNS and correct them if necessary.

DNS record is correct and it resolves to the 10.1.1.5 address
DNS record is correct and it resolves to the 10.1.1.5 address

Also make sure that your SharePoint machine’s host file (C:\windows\system32\drivers\etc) does not contain invalid data.

Hosts file
my hosts file contains invalid data

4. SQL server service is running

It’s lame, I know, but I have seen this so just make sure your DB service of the correct instance is running:

Service is not running
Service is not running (SQL Server MSSQLSERVER)

5. TCP/IP protocol for SQL server is enabled

On the SQL box open SQL Server Configuration Manager and under Network Configuration node check that TCP/IP is enabled. (If you modify this don’t forget to restart the service)

TCP/IP protocol is not enabled on my SQL instance
TCP/IP protocol is NOT enabled on my SQL instance

6. Check listening ports and IP addresses

In SQL Server Configuration Manager open the Properties of the enabled TCP/IP protocol. Here you can specify the IP address and listening port of this instance. On the first tab if Listen All is enabled (Yes) the used port is in the IP Addresses tab’s last section, IPAll/TCP port. This means that the service listens on all interfaces on this port. Use netstat -ab to review this.

Listen on all interfaces and use port 14333
Listen on all interfaces and use port 14333
Sql listens on port 14333
According to the settings above SQL indeed listens on port 14333

If it is not the default port (1433) you have to indicate this in your connection string (in Config Wizard’s database server parameter)

On the first tab if Listen All is disabled (No) you can set listening on individual ports and cards just don’t forget to enable at least one!

Listening on this interface is enabled, uses default port
Listening on this interface is enabled, uses default port
Netstat reflects the changes
The netstat output shows the changes nicely

Wit this info in hand you should be able to telnet from the SharePoint machine to the specified port with the name to verify network connectivity.

Telnet failed
Telnet failed

If it does not work check firewall settings on the SQL box and between the servers.

7. Windows firewall

If windows firewall is enabled verify it blocks the connection or not. First enable logging here (do this on the profile you use): Windows Firewall with Advanced Security / Right click and select Properties / Click Customize in the Logging section:

  • Log dropped packets : Yes
  • Note the logfile’s path

Try to connect with telnet again from the SharePoint machine and review the content of the logfile.

Windows firewall blocks my connection
Windows firewall blocks my connection

To remediate this turn windows firewall off on the selected profile (or on all profiles) otherwise create an allow rule for this incoming connection, whichever suits your scenario.

8. Other connectivity check between your servers

If your telnet does not even reach the SQL server and there is nothing in the logfile maybe you have connectivity issues between your machines. Are there any firewalls between them? If yes, create firewall rules or ask your firewall team to do it.

9. SQL Server client alias setting

Type cliconfg on your SharePoint machine and review the alias tab. If there is any entry make sure it points to the correct server and you use the correct alias during configuration wizard.

SQL alias
My SQL alias points to server sql01 on port 1433 so I can use “sql5” in configuration wizard to set up a connection

SQL alias has the advantage to change SQL server later easily (connection parameters) while the server name remains the same for SharePoint.

10. SQL permissions

On SQL server open the Application log in the event viewer and look for ID 18456, Category Logon. Don’t be fooled this won’t be marked as an error or warning, simple Information is the level.

Failed logon with the install account
Failed logon with the install account

Did you start the configuration wizard with your install account? If yes you have to check SQL Logins and assigned roles for your user with Management Studio (connect to your instance). Remember, your setup user needs a Login and have to be a securityadmin and a dbcreator! (http://technet.microsoft.com/en-us/library/ee662513.aspx)

Setup user roles
Setup user roles: yes indeed, he can create databases and manage permissions

If everything seems right you can always check the logs to gain more information, here they are again:

  • Event viewer on the SharePoint machine
  • SharePoint Products Configuration Wizard’s logfile in C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\LOGS (by default and their name’s start with PSCDiagnostics_<date>_<SomeID>)
  • Event viewer on the SQL server
  • Windows firewall logfiles

SharePoint 2013 Error: The server was unable to save the form at this time or Sorry we are having trouble reaching the server.

Very strange issue and after doing lot of searching and troubleshooting the issue was fixed by the below:

The error was coming in creating a new folder and adding a user for site access.

 

  • SharePoint Search Host Controller service may need to restart to free up some memory space.
  • Anonymous Authentication was disabled in IIS. – This fixed the issue for me!
  • “minFreeMemoryPercentageToActivateService” attribute is not set to serviceHostingEnvironment Tag in Web.Config.
  • The farm wasn’t updated with at least March 2013 PU / Service Pack 1.

In case of you got “The server was unable to save the form at this time. Please try again.” for a specific user. – If your list has unique permissions and a user/group has permissions only on that list and has limited or no access at the site level, then you run into this error.

Configure SSL for SharePoint 2010

Reference: https://blog.mastykarz.nl/configuring-ssl-sharepoint-2010-development-environment/

Once in a while, when working with SharePoint solutions you need to test them on sites using SSL just to be sure that everything is working just as intended. Although I have previously wrote about how to configure SSL for SharePoint development environments, SSL configuration in IIS7 has changed slightly. Find out how to configure SSL on SharePoint 2010 development environments.

When working with SSL in development environments there are two scenarios: you are either creating a new Web Application and would like to make it work with SSL from the very first day or you already have a working Web Application and would like to make it work with SSL as well. Because those scenarios are not that different we will focus on the first one which should provide you with sufficient information should you need to add support for SSL to an existing Web Application.

Configuring SSL for a new Web Application

Let’s start off with creating a new Web Application.

For the purpose of this article I will be using the secure.mavention.local host header which I already added to the HOSTS file on my development machine mapped to the 127.0.0.1 IP address.

Parameters for creating new secure Web Application

The most important things that you should keep in mind, while creating a new secure Web Application, is to set the port to 443 (1) and in the Security Configuration section set the Use Secure Sockets Layer (SSL) option to Yes.

Next, to be able to confirm that everything is working correctly, let’s create a Site Collection. You could create a Site Collection using a specific Site Definition/Web Template but for the purpose of simply confirming that we have configured SSL correctly, using the Blank Site Site Definition is sufficient.

Configuration of the new Site Collection used for SSL configuration verification

If you choose to navigate to the newly created Site Collection at this moment all you will see is an error message similar to the following:

‘Internet Explorer cannot display the webpage’ error message displayed in browser

The reason for this error is that, although we have chosen to have a secure site, we haven’t attached any SSL certificate to the IIS Web Site hosting our Web Application.

Creating Self-Signed Certificate in IIS7

To configure an SSL certificate with our Web Application let’s navigate to the IIS Manager.

For the purpose of this article we will use a self signed certificate. If you have a valid SSL certificate you can use it instead by importing it to IIS on your development machine.

In the Connections list select your local IIS server (1). Then from the IIS section select the Server Certificates option (2).

Opening the Server Certificates configuration in IIS7

Next, from the Actions toolpane choose the Create Self-Signed Certificate… option.

Red arrow pointing to the ‘Create Self-Signed Certificate’ option in IIS7

Enter secure.mavention.local as the friendly name of the certificate and confirm the creation process by clicking the OK button.

Creating Self-Signed certificate in IIS7

The next step is to associate the newly created Self-Signed certificate with our Web Application.

Associating SSL certificate with secure Web Application

Although IIS Manager allows you to configure a variety of settings for your Web Sites, one thing it doesn’t support is configuring a secure binding based on hostname.  As soon as you select an SSL certificate to associate with a binding (1) IIS Manager will clear the existing host header (2).

Editing a secure site binding in IIS Manager

If you would confirm your changes you would end up with a broken Web Application. Without the host header you would end up landing on the default site instead of our secure Web Application.

You can associate your Self-Signed certificate with your Web Application in two ways.

Using the AppCmd Command Line tool to associate an SSL certificate with an IIS Web Site

One way of associating an SSL certificate with a website is to use the appcmd Command Line tool to set the hostname to an existing secure binding.

First, in IIS Manager, from the list of available Web Sites select your secure Web Site. Then, from the Actions toolpane select the Bindings… option.

Red arrow pointing to the Bindings option

In the Bindings settings select your SSL certificate (1). Note that IIS Manager will remove the previously set host name for your Web Site (2).

Editing the site binding in IIS Manager

Confirm changes by clicking the OK button.

Next, open the Command Prompt as Administrator, change the directory to C:\Windows\System32\inetsrv and type the following command:

appcmd set site /site.name:"SharePoint - secure.mavention.local443" /bindings.[protocol='https',bindingInformation='*:443:'].bindingInformation:*:443:secure.mavention.local  

Executing this command will add the host name to the existing secure binding for your Web Site. If you navigate back to IIS Manager you should see the host name set on the secure binding.

Secure binding with host name set

And if you edit the binding you should see that the SSL is correctly associated as we did in the previous step.

Note that, although the host name is correctly configured, IIS Manager doesn’t display it in the Edit Binding window.

Although this approach is rather easy to follow it has a flaw as it allows you to use only one SSL certificate for all Web Sites. If you want to use your development environment for working with multiple secure Web Applications, each using a different SSL certificate you should take a look at the next approach.

Binding SSL certificate by using different IP addresses

Another way of binding SSL certificates to Web Sites in IIS is to use a different IP address for every Web Site. The great advantage of this approach is that it can be fully managed through UI and allows you to use different certificates.

The first thing that you have to do is to add another IP address to your network adapter. You can do this in the Advanced TCP/IP Settings of your Network Adapter.

Multiple IP addresses assigned to a single network adapter

Next, you have to change the binding between the host header and the IP address. As I mentioned before, for the purpose of this article I added an entry to the HOSTS file on my machine. To support this approach I have changed the IP address from 127.0.0.1 to 192.168.1.222 which is the new IP address I added to my network adapter.

Hosts file with the secure.mavention.local host name entry modified to point to the 192.168.1.222 IP address

The last step is to modify the binding of your Web Site in IIS. For this open the IIS Manager, from the list of available Web Sites choose the one that hosts your Web Application and from the Actions toolpane choose the Bindings option. In the Site Bindings window select the secure binding and click the Edit… button. In the IP address field type 192.168.1.222 and select the SSL certificate that you want to use. Confirm the changes by clicking the OK button.

Site binding configured to support the additional IP address

When you navigate to the website now you should see the warning about the untrusted certificate and if you ignore it, you should see the Site Collection that you created initially.

Site Collection of the secure Web Application

hide-ribbon-on-sharepoint-2013-using-css

Reference: https://mohitvash.wordpress.com/2013/05/07/hide-ribbon-on-sharepoint-2013-using-css/

 

I recently experienced the migration from SharePoint 2010 to 2013. I was earlier hiding Ribbon using the SPSecurityTrimmedControl and CSS. But in SP 2013 MS has divided whole ribbon in two section: s4-ribbonrow andsuiteBar

Hiding the ribbon is as much as easier as it was in 2010 version. But here we just need to take care of above two mentioned sections. I have gone thorough some of the blogs where people are saying that we have change the html file(Seattle.html or oslo.html) and design manger will take care of creating master page for you (ref: Hide Ribbon).

But there is another traditional way of doing same, follow the given steps:

1) Open the master page file in the SharePoint designer.

2) Add the style tag to hide the ribbon for all the user:

#s4-ribbonrow, #suiteBar { display : none; }

3) Now we have to do some more changes so that at least system account can access the ribbon. Find out the <div id=”s4-ribbonrow”> tag.

4) Insert the following code inside this div:

<!—MS:<SharePoint:SPSecurityTrimmedControl ID=”SPSecurityTrimmedControl2″ runat=”server” PermissionsString=”AddAndCustomizePages”>—>

document.getElementById(“s4-ribbonrow”).style.display = “block”;
document.getElementById(“suiteBar”).style.display = “block”;

<!—MS:</SharePoint:SPSecurityTrimmedControl>—>

5) Now save the master page and deploy or publish it to see the changes.

Happy SharePointing:)

PowerShell Script to delete items from SharePoint List

The criteria for the below script is to delete items that created before 7 days , you can put it as scheduale task to run weeklly to clear the large lists.

Add-PSSnapin Microsoft.SharePoint.PowerShell
[System.reflection.Assembly]::LoadWithPartialName(“Microsoft.SharePoint”)
$web = GetSPWeb “YOUR SHAREPOINT SITE”
$list = $web.Lists[“YOUR LIST NAME”]
$DeleteBeforeDate = [Microsoft.SharePoint.Utilities.SPUtility]::CreateISO8601DateTimeFromSystemDateTime([DateTime]::Now.AddDays(7))
$caml=‘<Where> <Lt> <FieldRef Name=”Created” /><Value Type=”DateTime”>{0}</Value> </Lt> </Where> ‘ $DeleteBeforeDate
$query=new-object Microsoft.SharePoint.SPQuery
$query.Query=$caml
$col=$list.GetItems($query)
WriteHost $col.Count
$col | % {$list.GetItemById($_.Id).Delete()}
$web.Dispose()

Courtesy: https://gallery.technet.microsoft.com/office/PowerShell-Script-to-f4e8ea90

The report server cannot decrypt the symmetric key – Reporting Servie, SSRS, SharePoint

ERROR:

"The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content.

 

Solution:

As the error shows, it is an access denied on a registry entry. A Process Monitor http://technet.microsoft.com/en-us/sysinternals/bb896645.aspx trace can show which registry key needs permissions for the service account of the SSRS service.

Usually, it could be this –

HKLM\Software\Microsoft\Microsoft SQL Server\Reporting Services\Service Applications

A brute force change to permissions at HKLM\Software\Microsoft\Microsoft SQL Server by adding the desired user with Read (propagated to child folders) could show if that’s all that is required.

Assign the service account full permission or if it doesn’t work assign everyone full permission on the desired key.

There may be multiple keys which is giving access denied in the process monitor.

try to fix all of them one by one.

unable to connect to sql server using local sql authentication

Reason 1:

SQL Authentication not enabled: If you use SQL Login for the first time on SQL Server instance than very often error 18456 occurs because Windows Authentication (only) is set in Server properties (security section).

To Access Server Properties, Open SQL Server Management Studio, go to Object Explorer pane (use view if you can’t see it). Use the connect button to connect to database engine for your server. Once connected you will see it in object explorer. Right click server and click properties. Server Properties window will appear.

See below screenshot that might be causing SQL login to fail

You should set Server Authentication to SQL Server Windows Authentication 

SQL Authentication not enabled: If you use SQL Login for the first time on SQL Server instance than very often error 18456 occurs because Windows Authentication (only) is set in Server properties (security section).

To Access Server Properties, Open SQL Server Management Studio, go to Object Explorer pane (use view if you can’t see it). Use the connect button to connect to database engine for your server. Once connected you will see it in object explorer. Right click server and click properties. Server Properties window will appear.

See below screenshot that might be causing SQL login to fail

You should set Server Authentication to SQL Server Windows Authentication 

 

Reason 2: Create the user with the SQL Script:

— Create a new SQL Server Login

exec sp_addlogin @loginame = ‘MyUser’, @passwd = ‘P@ssw0rd’ — Grant the SQL login access to your database. — Create a database user called WebAppUser to which the login is associated Use YourDatabase GO exec sp_grantdbaccess ‘MyUser’ — Create a user-defined database role. exec sp_addrole ‘WebAppUserRole’ — Add the database user to the new database role. exec sp_addrolemember ‘WebAppUserRole’, ‘MyUser’ — Grant the role execute permissions on the stored procedure called sprocname grant execute on sprocname to WebAppUserRole

Powershell Script to Automate Daily Monitoring in SharePoint 2010

Reference is from the site:

http://www.c-sharpcorner.com/uploadfile/fb1b0e/powershell-script-to-automate-daily-monitoring-in-sharepoint/

Introduction

From a SharePoint administration perspective monitoring tasks are very important for maintaining a healthy Farm. Monitoring should be done at specific time intervals. You can then differentiate monitoring into the 3 categories daily, weekly and monthly. This article outlines how to automate some of the tasks for daily monitoring.

What the script does

The daily monitoring script automates the following tasks and captures the information into CSV files:

  • SharePoint services status
  • IISWebsite status
  • AppPool status
  • Disk space info
  • Health analyser report
  • CPU utilization
  • Memory utilization
  • SharePoint server status

Manually doing these tasks consumes time and human error can occur. So the PowerShell script would become handy in this scenario.

There is one more function that is included with this script. This is the most interesting part. The script generates output in CSV files for each of the preceding tasks. There is a separate function that converts all the CSV files into an Excel sheet with tabs separated for each of the CSV file. This is done to consolidate all the reports into a single file and provide it to the customer or upper management for better readability.

Note: This functionality of consolidating files into Excel only works if you have an Excel client installed.

Functions

Function 1

The following piece of code automates the determination of the SharePoint services status:

  1. Function SharePointServices([Microsoft.SharePoint.Administration.SPFarm]$farm)
  2. {
  3.    Write-Host “”
  4.    write-host “Generating SharePoint services report” -fore Magenta
  5.    $output = $scriptbase + “\” + “SharePointServices.csv” “ServiceName” + “,” + “ServiceStatus” + “,” + “MachineName” | Out-File -Encoding Default -FilePath $Output;
  6.    foreach($server in $farm.Servers)
  7.    {
  8.      foreach($instance in $server.ServiceInstances)
  9.      {
  10.     # If the server has the timer service then stop the service
  11.     if($instance.TypeName -eq $timerServiceInstanceName)
  12.     {
  13.        [string]$serverName = $server.Name
  14.        write-host “Generating SP services report for server” $serverName -fore yellow
  15.        $Monitor = “SPAdminV4” , “SPTimerV4” , “SPTraceV4” , “SPUserCodeV4” , “SPWriterV4” , “OSearch14” , “W3SVC” , “IISADMIN” , “C2WTS” , “FIMService” , “FIMSynchronizationService”
  16.        $services = Get-Service -ComputerName $serverName -Name $Monitor -ea silentlycontinue
  17.        foreach($service in $services)
  18.        {
  19.           $service.displayname + “,” + $service.status + “,” + $service.MachineName | Out-File -Encoding Default  -Append -FilePath $Output;
  20.        }
  21.        write-host “SP services report generated” -fore green
  22.     }
  23.      }
  24.   }
  25. }

Function 2

The following piece of code automates the determination of the IIS website details:

  1. Function IISWebsite([Microsoft.SharePoint.Administration.SPFarm]$farm)
  2. {
  3.    Write-Host “”
  4.    write-host “Generating IIS website report” -fore Magenta
  5.    $output = $scriptbase + “\” + “IISWebsite.csv”
  6.    “WebSiteName” + “,” + “WebsiteID” + “,” + “WebSiteState” + “,” + “Server” | Out-File -Encoding Default -FilePath $Output;
  7.    foreach($server in $farm.Servers)
  8.    {
  9.       foreach($instance in $server.ServiceInstances)
  10.       {
  11.      # If the server has the timer service then stop the service
  12.      if($instance.TypeName -eq $timerServiceInstanceName)
  13.      {
  14.          [string]$serverName = $server.Name
  15.          write-host “Generating IIS website report for server” $serverName -fore yellow
  16.              $status = “”
  17.          $Sites = gwmi -namespace “root\webadministration” -Class site -ComputerName $serverName -Authentication PacketPrivacy -Impersonation Impersonate
  18.          foreach($site in $sites)
  19.          {
  20.         if($site.getstate().returnvalue -eq 1)
  21.         {
  22.            $status = “Started”
  23.         }
  24.         else
  25.         {
  26.            $status = “Not Started”
  27.         }
  28.         $site.name + “,” + $site.ID + “,” + $Status + “,” + $serverName | Out-File -Encoding Default  -Append -FilePath $Output;
  29.         }
  30.         write-host “IIS website report generated” -fore green
  31.     }
  32.       }
  33.    }
  34. }

Function 3

The following piece of code automates the determination of the AppPool status:

  1. Function AppPoolStatus([Microsoft.SharePoint.Administration.SPFarm]$farm)
  2. {
  3.    Write-Host “”
  4.    write-host “Generating AppPool status report” -fore Magenta
  5.    $output = $scriptbase + “\” + “AppPoolStatus.csv“”AppPoolName” + “,” + “Status” + “,” + “Server” | Out-File -Encoding Default -FilePath $Output;
  6.    foreach($server in $farm.Servers)
  7.    {
  8.       foreach($instance in $server.ServiceInstances)
  9.       {
  10.      # If the server has the timer service then stop the service
  11.      if($instance.TypeName -eq $timerServiceInstanceName)
  12.      {
  13.         [string]$serverName = $server.Name
  14.         write-host “Generating AppPool status report for server” $serverName -fore yellow
  15.             $status = “”
  16.         $AppPools = gwmi -namespace “root\webadministration” -Class applicationpool -ComputerName $serverName -Authentication PacketPrivacy -Impersonation Impersonate
  17.         foreach($AppPool in $AppPools )
  18.         {
  19.                if($AppPool.getstate().returnvalue -eq 1)
  20.            {
  21.           $status = “Started”
  22.            }
  23.            else
  24.            {
  25.           $status = “Stopped”
  26.            }
  27.            $AppPool.name + “,” + $Status + “,” + $serverName| Out-File -Encoding Default  -Append -FilePath $Output;
  28.            }
  29.        write-host “AppPool status report generated” -fore green
  30.      }
  31.       }
  32.   }
  33. }
Function 4

The following piece of code monitors disk space:

  1. Function DiskSpace([Microsoft.SharePoint.Administration.SPFarm]$farm)
  2. {
  3.     Write-Host “”
  4.     write-host “Generating Disk space report” -fore Magenta
  5.     $output = $scriptbase + “\” + “DiskSpace.csv”
  6.     “Computer Name” + “,” + “Drive” + “,” + “Size in (GB)” + “,” + “Free Space in (GB)” + “,” + “Critical (*)”  | Out-File -Encoding Default -FilePath $Output;
  7.     foreach($server in $farm.Servers)
  8.         {
  9.         foreach($instance in $server.ServiceInstances)
  10.         {
  11.             # If the server has the timer service then stop the service
  12.             if($instance.TypeName -eq $timerServiceInstanceName)
  13.             {
  14.                 [string]$serverName = $server.Name
  15.                 write-host “Generating disk space report for server” $serverName -fore yellow
  16.                 $drives = Get-WmiObject -ComputerName $serverName Win32_LogicalDisk | Where-Object {$_.DriveType -eq 3}
  17.                 foreach($drive in $drives)
  18.                 {
  19.                     $id = $drive.DeviceID
  20.                     $size = [math]::round($drive.Size / 1073741824, 2)
  21.                     $free = [math]::round($drive.FreeSpace  / 1073741824, 2)
  22.                     $pct = [math]::round($free / $size, 2) * 100
  23.                     if ($pct -lt 30)
  24.                     {
  25.                         $pct = $pct.ToString() + “% *** “
  26.                     }
  27.                     else
  28.                     {
  29.                         $pct = $pct.ToString() + ” %”
  30.                     }
  31.                     $serverName + “,” + $id + “,” + $size + “,” + $free + “,” + $pct  | Out-File -Encoding Default  -Append -FilePath $Output;
  32.                     $pct = 0
  33.                 }
  34.                 write-host “Disk space report generated” -fore green
  35.             }
  36.         }
  37.     }
  38. }

Function 5

The following piece of code automates the determination of the health analyzer reports:

  1. Function HealthAnalyserReports()
  2. {
  3.     write-host “”
  4.     write-host “Generating health analyser report” -fore magenta
  5.     $output = $scriptbase + “\” + “HealthAnalyser.csv”
  6.     “Severity” + “,” + “Category” + “,” + “Modified” + “,” + “Failing servers” + “,” + “Failing services”  | Out-File -Encoding Default -FilePath $Output;
  7.     $ReportsList = [Microsoft.SharePoint.Administration.Health.SPHealthReportsList]::Local
  8.     $Items = $ReportsList.items | where {
  9.         if($_[‘Severity’] -eq ‘1 – Error’)
  10.         {
  11.             #write-host $_[‘Name’]
  12.             #write-host $_[‘Severity’]
  13.             $_[‘Severity’] + “,” + $_[‘Category’] + “,” + $_[‘Modified’] + “,” + $_[‘Failing Servers’] + “,” + $_[‘Failing Services’]  | Out-File -Encoding Default  -Append -FilePath $Output;
  14.         }
  15.     }
  16.     write-host “Health analyser report generated” -fore green
  17. }

Function 6

The following piece of code automates the determination of the CPU utilization:

  1. Function CPUUtilization([Microsoft.SharePoint.Administration.SPFarm]$farm)
  2. {
  3.     Write-Host “”
  4.     write-host “Generating CPU utilization report” -fore Magenta
  5.     $output = $scriptbase + “\” + “CPUUtilization.csv”
  6.     “ServerName” + “,” + “DeviceID” + “,” + “LoadPercentage” + “,” + “Status” | Out-File -Encoding Default -FilePath $Output;
  7.     foreach($server in $farm.Servers)
  8.     {
  9.         foreach($instance in $server.ServiceInstances)
  10.         {
  11.             # If the server has the timer service then stop the service
  12.             if($instance.TypeName -eq $timerServiceInstanceName)
  13.             {
  14.                [string]$serverName = $server.Name
  15.                 write-host “Generating CPU utilization report for server” $serverName -fore yellow
  16.                 $CPUDataCol = Get-WmiObject -Class Win32_Processor -ComputerName $ServerName
  17.                 foreach($Data in $CPUDataCol)
  18.                 {
  19.                     $serverName + “,” + $Data.DeviceID + “,” + $Data.loadpercentage + “,” + $Data.status | Out-File -Encoding Default  -Append -FilePath $Output;
  20.                 }
  21.                 write-host “CPU utilization report generated” -fore green
  22.             }
  23.         }
  24.     }
  25. }
Function 7

The following piece of code automates the determination of the memory utilization:

  1. Function MemoryUtilization([Microsoft.SharePoint.Administration.SPFarm]$farm)
  2. {
  3.     Write-Host “”
  4.     write-host “Memory utilization report” -fore Magenta
  5.     $output = $scriptbase + “\” + “MemoryUtilization.csv”
  6.     “ServerName” + “,” + “FreePhysicalMemory” + “,” + “TotalVisibleMemorySize” + “,” + “Status” | Out-File -Encoding Default -FilePath $Output;
  7.     foreach($server in $farm.Servers)
  8.         {
  9.         foreach($instance in $server.ServiceInstances)
  10.         {
  11.             # If the server has the timer service then stop the service
  12.             if($instance.TypeName -eq $timerServiceInstanceName)
  13.             {
  14.                 [string]$serverName = $server.Name
  15.                 write-host “Generating memory utilization report for server” $serverName -fore yellow
  16.                 $MemoryCol = Get-WmiObject -Class Win32_OperatingSystem -ComputerName $ServerName
  17.                 foreach($Data in $MemoryCol)
  18.                 {
  19.                     $serverName + “,” + $Data.FreePhysicalMemory + “,” + $Data.TotalVisibleMemorySize + “,” + $Data.status | Out-File -Encoding Default  -Append -FilePath $Output;
  20.                 }
  21.                 write-host “Memory utilization report generated” -fore green
  22.             }
  23.         }
  24.     }
  25. }

Function 8

The following piece of code automates the determination of the SharePoint server status:

  1. Function SPServerStatus([Microsoft.SharePoint.Administration.SPFarm]$farm)
  2. {
  3.     Write-Host “”
  4.     write-host “SharePoint server status report” -fore Magenta
  5.     $output = $scriptbase + “\” + “SPServerStatus.csv”
  6.     “ServerName” + “,” + “Role” + “,” + “Status” + “,” + “CanUpgrade” + “,” + “NeedsUpgrade” | Out-File -Encoding Default -FilePath $Output;
  7.     foreach($server in $farm.Servers)
  8.     {
  9.        foreach($instance in $server.ServiceInstances)
  10.        {
  11.           # If the server has the timer service then stop the service
  12.            if($instance.TypeName -eq $timerServiceInstanceName)
  13.             {
  14.                 $server.Name + “,” + $server.role + “,” + $server.status + “,” + $server.canupgrade + “,” + $server.NeedsUpgrade | Out-File -Encoding Default  -Append -FilePath $Output;
  15.                 write-host “SP server status report generated” -fore green
  16.             }
  17.         }
  18.     }
  19. }

Function 9

The output of each of the preceding functions is captured into a CSV file.

This function converts all the CSV files into a single Excel sheet separated with tabs for each CSV file.

  1. Function Release-Ref ($ref)
  2. {
  3.     ([System.Runtime.InteropServices.Marshal]::ReleaseComObject(
  4.     [System.__ComObject]$ref) -gt 0)
  5.     [System.GC]::Collect()
  6.     [System.GC]::WaitForPendingFinalizers()
  7. }
  8. Function ConvertCSV-ToExcel
  9. {
  10. [CmdletBinding(
  11.     SupportsShouldProcess = $True,
  12.     ConfirmImpact = ‘low’,
  13.     DefaultParameterSetName = ‘file’
  14.     )]
  15. Param (
  16.     [Parameter(
  17.      ValueFromPipeline=$True,
  18.      Position=0,
  19.      Mandatory=$True,
  20.      HelpMessage=“Name of CSV/s to import”)]
  21.      [ValidateNotNullOrEmpty()]
  22.     [array]$inputfile,
  23.     [Parameter(
  24.      ValueFromPipeline=$False,
  25.      Position=1,
  26.      Mandatory=$True,
  27.      HelpMessage=“Name of excel file output”)]
  28.      [ValidateNotNullOrEmpty()]
  29.     [string]$output
  30.     )
  31. Begin {
  32.     #Configure regular expression to match full path of each file
  33.     [regex]$regex = “^\w\:\\”
  34.     
  35.     #Find the number of CSVs being imported
  36.     $count = ($inputfile.count -1)
  37.    
  38.     #Create Excel Com Object
  39.     $excel = new-object -com excel.application
  40.     
  41.     #Disable alerts
  42.     $excel.DisplayAlerts = $False
  43.     #Show Excel application
  44.     $excel.Visible = $False
  45.     #Add workbook
  46.     $workbook = $excel.workbooks.Add()
  47.     #Remove other worksheets
  48.     $workbook.worksheets.Item(2).delete()
  49.     #After the first worksheet is removed,the next one takes its place
  50.     $workbook.worksheets.Item(2).delete()
  51.     #Define initial worksheet number
  52.     $i = 1
  53.     }
  54. Process {
  55.     ForEach ($input in $inputfile) {
  56.         #If more than one file, create another worksheet for each file
  57.         If ($i -gt 1) {
  58.             $workbook.worksheets.Add() | Out-Null
  59.             }
  60.         #Use the first worksheet in the workbook (also the newest created worksheet is always 1)
  61.         $worksheet = $workbook.worksheets.Item(1)
  62.         #Add name of CSV as worksheet name
  63.         $worksheet.name = “$((GCI $input).basename)”
  64.         #Open the CSV file in Excel, must be converted into complete path if no already done
  65.         If ($regex.ismatch($input)) {
  66.             $tempcsv = $excel.Workbooks.Open($input)
  67.             }
  68.         ElseIf ($regex.ismatch(“$($input.fullname)”)) {
  69.             $tempcsv = $excel.Workbooks.Open(“$($input.fullname)”)
  70.             }
  71.         Else {
  72.             $tempcsv = $excel.Workbooks.Open(“$($pwd)\$input”)
  73.             }
  74.         $tempsheet = $tempcsv.Worksheets.Item(1)
  75.         #Copy contents of the CSV file
  76.         $tempSheet.UsedRange.Copy() | Out-Null
  77.         #Paste contents of CSV into existing workbook
  78.         $worksheet.Paste()
  79.         #Close temp workbook
  80.         $tempcsv.close()
  81.         #Select all used cells
  82.         $range = $worksheet.UsedRange
  83.         #Autofit the columns
  84.         $range.EntireColumn.Autofit() | out-null
  85.         $i++
  86.         }
  87.     }
  88. End {
  89.     #Save spreadsheet
  90.     $workbook.saveas(“$pwd\$output”)
  91.     Write-Host -Fore Green “File saved to $pwd\$output”
  92.     #Close Excel
  93.     $excel.quit()
  94.     #Release processes for Excel
  95.     $a = Release-Ref($range)
  96.     }
  97. }
Complete Code
  1. $LogTime = Get-Date -Format yyyy-MM-dd_hh-mm
  2. $LogFile = “.\DailyMonitoringPatch-$LogTime.rtf”
  3. # Add SharePoint PowerShell Snapin
  4. if ( (Get-PSSnapin -Name Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue) -eq $null )
  5. {
  6.     Add-PSSnapin Microsoft.SharePoint.Powershell
  7. }
  8. import-module WebAdministration
  9. $scriptBase = split-path $SCRIPT:MyInvocation.MyCommand.Path -parent
  10. Set-Location $scriptBase
  11. write-host “TESTING FOR LOG FOLDER EXISTENCE” -fore yellow
  12. $TestLogFolder = test-path -path $scriptbase\Logs
  13. if($TestLogFolder)
  14. {
  15.     write-host “The log folder already exist in the script location” -fore yellow
  16.     $clearlogfolder = read-host “Do you want to clear the log folder (y/n)”
  17.     if($clearlogfolder -eq ‘y’)
  18.     {
  19.         write-host “The user choosen to clear the log folder” -fore yellow
  20.         write-host “Clearing the log folder” -fore yellow
  21.         remove-item $scriptbase\Logs\* -recurse -confirm:$false
  22.         write-host “Log folder cleared” -fore yellow
  23.     }
  24.     else
  25.     {
  26.         write-host “The user choosen not to clear the log files” -fore yellow
  27.     }
  28. }
  29. else
  30. {
  31.     write-host “Log folder does not exist” -fore yellow
  32.     write-host “Creating a log folder” -fore yellow
  33.     New-Item $Scriptbase\Logs -type directory
  34.     write-host “Log folder created” -fore yellow
  35. }
  36. #moving any .rtf files in the scriptbase location
  37. $FindRTFFile = Get-ChildItem $scriptBase\*.* -include *.rtf
  38. if($FindRTFFile)
  39. {
  40.     write-host “Some old log files are found in the script location” -fore yellow
  41.     write-host “Moving old log files into the Logs folder” -fore yellow
  42.     foreach($file in $FindRTFFile)
  43.         {
  44.             move-item -path $file -destination $scriptbase\logs
  45.         }
  46.     write-host “Old log files moved successfully” -fore yellow
  47. }
  48. start-transcript $logfile
  49. $global:timerServiceName = “SharePoint 2010 Timer”
  50. $global:timerServiceInstanceName = “Microsoft SharePoint Foundation Timer”
  51. # Get the local farm instance
  52. [Microsoft.SharePoint.Administration.SPFarm]$farm = [Microsoft.SharePoint.Administration.SPFarm]::get_Local()
  53. Function SharePointServices([Microsoft.SharePoint.Administration.SPFarm]$farm)
  54. {
  55.     Write-Host “”
  56.     write-host “Generating SharePoint services report” -fore Magenta
  57.     $output = $scriptbase + “\” + “SharePointServices.csv”
  58.     “ServiceName” + “,” + “ServiceStatus” + “,” + “MachineName” | Out-File -Encoding Default -FilePath $Output;
  59.     foreach($server in $farm.Servers)
  60.         {
  61.         foreach($instance in $server.ServiceInstances)
  62.                 {
  63.             # If the server has the timer service then stop the service
  64.                       if($instance.TypeName -eq $timerServiceInstanceName)
  65.             {
  66.                           [string]$serverName = $server.Name
  67.                 write-host “Generating SP services report for server” $serverName -fore yellow
  68.                 $Monitor = “SPAdminV4” , “SPTimerV4” , “SPTraceV4” , “SPUserCodeV4” , “SPWriterV4” , “OSearch14” , “W3SVC” , “IISADMIN” , “C2WTS” , “FIMService” , “FIMSynchronizationService”
  69.                 $services = Get-Service -ComputerName $serverName -Name $Monitor -ea silentlycontinue
  70.                 foreach($service in $services)
  71.                 {
  72.                     $service.displayname + “,” + $service.status + “,” + $service.MachineName | Out-File -Encoding Default  -Append -FilePath $Output;
  73.                 }
  74.                 write-host “SP services report generated” -fore green
  75.             }
  76.         }
  77.     }
  78. }
  79. Function IISWebsite([Microsoft.SharePoint.Administration.SPFarm]$farm)
  80. {
  81.     Write-Host “”
  82.     write-host “Generating IIS website report” -fore Magenta
  83.     $output = $scriptbase + “\” + “IISWebsite.csv”
  84.     “WebSiteName” + “,” + “WebsiteID” + “,” + “WebSiteState” + “,” + “Server” | Out-File -Encoding Default -FilePath $Output;
  85.     foreach($server in $farm.Servers)
  86.         {
  87.         foreach($instance in $server.ServiceInstances)
  88.                 {
  89.             # If the server has the timer service then stop the service
  90.                       if($instance.TypeName -eq $timerServiceInstanceName)
  91.             {
  92.                           [string]$serverName = $server.Name
  93.                 write-host “Generating IIS website report for server” $serverName -fore yellow
  94.                 $status = “”
  95.                 $Sites = gwmi -namespace “root\webadministration” -Class site -ComputerName $serverName -Authentication PacketPrivacy -Impersonation Impersonate
  96.                 foreach($site in $sites)
  97.                 {
  98.                     if($site.getstate().returnvalue -eq 1)
  99.                     {
  100.                         $status = “Started”
  101.                     }
  102.                     else
  103.                     {
  104.                         $status = “Not Started”
  105.                     }
  106.                     $site.name + “,” + $site.ID + “,” + $Status + “,” + $serverName | Out-File -Encoding Default  -Append -FilePath $Output;
  107.                 }
  108.                 write-host “IIS website report generated” -fore green
  109.             }
  110.         }
  111.     }
  112. }
  113. Function AppPoolStatus([Microsoft.SharePoint.Administration.SPFarm]$farm)
  114. {
  115.     Write-Host “”
  116.     write-host “Generating AppPool status report” -fore Magenta
  117.     $output = $scriptbase + “\” + “AppPoolStatus.csv”
  118.     “AppPoolName” + “,” + “Status” + “,” + “Server” | Out-File -Encoding Default -FilePath $Output;
  119.     foreach($server in $farm.Servers)
  120.         {
  121.         foreach($instance in $server.ServiceInstances)
  122.                 {
  123.             # If the server has the timer service then stop the service
  124.                       if($instance.TypeName -eq $timerServiceInstanceName)
  125.             {
  126.                           [string]$serverName = $server.Name
  127.                 write-host “Generating AppPool status report for server” $serverName -fore yellow
  128.                 $status = “”
  129.                 $AppPools = gwmi -namespace “root\webadministration” -Class applicationpool -ComputerName $serverName -Authentication PacketPrivacy -Impersonation Impersonate
  130.                 foreach($AppPool in $AppPools )
  131.                 {
  132.                     if($AppPool.getstate().returnvalue -eq 1)
  133.                     {
  134.                         $status = “Started”
  135.                     }
  136.                     else
  137.                     {
  138.                         $status = “Stopped”
  139.                     }
  140.                     $AppPool.name + “,” + $Status + “,” + $serverName| Out-File -Encoding Default  -Append -FilePath $Output;
  141.                 }
  142.                 write-host “AppPool status report generated” -fore green
  143.             }
  144.         }
  145.     }
  146. }
  147. Function DiskSpace([Microsoft.SharePoint.Administration.SPFarm]$farm)
  148. {
  149.     Write-Host “”
  150.     write-host “Generating Disk space report” -fore Magenta
  151.     $output = $scriptbase + “\” + “DiskSpace.csv”
  152.     “Computer Name” + “,” + “Drive” + “,” + “Size in (GB)” + “,” + “Free Space in (GB)” + “,” + “Critical (*)”  | Out-File -Encoding Default -FilePath $Output;
  153.     foreach($server in $farm.Servers)
  154.         {
  155.         foreach($instance in $server.ServiceInstances)
  156.                 {
  157.             # If the server has the timer service then stop the service
  158.                       if($instance.TypeName -eq $timerServiceInstanceName)
  159.             {
  160.                           [string]$serverName = $server.Name
  161.                 write-host “Generating disk space report for server” $serverName -fore yellow
  162.                 $drives = Get-WmiObject -ComputerName $serverName Win32_LogicalDisk | Where-Object {$_.DriveType -eq 3}
  163.                 foreach($drive in $drives)
  164.                 {
  165.                     $id = $drive.DeviceID
  166.                     $size = [math]::round($drive.Size / 1073741824, 2)
  167.                     $free = [math]::round($drive.FreeSpace  / 1073741824, 2)
  168.                     $pct = [math]::round($free / $size, 2) * 100
  169.                     if ($pct -lt 30)
  170.                     {
  171.                         $pct = $pct.ToString() + “% *** “
  172.                     }
  173.                     else
  174.                     {
  175.                         $pct = $pct.ToString() + ” %”
  176.                     }
  177.                     $serverName + “,” + $id + “,” + $size + “,” + $free + “,” + $pct  | Out-File -Encoding Default  -Append -FilePath $Output;
  178.                     $pct = 0
  179.                 }
  180.                 write-host “Disk space report generated” -fore green
  181.             }
  182.         }
  183.     }
  184. }
  185. Function HealthAnalyserReports()
  186. {
  187.     write-host “”
  188.     write-host “Generating health analyser report” -fore magenta
  189.     $output = $scriptbase + “\” + “HealthAnalyser.csv”
  190.     “Severity” + “,” + “Category” + “,” + “Modified” + “,” + “Failing servers” + “,” + “Failing services”  | Out-File -Encoding Default -FilePath $Output;
  191.     $ReportsList = [Microsoft.SharePoint.Administration.Health.SPHealthReportsList]::Local
  192.     $Items = $ReportsList.items | where {
  193.         if($_[‘Severity’] -eq ‘1 – Error’)
  194.         {
  195.             #write-host $_[‘Name’]
  196.             #write-host $_[‘Severity’]
  197.             $_[‘Severity’] + “,” + $_[‘Category’] + “,” + $_[‘Modified’] + “,” + $_[‘Failing Servers’] + “,” + $_[‘Failing Services’]  | Out-File -Encoding Default  -Append -FilePath $Output;
  198.         }
  199.     }
  200.     write-host “Health analyser report generated” -fore green
  201. }
  202. Function CPUUtilization([Microsoft.SharePoint.Administration.SPFarm]$farm)
  203. {
  204.     Write-Host “”
  205.     write-host “Generating CPU utilization report” -fore Magenta
  206.     $output = $scriptbase + “\” + “CPUUtilization.csv”
  207.     “ServerName” + “,” + “DeviceID” + “,” + “LoadPercentage” + “,” + “Status” | Out-File -Encoding Default -FilePath $Output;
  208.     foreach($server in $farm.Servers)
  209.         {
  210.         foreach($instance in $server.ServiceInstances)
  211.                 {
  212.             # If the server has the timer service then stop the service
  213.                       if($instance.TypeName -eq $timerServiceInstanceName)
  214.             {
  215.                           [string]$serverName = $server.Name
  216.                 write-host “Generating CPU utilization report for server” $serverName -fore yellow
  217.                 $CPUDataCol = Get-WmiObject -Class Win32_Processor -ComputerName $ServerName
  218.                 foreach($Data in $CPUDataCol)
  219.                 {
  220.                     $serverName + “,” + $Data.DeviceID + “,” + $Data.loadpercentage + “,” + $Data.status | Out-File -Encoding Default  -Append -FilePath $Output;
  221.                 }
  222.                 write-host “CPU utilization report generated” -fore green
  223.             }
  224.         }
  225.     }
  226. }
  227. Function MemoryUtilization([Microsoft.SharePoint.Administration.SPFarm]$farm)
  228. {
  229.     Write-Host “”
  230.     write-host “Memory utilization report” -fore Magenta
  231.     $output = $scriptbase + “\” + “MemoryUtilization.csv”
  232.     “ServerName” + “,” + “FreePhysicalMemory” + “,” + “TotalVisibleMemorySize” + “,” + “Status” | Out-File -Encoding Default -FilePath $Output;
  233.     foreach($server in $farm.Servers)
  234.         {
  235.         foreach($instance in $server.ServiceInstances)
  236.                 {
  237.             # If the server has the timer service then stop the service
  238.                       if($instance.TypeName -eq $timerServiceInstanceName)
  239.             {
  240.                           [string]$serverName = $server.Name
  241.                 write-host “Generating memory utilization report for server” $serverName -fore yellow
  242.                 $MemoryCol = Get-WmiObject -Class Win32_OperatingSystem -ComputerName $ServerName
  243.                 foreach($Data in $MemoryCol)
  244.                 {
  245.                     $serverName + “,” + $Data.FreePhysicalMemory + “,” + $Data.TotalVisibleMemorySize + “,” + $Data.status | Out-File -Encoding Default  -Append -FilePath $Output;
  246.                 }
  247.                 write-host “Memory utilization report generated” -fore green
  248.             }
  249.         }
  250.     }
  251. }
  252. Function SPServerStatus([Microsoft.SharePoint.Administration.SPFarm]$farm)
  253. {
  254.     Write-Host “”
  255.     write-host “SharePoint server status report” -fore Magenta
  256.     $output = $scriptbase + “\” + “SPServerStatus.csv”
  257.     “ServerName” + “,” + “Role” + “,” + “Status” + “,” + “CanUpgrade” + “,” + “NeedsUpgrade” | Out-File -Encoding Default -FilePath $Output;
  258.     foreach($server in $farm.Servers)
  259.         {
  260.         foreach($instance in $server.ServiceInstances)
  261.                 {
  262.             # If the server has the timer service then stop the service
  263.                       if($instance.TypeName -eq $timerServiceInstanceName)
  264.             {
  265.                 $server.Name + “,” + $server.role + “,” + $server.status + “,” + $server.canupgrade + “,” + $server.NeedsUpgrade | Out-File -Encoding Default  -Append -FilePath $Output;
  266.                 write-host “SP server status report generated” -fore green
  267.             }
  268.         }
  269.     }
  270. }
  271. #######################Function to combine multiple CSV files into single excel sheet with seperated tabs for each CSV#########################
  272. Function Release-Ref ($ref)
  273. {
  274.     ([System.Runtime.InteropServices.Marshal]::ReleaseComObject(
  275.     [System.__ComObject]$ref) -gt 0)
  276.     [System.GC]::Collect()
  277.     [System.GC]::WaitForPendingFinalizers()
  278. }
  279. Function ConvertCSV-ToExcel
  280. {
  281. [CmdletBinding(
  282.     SupportsShouldProcess = $True,
  283.     ConfirmImpact = ‘low’,
  284.     DefaultParameterSetName = ‘file’
  285.     )]
  286. Param (
  287.     [Parameter(
  288.      ValueFromPipeline=$True,
  289.      Position=0,
  290.      Mandatory=$True,
  291.      HelpMessage=“Name of CSV/s to import”)]
  292.      [ValidateNotNullOrEmpty()]
  293.     [array]$inputfile,
  294.     [Parameter(
  295.      ValueFromPipeline=$False,
  296.      Position=1,
  297.      Mandatory=$True,
  298.      HelpMessage=“Name of excel file output”)]
  299.      [ValidateNotNullOrEmpty()]
  300.     [string]$output
  301.     )
  302. Begin {
  303.     #Configure regular expression to match full path of each file
  304.     [regex]$regex = “^\w\:\\”
  305.     
  306.     #Find the number of CSVs being imported
  307.     $count = ($inputfile.count -1)
  308.    
  309.     #Create Excel Com Object
  310.     $excel = new-object -com excel.application
  311.     
  312.     #Disable alerts
  313.     $excel.DisplayAlerts = $False
  314.     #Show Excel application
  315.     $excel.Visible = $False
  316.     #Add workbook
  317.     $workbook = $excel.workbooks.Add()
  318.     #Remove other worksheets
  319.     $workbook.worksheets.Item(2).delete()
  320.     #After the first worksheet is removed,the next one takes its place
  321.     $workbook.worksheets.Item(2).delete()
  322.     #Define initial worksheet number
  323.     $i = 1
  324.     }
  325. Process {
  326.     ForEach ($input in $inputfile) {
  327.         #If more than one file, create another worksheet for each file
  328.         If ($i -gt 1) {
  329.             $workbook.worksheets.Add() | Out-Null
  330.             }
  331.         #Use the first worksheet in the workbook (also the newest created worksheet is always 1)
  332.         $worksheet = $workbook.worksheets.Item(1)
  333.         #Add name of CSV as worksheet name
  334.         $worksheet.name = “$((GCI $input).basename)”
  335.         #Open the CSV file in Excel, must be converted into complete path if no already done
  336.         If ($regex.ismatch($input)) {
  337.             $tempcsv = $excel.Workbooks.Open($input)
  338.             }
  339.         ElseIf ($regex.ismatch(“$($input.fullname)”)) {
  340.             $tempcsv = $excel.Workbooks.Open(“$($input.fullname)”)
  341.             }
  342.         Else {
  343.             $tempcsv = $excel.Workbooks.Open(“$($pwd)\$input”)
  344.             }
  345.         $tempsheet = $tempcsv.Worksheets.Item(1)
  346.         #Copy contents of the CSV file
  347.         $tempSheet.UsedRange.Copy() | Out-Null
  348.         #Paste contents of CSV into existing workbook
  349.         $worksheet.Paste()
  350.         #Close temp workbook
  351.         $tempcsv.close()
  352.         #Select all used cells
  353.         $range = $worksheet.UsedRange
  354.         #Autofit the columns
  355.         $range.EntireColumn.Autofit() | out-null
  356.         $i++
  357.         }
  358.     }
  359. End {
  360.     #Save spreadsheet
  361.     $workbook.saveas(“$pwd\$output”)
  362.     Write-Host -Fore Green “File saved to $pwd\$output”
  363.     #Close Excel
  364.     $excel.quit()
  365.     #Release processes for Excel
  366.     $a = Release-Ref($range)
  367.     }
  368. }
  369. #################################################################################################################################################
  370. ##########Calling Functions#################
  371. SharePointServices $farm
  372. IISWebsite $farm
  373. AppPoolStatus $farm
  374. DiskSpace $farm
  375. HealthAnalyserReports
  376. CPUUtilization $farm
  377. MemoryUtilization $farm
  378. SPServerStatus $farm
  379. write-host “”
  380. write-host “Combining all CSV files into single file” -fore yellow
  381. Get-Item $scriptbase\*.csv | ConvertCSV-ToExcel -output “DailyMonitoringReports.xlsx”
  382. write-host “”
  383. write-host “SCRIPT COMPLETED” -fore green
  384. stop-transcript

Execution Procedure

  • Step 1: Download and copy the script to the SharePoint server.
  • Step 2: Navigate to the script path.
  • Step 3: Execute the script as in the following:

Conclusion

Thus this article outlines how to automate daily monitoring tasks for SharePoint 2010 using a PowerShell script. dailymonitoring – download the full script from here.

Conditional formatting in SharePoint 2013 and Office 365

Sometimes the requirement comes up to make conditional formatting in a SharePoint list or document library. There are several ways of achieving this, you can buy a solution, the are several solutions out there providing conditional formatting. Or you can use SharePoint designer etc.

I will describe a simple method using jQuery that will work both on SharePoint 2013 on-premises and on SharePoint online in Office 365.

In this example I created a custom list with a set of columns. One of the columns, “Approve” is a Choice column with a couple of choices , “None”, “Approved” and “Approved under revision”.

2014-09-18_09h49_33

These are the choices we will use for our conditional formatting, We will make items tagged with “Approved under revision” a yellow background and items tagged with “Approved” a green background.

Navigate to your list or document library and select Edit Page from the settings menu.

2014-09-18_12h07_34Then click Add a Web Part and add a Content editor webpart to this page. We will use this Content editor webpart to add our jQueryscript.

Click anywhere in the Content editor webpart and then click Edit Source on the ribbon.

2014-09-18_12h09_58

Add the following JavaScript to the HTML Source dialog.

2014-09-18_12h12_09

01 /code
02
03  
04
05     $(document).ready(function(){
06
07         $Text = $("td .ms-vb2:contains('Approved')").filter(function() {
08   return $(this).text() == "Approved";})
09         $Text.parent().css("background-color", "#00FF66");
10         
11         $Text = $("td .ms-vb2:contains('Approved under revision')");
12         $Text.parent().css("background-color", "#FFFF66");
13
14         
15     });

Line 1 of the script points in this example to the jQuery library online. In your environment you could just as easily download the jQuery  library form here and put in in a library on your SharePoint or Office 365 site. I usually put it in the Site Assets library and update the URL in line 1 accordingly.

Line 7 searches for a table cell with the word “Approved” in it and uses the filter function to make sure it matches the word exactly since the next item we are looking for “Approved under revision” alos includes the word approved. Once it find a table cell with the word “Approved” it sets the background color for the parent to green. The parent in this case will be the table row so the hole row gets a nice green background color.

Line 11 and 12 does the same thing, without the filter function since we are searching for a match to “Approved under revision”, and sets the background color of the parent to yellow.

Click Ok and the Stop Editing in the ribbon when you have pasted in the script.

The result will look like the screenshot below.

2014-09-16_10h10_17

So that was my description of an easy way to get conditional formatting for a list or document library. Works great both in SharePoint 2013 on-premises and in Office 365.