18 June 2016

Powershell Tip - Padding Text

Since we use Powershell for just about everything these days, you're sure to end up generating some kind of quick report of something in SharePoint through a simple Powershell script.  Inevitably, the need to pad text comes up.  Powershell makes it easy to left and right pad text variables through the use of the .PadLeft() and .PadRight() .NET methods used as follows:

$str = "My Report Title";
$strPad = $str.PadLeft(20) + ".";
Write-Host $strPad;

The output looks like this:

     My Report Title.

In this case, we were left padding the string to 20 characters.  By default, padding is done with spaces so given the string was already 15 characters long, 5 spaces were added to the front of the string resulting in our output.  The use of the additional period will become apparent as we look at right padding the text as follows:

$strPad = $str.PadRight(20) + ".";
Write-Host $strPad;

The output looks like this:

My Report Title     .

Here you can clearly see the 5 spaces before the period. ;-)
If you want to pad with something other than spaces, you can simply supply the second, optional parameter to PadLeft() or PadRight() specifying the character to be used for padding e.g.

$strPad = $str.PadRight(20, "$") + ".";
Write-Host $strPad;

The output looks like this:

My Report Title$$$$$.

Enjoy!
C


22 May 2016

How do I - Round numbers to a given decimal space in Powershell?

One of my subscribers asked how to round in Powershell.  It's actually quite easy.  All you have to do is use the [math] preprocessor's Round() method e.g. if I wanted to round to two decimal places I'll use the following:

[math]::Round($value, 2);

If I issue the Round() call without a second parameter thus:

[math]::Round($value);

I would get an integer rounded value, i.e. I'd get the whole, ignoring any decimals.

Hope that helps!

Later
C

16 April 2016

How do I use Powershell with IIS 7 or later?

If you're into any form of automation, you know that Powershell rocks!
That said, the real strength of Powershell comes from it's snap-ins.  These extensions allow you to do all manner of things.  One of those is the ability to script actions in IIS.  To get started you'll need to ensure you have the Feature installed on your flavor of Windows.  If you don't have it installed, you can either download it here:

https://www.microsoft.com/en-us/download/details.aspx?id=7436

or add it via Windows' Programs and Features.  Generally on anything late Windows 7 or Server 2008, it's already loaded.

Once installed, you'll need to load the snap-in thus:

Import-Module WebAdministration
From here, you're only limited by the available calls and your imaginations.

Happy scripting.
C

10 March 2016

What is the SharePoint hive?

Almost a decade ago I posted an article explaining what the 60/12 hive is.  Since then, SharePoint has undergone many new versions so I thought it was time I updated my hive article just a bit to include the three additional major releases since then.  For more information on the origination of the "hive", refer to the original article linked above.

SharePoint Version
Hive Server Disk Location
2007C:\Program Files\Common Files\Microsoft Shared\web server extensions\12
2010C:\Program Files\Common Files\Microsoft Shared\web server extensions\14
2013C:\Program Files\Common Files\Microsoft Shared\web server extensions\15
2016C:\Program Files\Common Files\Microsoft Shared\web server extensions\16

Enjoy
C

22 February 2016

How do I - Get a list of email addresses of all users who belong to a given SharePoint security group?

One of mentees asked me how to extract the emails of all the owners of a given site.  With some Powershell magic and the use of the -ExpandProperty switch, this is actually quite easy.

$url = "http://{webapp}/sites/{site}";
$owners = Get-SPWeb $url | Select -ExpandProperty SiteGroups | Where {$_.Name.ToLower() -match ((Get-SPWeb $url).Title.ToLower() + " owners")} | Select -ExpandProperty Users | Select Email;
foreach ($owner in $owners)
{
   {do something with $owner.Email}
}

In the above script, we start off by setting the $url variable.  Then we make the big call to get the owners list of emails into $owners.
The command calls Get-SPWeb for the given URL.  The return of that is then piped to a Select command using the -ExpandProperty switch to expand the SiteGroups.  This would get you all site groups, but piping it to the Where command allows us to filter the returned groups down to the one we want.
The Where command is fed the name of the site group (in lower case) and is asked to match it against the spweb object's title (in lower case) with " owners" concatenated.  A couple of things to note:

  1. It is good practice to ALWAYS make the case of text the same on both sides of a text based comparison.  I prefer lower case, hence the dispersion of .ToLower() in my code.
  2. This part of the code does depend on default security groups being used in SharePoint.  This is 95% of the time the case, but you should be aware that different naming conventions may have been used for the name of the site owners group.
Now that we have the owners security group, the result is piped to another Select command expanding to return the users of that group.  Finally, we pipe to a Select statement that selects a single field i.e. the email address.

You now have an array of email addresses that can be iterated and used via the .Email field reference.

Enjoy
C

18 January 2016

How to load a dot net class in Powershell when you don't know the DLL name.

Most of us know that the main SharePoint classes live in Microsoft.SharePoint.dll.  The easiest way to load this assembly is by using the ::LoadWithPartialName method thus releasing you from having to use the exact DLL name.  In this case its obvious, but there are a couple of classes that are in DLLs that are not so obvious.  Simply call the following:

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint");

You can replace the "Microsoft.SharePoint" with any class name, provided its accessible on the system.

Enjoy
C

10 December 2015

How do I - Allow someone the ability to TRUNCATE TABLE in SQL Server without granting them DBO rights?

There comes a time where a developer produces a stored procedure (sproc) that tries to clear a table quickly. In the case of permanent tables, this is usually done using the TRUNCATE TABLE statement in TSQL. Of course in a production setting, you will have limited rights on your SQL Server and databases. As such, we generally grant users DB_DATAREADER and DB_DATAWRITER rights. Additionally, we'll grant users EXECUTE rights on sprocs. The problem is that in order to do a TRUNCATE TABLE, SQL actually drops that table in order to clear it so quickly. That won't be possible when a users is trying to execute the sproc in production. Since they have EXECUTE rights, they'd be able to execute it, but it will fail when the system tries to drop the table in question. For that, the executor needs to have DBO rights. Of course we're not going to grant people DBO rights in production! So the question becomes, how do you solve the problem?

I'm assuming that well defined change management and process control is in place already. If it is not, you need to start there first. Once you have a proper method for reviewing changes to sprocs going into production, you can apply this quick fix:

TSQL BEFORE

ALTER PROCEDURE [dbo].[BlaBlaBla]
@TableName varchar(60)
AS    
DECLARE @QUERY NVARCHAR(200);
...

TSQL AFTER

ALTER PROCEDURE [dbo].[BlaBlaBla]
@TableName varchar(60)
WITH EXECUTE AS OWNER
AS
DECLARE @QUERY NVARCHAR(200);
...

By simply adding the WITH EXECUTE AS OWNER line to the sproc, it will execute as owner level permissions, thus allowing the TRUNCATE statement to succeed.  Now it's important to understand that this creates a security risk that YOU must manage.  That's why it's important to review any and all changes that are made to the sprocs, especially if they're going to have this statement in them.  It may be better to just create a generic sproc that you can feed a table name to, which elevates like this and truncates the table passed to it.

Enjoy
C

SharePoint Remote Event Receivers are DEAD!!!

 Well, the time has finally come.  It was evident when Microsoft started pushing everyone to WebHooks, but this FAQ and related announcement...