6/11/2016

SharePoint Column Validation Examples

Now available on Amazon!

image

Over 100 Examples!

A how-to book of formulas would not be too useful without a few examples. I've been collecting these for years. They've come from classroom questions, forum questions, and my own SharePoint sites. Now they are all in one place…

  • Over 60 Calculated Columns examples
  • Over 30 Column Validation examples
  • 11 List/Library Item Validation examples
    (and every one of the Column Validation examples can be used here.)
  • 7 Calculated Column Default Values examples
  • 15 Workflow “workarounds” for things SharePoint formulas can’t do

Update 11/18/2017… added test for nearest 1/4th, 1/10th, etc.
Update 11/2/2015… added "Date must be the first day of the month" and "Date must be the last day of the month".

The following applies to SharePoint 2007, 2010, 2013, 2016 and SharePoint Online/Office 365.

 

Column Validation

SharePoint does not include column types for phone numbers or part numbers, nor does it include support for Regular Expressions to test for character patterns. It does support Excel style functions that we can use to create useful column validation formulas.

Below you will find column validation examples for:

  • OR
  • AND
  • Length (LEN)
  • Pattern matching using SEARCH and FIND
  • Date testing


General Validation Formula Rules:

  • Formula must return True or False.
  • Column validations can only be added to Single Line of Text, Number, Choice (Drop-Down menu or Radio buttons, but not Checkboxes), Currency and Date and Time columns.
  • Expressions are generally Excel compatible, but not all Excel functions can be used.
  • Field names without special symbols can be entered as is or in square brackets
          = Price * [Qty]  > 100
  • Field names with spaces or symbols must be enclosed in square brackets
          =OR( [Sales Region] = 1, [Sales Region] = 1)
  • The text comparisons are not case sensitive.
          =OR( status = "a", status="c")     is true for either "A" or "a" or "C" or "c".
  • In a column validation the formula cannot refer to another column.
  • In a list / library validation the formula can refer to other columns in the same item.


Examples using "OR":

The OR function accepts two or more Boolean tests that each return True or False. OR returns True if any one of the tests is True.

=OR(YourFieldName="A",YourFieldName="C",YourFieldName="E")

=OR(State="OH", State="IN", State="KY", State="MI")

=OR(Qty=5, Qty=10, Qty=20)


Examples using "AND":

The AND function accepts two or more Boolean tests that each return True or False. AND returns True if all of the tests are True.

=AND(YourFieldName>"A", YourFieldName<"M")     YourFieldName value must be between A and M.

=AND(Qty>5, Qty<100, Qty<>47)      Qty must be between 5 and 100, but not 47.


Examples using "LEN":

As an example, if your part numbers are always 9 characters long:
    =LEN(YourFieldName) = 9

If the part numbers can be 9 or 12 characters long:
    =OR( LEN(YourFieldName) = 9, LEN(YourFieldName) = 12 )


Examples for Pattern Matching

The SEARCH function:  (online help)

  • Matches a pattern using "*" and "?". "*" equals zero more characters and "?" equals exactly one character.
  • To match an asterisks or question mark character prefix the symbols with "~". 
    Example: "a~?b?c" matches "a?bxc" but not "axbxc". 
  • An "*" is assumed to be appended to the end of the match pattern. To limit the length use the AND and LEN functions.
  • The comparison is not case sensitive.
  • If there is a match, the function returns the position of the match. If the every character is to be matched you would typically test for "=1" or maybe ">0". 
  • If there is no match, the function returns ERROR, therefore it must be wrapped inside of an ISERROR function. As we will have a match if there is no error, the ISERROR must be wrapped inside of a NOT function. (online help for ISERROR)

Examples:

Must start with an "a" or "A" and the third character must be a "c" or "C":
   =NOT(ISERROR( SEARCH("A?C",YourFieldName)=1 ))

   Matches: abc   AbC  aXc  a6c aBcDEF
   Does not match:   bbb   abb  ac  a

Match a phone number pattern of xxx-xxx-xxxx: (note: user could type letters or digits or type extra characters.)
   =NOT(ISERROR( SEARCH("???-???-????",YourFieldName)=1 ))

   Matches: 123-123-1234    aaa-aaa-aaaa   123-123-12344444

Match a phone number pattern of xxx-xxx-xxxx and limit the length:
   =AND( NOT(ISERROR(SEARCH("???-???-????",YourFieldName,1))), LEN(YourFieldName)=12 )

   Matches: 123-123-1234
   Does not match: 123-123-12345


Match a phone number and make sure only digits have been used:

The first example here is not a true pattern match. It just extracts the characters we think should be digits and tries to multiply them by any number. If that fails, then one or more of the characters is not a number. (online help for CONCATENATE and MID)

=NOT(ISERROR(1*CONCATENATE(MID(YourFieldName,1,3),MID(YourFieldName,5,3),MID(YourFieldName,9,4))))

   Matches: 123-123-1234    123x123x1234   123-123-1234xxxxx
   Does not match: abc-123-1234

The second example combines the earlier pattern match with a numeric test:

   =AND(NOT(ISERROR(SEARCH("???-???-????",YourFieldName,1))),LEN(YourFieldName)=12, NOT(ISERROR(1*CONCATENATE(MID(YourFieldName,1,3),MID(YourFieldName,5,3),MID(YourFieldName,9,4)))))


The FIND Function:  (online help)

The FIND function is similar to the SEARCH function with two differences;

  • FIND is case sensitive.
  • FIND does not support wild cards.


Examples for Numbers

Validate if a number ends in either .25, .50 or .5 or .75 or is a whole number.

     =ROUND([Activity Effort]*4,0)/4 = [Activity Effort]

If you wanted the number to the nearest 10th then divide by 10, round then multiple by 10.
     =ROUND([Activity Effort]*10,0)/10 = [Activity Effort]
This works all of the time for numbers with non-repeating digits. I.e. It will not work for 1/3 as 0.333333333333... can't be truly represented as a fixed set of digits. (It will actually work for 1/3 if you know the right number of digits to type! it looks like it's 15 significant digits: 0.333333333333333, 0.666666666666667 and 1.33333333333333 will work for 1/3, 2/3 and 1 1/3 with =ROUND([Activity Effort]*3,0)/3 = [Activity Effort] as the validation.)


Examples Using Dates

You can create rules to limit date ranges by using the TODAY() function or the DATEVALUE() function.

Date must be in the future:
    =YourFieldName>TODAY()

Date must be in the future by "x" days:
    =YourFieldName>TODAY() + 3
I.e. If today is the 7th, then valid dates start on the 11th.

Test against a particular date:  (online help for DATEVALUE)
    =YourFieldName>datevalue("1/1/2015")

Date must be between now and the end of the current year:  (online help for YEAR)
    =YourFieldName < DATEVALUE( "12/31/" & YEAR(TODAY()) )
This example calculates a DATEVALUE by building a string to represent a future date.

Date must be within the next 30 days:
    =AND(YourFieldName >= TODAY(),YourFieldName <= TODAY()+30)

Date must be a Monday:   (1 = Sunday, 2 = Monday, 3 = Tuesday, …)   (online help for WEEKDAY)
    =WEEKDAY(YourFieldName)=2

Date must be the last day of the month:
=DATE(YEAR(yourDateColumn),MONTH(yourDateColumn),DAY(yourDateColumn))=DATE(YEAR(yourDateColumn),MONTH(yourDateColumn)+1,0)

Date must be the first day of the month:
=DATE(YEAR(yourDateColumn),MONTH(yourDateColumn),DAY(yourDateColumn))=DATE(YEAR(yourDateColumn),MONTH(yourDateColumn),1)

Note: Some of the more "fun" Excel date functions like WEEKNUM, NETWORKDAYS and EOMONTH are not supported in SharePoint.


Not so useful tests!   Smile

Value must be greater than PI.  (3.14159265358979 more or less…)
    =YourFieldName > PI()

And some square roots:
    =YourFieldName > SQRT(2)

And of course you need a little trig:
    =TAN(RADIANS(YourFieldName)) > 1


.

5/31/2016

SharePoint: Search for People using Properties (LastName:smith)

 

The typical SharePoint end user knows less than 1% of the SharePoint Search feature set. In other words, they know how to do a Google search. They just type a word or two, press Enter and hope for the best. Just think what they could do if they just knew a few search properties!

You can really improve search results by adding a Search Administrator to your team and letting them invest an hour or three a week in improving the end-user search experience. Train your end users, add tips to your search pages, create cheat sheets - there’s lots of options.

Just adding a few search tips to the search pages will do wonders:

   image

So, let’s take a look at how users can do a better People search…

 

Searching for People

Let’s say I do a search for people using the keyword “training”. I could find people in the Training department, people with the word “training” in About Me, and even people with the last name of “Training”. If you would like to focus your search then you can use the predefined search Managed Properties. For example:

   image

While a few of the properties can be used with the equals operator (“=”), most will only return results with the contains operator (“:”). For example, searching for a work phone number using “=” returns nothing. Using “:” will return the person with that number.

   image

 

Managed Properties for People Searches

Most of the User Profile properties are searchable in a People search by just typing a keyword in the search box. You can also perform a People search using some of the out of the box Managed Metadata properties that are linked to the User Profile Services properties. In the table below you will find most of the User Profile properties along with the matching search Managed Property names. A few of the properties below have obvious names. A few are wrapped up in a single search property named “ContentHidded”. Some are “indexed” (crawled), but do not have the needed matching Managed Property. For most of those that do not have a matching Managed Property you can manually add a property to the search schema.

I will follow up with an article on adding the missing search Managed Properties.

Property for search

Property name found in the user’s profile

Notes

AccountName Account Name Example: accountname:contoso\samc
AboutMe About Me  
Interests Interests  
Responsibility Ask Me About  
FirstName First name  
LastName Last name  
PreferredName Name This is the full name. “Mike Smith”
WorkPhone Work Phone To find all users in the same area code or a partial number, use wild cards: workphone=513*
JobTitle Title  
WorkEmail Work Email  
MobilePhone Mobile phone  
  Home phone Mapped to ContentsHidden. Can be mapped to a new Managed Property.
  Fax Not mapped, but can be. (People:Fax)
     
Department Department This is a String property. This department maps to “Department”
  Department This is a Managed Metadata property. Not mapped, but can be. (People:SPS-Department)
Schools Schools  
If you see this list anywhere but on TechTrainingNotes.blogspot.com, then it was “stolen” and used without permission.
OfficeNumber Office Generally used for “room number”.
BaseOfficeLocation Office Location  
PastProjects Past Project  
Skills Skills  
  Manager Not mapped, but can be. (People:Manager) (returned as domain\username)
  Assistant Not mapped, but can be. (People:Manager) (returned as domain\username)
  Birthday Not mapped, but can be. (People:Birthday) (returned as “2000-03-01T00:00:00.0000000Z”)  All birthdays are set for year 2000.
  Hire Date Not mapped, but can be. (People:SPS-HireDate)
ContentsHidden (many)

This maps to several crawled properties as a single merged property:
People:Office
People:SPS-PastProjects
People:SPS-School
People:WorkPhone
People:CellPhone
People:Fax
People:HomePhone
People:SPS-MemberOf
People:AboutMe
People:OrganizationParentNames

 

.

5/17/2016

SharePoint Folders Are Not EVIL!

 

image
It seems that everyday I run across another blog article, forum post or social media that says “Never Use Folders!” While one of the common analogies for SharePoint is the Swiss Army Knife, a better one might be a tool box, and one with a lot of tools. Saying “Never Use Folders” is kind of like saying never use an adjustable wrench because we have box wrenches. Tools are tools and you need to select the correct tool for the job.

The following is not an excuse to not create a formal taxonomy and use a pure metadata approach to content management. It is a description of one of your many SharePoint tools in your toolbox. Remember everything is not a nail, and your only tool is not just a hammer.

 

Sometimes You Just Can’t Afford Metadata

Not an excuse so much as a reality.

You just built your new SharePoint farm. You have hundreds of thousands of documents to migrate to SharePoint. Who’s going to add all of the metadata? You employees (in their free time?), summer interns, contractors?

If you maintain the folder structure during your migration from network shares then your users can still find content as they always have. And, when you have added all of your metadata you can then either hide the old folders in your views, or move the content into one giant folderless library.

 

Folders are metadata!

In fact, Folders are “instant metadata”. Just upload or drag the document to the right folder and everyone will know something about it. If it’s in the folder named “Chlamydoselachidae” then it must be something about “Frill Sharks”!

(I’ll give anybody at Microsoft a couple of dollars if they will add the folder name property to the available columns in a view. It would then be true metadata!)

Folders can have custom metadata

A folder is a Content Type. You can create new Content Types that inherit from Folder and then add metadata columns. While a search on the metadata does not return the files in the folder, it will return the folders.

Here’s an article I wrote back in 2007 that still applies to SharePoint 2010, 2013 and 2016: http://techtrainingnotes.blogspot.com/2007/08/sharepoint-how-to-create-links-from.html

   image

   image

   image

 

Want really smart folders with metadata that shares their metadata with their contents?

Take a look at Document Sets. Not the out of the box example, but rather a custom one that you create by inheriting from the Document Set Content Type. If you add a Site Column named “Product Category” then every document in that Folder / Document Set will be findable from search on that property. If you move a document from one Document Set to another Document Set, the document’s inherited metadata is updated to match!

https://technet.microsoft.com/en-us/library/ff603637.aspx

https://support.office.com/en-us/article/Introduction-to-Document-Sets-C49C246D-31F1-4BFE-AFE2-E26698B47E05

https://support.office.com/en-us/article/Create-and-configure-a-new-Document-Set-content-type-9DB6D6DC-C23A-4DCD-A359-3E4BBBC47FC1

 

Folders can be nested more than two levels deep

Using views and metadata you can create two levels of grouping. If you have SharePoint 2007 or 2010, you can use SharePoint Designer to create views that are up to 16 levels deep. But for SharePoint 2013 and 2016 they have changed (broken) SharePoint Designer so you can only group deeper than two levels by hand crafting XLST and HTML.

You can nest folders as much as needed, up to the maximum URL limits of Path to Library + Folders + Filename.

 

Folders are ideal for a rigid taxonomy

If the primary way of accessing content is by a single hierarchy then a folder structure may be the better choice. While still limited to the maximum length of a URL, it clearly supports more than the two levels offered by a grouped view.

   image

Want a full crumb trail like we had in SharePoint 2007? See here: http://techtrainingnotes.blogspot.com/2015/11/add-crumb-trail-to-sharepoint-2013.html

 

Folders can be navigated using a Tree View

There are actually two tree views available, one out of the box, and one that is hidden.

The Quick Launch Tree View (Settings, Site Settings, Navigation Elements):

   image

The hidden SharePoint 2010 “Navigate Up” button:

(See: http://techtrainingnotes.blogspot.com/2014/06/sharepoint-2013-restoring-2010-navigate.html)

   image

Note: Currently neither Tree View is available in the “new library experience” for SharePoint Online, and one day for SharePoint 2016 on premises.

 

Metadata is not always searchable as a property

Unless you have created Site Columns, and configured them as friendly search Managed Properties, then as far as seach is concerned, all of those columns of metadata might have just been typed into a single “Keywords” column.

 

Search Likes Folders

Search includes several managed properties to make finding folders and content in folders easy to do. Unlike Site Columns, these folder properties do not require any Search Service setup to work.

Path:    path:https://yourServer/sites/site/library/folder
            path:"https://yourserver/sites/taxonomy/Fish/Agnatha and Lampreys/Myxini/Myxiniformes"

Searching with Path works, and is very precise, and returns all of the content in that path. The negative is typing the full path to the folder.

contenttype:folder     contenttype:folder Myxiniformes

contenttype finds all folders and all content types that inherit from Folder. (This will also return folders that have a column with the keywords being searched. In the example above you will get folders with “Myxiniformes” in the folder name and folders with a column with “Myxiniformes” in its name.)

IsContainer:true        IsContainer:true Myxiniformes

IsContainer returns Sites, Libraries and Folders that have the keyword in their name or metadata. IsContainer also returns Team Site Notebooks (OneNote files) and content stored in Asset libraries (The thing you click on in an Asset library is a folder, not the actual picture or video.) as they are represented as folders.

Library search box

The search box at the top of each library assumes you only want to search the content in the current folder! (You can then click “Some files might be hidden. Include these in your search” to search the rest of the library.)

   image

 

Microsoft / SharePoint Really Likes Folders!

Take a look at OneDrive for Business… you can’t even add metadata columns or use Content Types. “Name”, “Modified”, “Modified by”, “File Size” and “Sharing” are all you get. The only “metadata” I can add is by using folders.

    image
    (Yes, I really have a folder named “junk”!)

In my OneDrive I have to embed metadata in the filename and/or the folder structure. Kind of like network shares!

    image

 

The New Library Experience likes folders!

The new library experience in Office 365 makes it easy to arrange and rearrange documents by folder. (Seems to encourage the use of folders!)

    image

 

Sync Only Sync’s Folders

All three of the sync clients only sync folder structure, not metadata. If you want any obvious classification of your local sync of the content then you have to use folders. The only metadata you can add from client side is in the filename and the location/folder.

image

 

Security and Folders

Remember when Microsoft’ advise was to never use item level permissions? At least until SharePoint 2013 where they gave everybody a “Share” button. Now SharePoint 2013 and 2016 encourage users to break inheritance everywhere!

See here for what can happen with unlimited use of the Share buttons: http://techtrainingnotes.blogspot.com/2015/10/trick-or-treat-day-in-life-of.html

For a simple example consider:

  1. We create a site for Sales Managers. We create a library for their files.
  2. The sales managers start clicking the Share links on various documents, most to share with the “Summer Interns” group and the “Marketing Team” group. Over time there are 500 items with broken inheritance.
  3. Management asks you to add Regional Sales Managers to the site, with their own group.
  4. You create a SharePoint group and add the Sales Managers and grant it access to the site.
  5. The Regional Managers visit the site and complain that they can’t find all of the files the Sales Managers have told them about.
  6. You now have to:
    1. Find the 500 files with broken inheritance.
    2. Grant permissions to each of the files to the Regional Managers group.

So what can you do? Use folders for permissions.

  1. Create the library.
  2. Add a folder for “Everyone”. (Optional as the files in the root of the library will be available to everyone by default.)
  3. Add a folder for “Sales Managers Only”. Break inheritance and grant permissions to the Sales Managers group.
  4. Add a folder for “Visible to Marketing Team”. Break inheritance and grant permissions to the Sales Managers group and to the Marketing Team group.
  5. Add a folder for “Visible to Interns”. Break inheritance and grant permissions to the Sales Managers group and to the Interns group.
  6. Create a new view named “Sales Files”:
    1. Make it the default view.
    2. In the Folders section hide the folders.

Users will now see a single list of content, which can also be grouped using metadata, but they will only be able to see the content they should see. The users who maintain the content use the AllItems view so they can quickly upload documents into the correct folder, and automatically apply the correct permissions. (Now all you have to do is hide those pesky Share buttons! http://techtrainingnotes.blogspot.com/2015/08/hiding-evil-sharepoint-2013-share.html)

 

So which should you use?
  Folders or
    Metadata+Views or
      Folders+Metadata+Views?

Use the best tool for the job!

 

.

5/16/2016

Ed Wilson, the Microsoft Scripting Guy, Cincinnati June 15th!

 

Ed Wilson, the Microsoft Scripting Guy, will be presenting at the Cincinnati PowerShell User Group meeting on June 15th at MAX Technical Training.

Topic: Configuration Management with Azure Automation DSC - Cloud & On-Prem, Windows & Linux

Info and regestration here: http://www.meetup.com/TechLife-Cincinnati/events/230743256/

See you there!

3/15/2016

SharePoint 2013 and SharePoint Online Built-In Accounts

 

image

I’m often chasing one SharePoint rabbit or another down a rabbit hole and spending hours there when I only wanted to ask the rabbit one simple question. In this case the question was who is “Everyone” and are they related to “NT AUTHORITY\Authenticated Users”. A simple question, or so I had thought. In this rabbit hole I found all kinds of interesting accounts, so I thought that I’d take a few notes while I was there. As to “Everyone”, I’ll follow up with another blog article. I also got distracted by two Office 365 users hanging around the hole named “Guest Contributor” and “Guest Reader” that will also get their own article.

If you would like to dive into the rabbit hole, here’s a few tools to investigate user accounts:

  • PowerShell on prem: 
    $site = Get-SPSite http://yourDomain/sites/yourSite
    $site2.RootWeb.AllUsers | FT –AutoSize
  • PowerShell for Office 365:
    Get-SPOUser -Site http://yourDomain/sites/yourSite | Select DisplayName, LoginName
  • In the browser:
    • Go to Settings (gear), Site Settings, People and Groups
    • Edit the URL and change the GroupId to 0    (“?MembershipGroupId=0”)
    • Click any interesting user name. If the user has a User Profile you will probably be redirected to their profile page. If not, you will be redirected to userdisp.aspx where you can see the user name and their internal Account property as listed in the table below.
  • Third party security and auditing tools.

 

Claims Based Authentication

SharePoint 2013 and later uses Claims Based Authentication which can support more than one authentication source. This slightly complicates the UserLogin property as it must have both the user name and the claims source data in the property value. In a non-Claims system the user name might be as simple as contoso\msmith. In a Claims system you need to know where the user was authenticated, so you end up with UserLogins that might look like i:0#.w|contoso\msmith for a Windows AD user or i:0#.f|ContosoFBA|susan for a Forms Based Authentication user.

If you would like to learn more about the Claims identity codes (“c:0!.s”, etc.) see: http://social.technet.microsoft.com/wiki/contents/articles/13921.sharepoint-2013-claims-encoding-also-valuable-for-sharepoint-2010.aspx
and
http://www.wictorwilen.se/Post/How-Claims-encoding-works-in-SharePoint-2010.aspx

 

The Users

image

Who are all of these users? Well… I’m still negotiating with the rabbit for more details, but I’ll soon add these articles with what I have discovered:

  • SharePoint: All Users vs. Everyone vs. Everyone But External vs. NT AUTHORITY\AUTHENTICATED USERS
  • SharePoint Online “Guest Contributor” and “Guest Reader” - Who’s Guest Contributor,
    and what are they doing in my library?
  • SharePoint internal and hidden accounts hiding in your Site Collection

For now:

  • NT AUTHORITY\AUTHENTICATED USERS represents all of the users in your Active Directory, on prem or in the cloud.
  • Everyone at the AD level is NT AUTHORITY\AUTHENTICATED USERS plus the Guest account. The Guest is disabled both by default and as a best practice. (You don’t see this one in SharePoint, but it is often listed as being the same as the SharePoint “Everyone”.)
  • Everyone is defined at the SharePoint level and includes all users authenticated to SharePoint.
  • Everyone except external users is found in SharePoint Online / Office 365 and is as named. External users are people not in your Active Directory, most likely not employees, who got their access from site members clicking the SHARE buttons.
  • All Users (<somename>) is SharePoint defined and represents all of the users from a selected authentication provider. (If I created a Forms Based Authentication provider named “Vendors” then I would have “Everyone (Vendors)”
  • All Users (windows) is SharePoint defined and is same as NT AUTHORITY\AUTHENTICATED USERS. After adding “All Users (windows)” to a site it is displayed as “All Users (windows)” in 2013 on prem and 2016 on prem, but is displayed as NT AUTHORITY\AUTHENTICATED USERS in Office 365.
  • Guest Contributor and Guest Reader are at this time only found in SharePoint Online / Office 365 and represent users with anonymous / link access.

 

Best Practices

I was reviewing some training materials recently and ran across a statement to the effect you should put NT AUTHORITY\AUTHENTICATED USERS in all of your site Visitors groups so everyone can find content in SharePoint. Should you do this? Should everything in your SharePoint be freely accessible to everyone who can logon to your network? Contractors, vendors, summer co-ops, part timers? If you don’t already have a policy or governance on this, then you should be working on it.

SharePoint does not give us any way to prevent the use of the “Everyone” accounts, so you will need to deal with this through education and auditing.

UPDATE! Anders Rask responded to this post with info about a SharePoint Online cmdlet that can hide these “everyone” options in the people pickers. Turns out there are three options:

Set-SPOTenant -ShowEveryoneClaim $false
Set-SPOTenant -ShowEveryoneExceptExternalUsersClaim $false
Set-SPOTenant -ShowAllUsersClaim $false

The Set-SPOTenant cmdlet: https://technet.microsoft.com/en-us/library/fp161390.aspx 
Blog:
https://blogs.office.com/2015/07/16/new-it-management-controls-added-to-onedrive-for-business/

 

Here’s a short list of best practices. The term “everyone” used here includes NT AUTHORITY\AUTHENTICATED USERS and any account that starts with “Everyone” or “All Users”.

  • Educate your users on security, including the use of the “everyone” accounts.
  • Do not use “everyone” accounts if a site contains non-public data.
  • Document who “everyone” is. There’s more than one “everyone” group in SharePoint.
  • Perform regular audits using PowerShell or 3rd party tools to track the usage of “everyone” groups.
  • Document, audit and enforce your SharePoint content policies. Document what is allowed, and what is not allowed to be stored in SharePoint.
  • If you do encourage the use of the “everyone” groups, add a banner to the top of every page that declares “Do not post confidential data in this SharePoint site! It can be seen by everyone with network access.”

 

The Built-In Accounts

While your SharePoint may vary… see the Notes column… here’s a list of the accounts that may include users other than those who you were expecting. This is not complete, so if you discover others please post a comment to this article.

DisplayName UserLogin or SystemUserKeyProperty Notes
     
All Users (membership) 
c:0!.s|forms%3amembership
Only O365
All Users (windows) 
c:0!.s|windows
Same as NT AUTHORITY\ authenticated users
All Users (yourFBAMembershipProviderName) c:0!.s|forms%3aYourFBAMembershipProviderName Form Based Authentication
Everyone c:0(.s|true   
Everyone except external users c:0-.f|rolemanager|spo-grid-all-users/17b83262-5265-… Only O365 (ID will vary)
NT AUTHORITY\ authenticated users c:0!.s|windows  
Guest Contributor SHAREPOINT\writer_9e8a77849f89425c9cff6a6af5175… ID varies with share
Guest Reader SHAREPOINT\reader_cb6f6371456b4542ba0609638a4…  
     
     
_SPOCacheFull ylo001\_spocachefull Only O365. Visible only from PowerShell
_SPOCacheRead ylo001\_spocacheread Only O365. Visible only from PowerShell
_spocrawler_17_3910 ylo001\_spocrawler_17_3910 Only O365 (ID will vary)
System Account SHAREPOINT\system Visible only from PowerShell
System Account S-1-0-0 SystemUserKeyProperty
Company Administrator s-1-5-21-1851826741-1401831065-3463747319-87287… Only O365 (ID will vary)
     
Typical user (Sam Conklin) samc@yourDomain.onmicrosoft.com As seen in O365 PowerShell
Typical user (Sam Conklin) i:0#.w|yourDomain\samc As seen in On Prem PowerShell
Typical user (Sam Conklin) i:0).w|s-1-5-21-2499188511-2905385804-3446143336-… SystemUserKeyProperty
Typical FBA user (Susan) i:0#.f|YourFBAMembershipProviderName|susan Form Based Authentication

.

3/11/2016

New SharePoint 2016 Courses Coming from Microsoft

 

Microsoft is releasing two new SharePoint 2016 administrator courses in July. The courses have a new numbering scheme to better reflect the “Part 1” and “Part 2” nature of the pair. 20339-1 and 20339-2. While the focus is on on-premises SharePoint 2016, some Office 365 content is included.

Course 20339-1: Planning and Administering SharePoint 2016
https://www.microsoft.com/en-sg/learning/course.aspx?cid=20339-1
Publish date: July 8, 2016

Course 20339-2: Advanced Technologies of SharePoint 2016
https://www.microsoft.com/en-sg/learning/course.aspx?cid=20339-2
Publish date: July 21, 2016

.

3/10/2016

SharePoint: I Just Love Consistency! SharePoint, Not So Much

 

SharePoint 2016 is so similar to SharePoint Online / Office 365 that it’s sometimes hard to tell which version you are in. I just ran across an odd change from the past versions. How do you share or break inheritance on a list item?

The steps are different and the end results are different. And I thought 2016 was supposed to be SharePoint Online brought onsite.

 

SharePoint 2013

  • Shared With is available in the ITEMS ribbon and the ribbon of the View Properties page.
  • The “…” menu has a Shared With option:
      image
  • The above link takes you to the Shared With popup.

 

SharePoint Online / Office 365

  • Shared With is available in the ITEMS ribbon and the ribbon of the View Properties page.
  • The “…” menu has a Share option:
      image
  • The above link takes you to the Shared With popup.

 

SharePoint 2016

  • Shared With is grayed out in the ITEMS ribbon, but is available in the ribbon of the View Properties page.
  • The “…” menu has an Advanced popout that has a Manage Permissions option:image
  • The above link takes you to the user.aspx Permissions page.

 

 

I write training materials and really wish I was paid by the word or page! These detail differences waste hours and complicate training!

 

.

SharePoint: Undocumented Pending Shares Page

 

Article applies to SharePoint 2013, SharePoint Online and SharePoint 2016.

 

image

Did you ever wonder after using the Share buttons in SharePoint if the Site Owner ever responded to your request, responded with a question, or approved the request?

The My Permissions page

As I can’t find any documentation, I’ll call this undocumented for now… After a bit of web searching I did find a mention of the page in an Ignite presentation. In any case, this page lists the status of pending requests and lets the user who made the request check and send messages to the site owners. Requests that have been approved or declined will not be listed here.

The site owner can see your requests by going to Settings (gear), Site Settings, Site Permissions and clicking “Show access requests and invitations”. This will take them to the Access Requests page at _layouts/Access%20Requests/pendingreq.aspx.

You can check your pending requests by going to:
http://yourDomain/sites/yourSite/_layouts/mypermissions.aspx
This link will redirect to /_layouts/15 for now and may change in future versions.

Of course, no one knows about this page. There are no out of the box links to it. And… the site owner will probably not know to click the “SEND” button to start a conversation with the person who made the request.

If “Sharing” is important in your organization, you will need to provide some training, easy access to a link to the MyPermissions page, and do some work to “drive adoption”.

image

 

Details:

  • Only “Pending” requests are displayed. Approved requests are not.
  • You can click the “…” to see messages from the site owner, or to send a message to the site owner.
    image
  • This pending invites listed are unique to the current site. I.e. each site has its on MyPermissions page.

 

.

3/08/2016

SharePoint PowerShell Training for Auditing and Site Content Administration

 

image

If you have followed this blog, you know that I’m kind of a SharePoint nut who’s also a PowerShell nut. Over the years I have created a lot of PowerShell scripts while working with SharePoint and answering questions in my classes and in the TechNet forums. There’s plenty of resources for installing and configuring SharePoint using PowerShell, but there’s little on dealing with all of the daily questions on premise admins get that can be quickly answered using PowerShell. I was just going to take my 100+ scripts and create something like a “cookbook”, but instead created a class. The class handout kind of ended up as the cookbook… 85 pages and 175 sample scripts, or maybe more like a giant PowerShell cheatsheet for SharePoint.

This class is for on-premise SharePoint 2010, 2013 and 2016 administrators. A SharePoint Online version is in the works, but not available yet.

If you would like to attend this class, delivered by the author (your’s turely!), we are offering it next Monday, March 14th, at MAX Technical Training in Cincinnati, Ohio. You can attend this class at MAX or remotely from anywhere. If you can’t attend this class, it is available from trainging centers all over the world.

March 14th class at MAX: SharePoint 2010 and 2013 Auditing and Site Content Administration using PowerShell

Search for this class at other training centers: https://www.bing.com/search?q=55095%20powershell

If you would like to see some of the other courses and books I’ve written, then click here.

 

55095 SharePoint 2010 and 2013 Auditing and Site Content Administration using PowerShell

This one day instructor-led class is designed for SharePoint 2010 and 2013 server administrators and auditors who need to query just about anything in SharePoint. The class handout is effectively a cheat sheet with over 175 PowerShell scripts plus the general patterns to create your own scripts. These scripts cover:

  • using the SharePoint Management Shell and the ISE
  • general tips for counting, reformatting and exporting results;
  • drilling up and down the SharePoint object model
  • getting lists / inventories of servers, services web applications, sites, webs, lists, libraries, items, fields, content types, users and much more
  • finding lists by template type, content type and types of content
  • finding files by user, content type, file extension, checked out status, size and age
  • finding inactive sites
  • finding and changing SharePoint Designer settings and finding and resetting customized pages
  • inventorying and managing features
  • deleting and recycling files and list items
  • inventorying users and user permissions and finding out “who can access what”
  • creating sites, lists and libraries
  • uploading and downloading files
  • and much more…

At Course Completion

After completing this course, students will be able to:

  • Use PowerShell to query just about anything inside of SharePoint.
  • Understand the core SharePoint object model and object hierarchy as seen from PowerShell.
  • Format PowerShell output in to reports.
  • Manage resources to limit the impact on production servers.
  • Create and delete Site Collections, subsites, lists, libraries and content.

Prerequisites

Before attending this course, students must:

  • Very good knowledge of SharePoint and its features.
  • Good experience using PowerShell 2 or later or recent completion of a PowerShell class such as 10961 or 50414.

Course Outline

Module 1:  SharePoint and PowerShell

This module provides an introduction to the topics covered in the class, introduces SharePoint PowerShell terminology and provides a review of important PowerShell features.

Lessons:

  • History of PowerShell in SharePoint
  • PowerShell vs. Search
  • PowerShell, SharePoint Management Shell and cmdlets
  • Security and Permissions Needed
  • Getting Started with PowerShell: Counting Items, Custom Columns, Reformatting Numbers, Saving Results to a File
  • Changing and Updating Content: Creating SharePoint Objects, Changing Objects

Lab:

  • Using PowerShell with SharePoint

After completing this module, students will be able to:

  • Get started using PowerShell to inventory and update SharePoint.

Module 2: Working with SharePoint CMDLETs and Objects

This module introduces the SharePoint object model and some important terminology.

Lessons:

  • GUIDs
  • Sites vs. Webs
  • The SharePoint Object Hierarchy

Lab:

  • Get a list of all Site Collections and their GUIDs
  • Get a list of all Webs in all Site Collections
  • Given a web’s URL get its parent web and web application

After completing this module, students will be able to:

  • Explore sites and webs using PowerShell.
  • Retrieve important properties of common SharePoint objects

Module 3: Managing Memory and Limiting Performance Impact

This explores limiting impact on server memory usage and performance.

Lessons:

  • Memory Management and Disposing Objects
  • Limiting Impact on Production Servers

Lab:

  • Exploring PowerShell’s use of system memory.
  • Testing the impact of scripts on server performance

After completing this module, students will be able to:

  • Recognize and manage the impact of PowerShell on a SharePoint server.

Module 4: Working with Content

This module explores SharePoint using PowerShell from the Farm down to individual list items.

Lessons:

  • Getting Farm Information: version, services, services, features
  • Getting Web Application information
  • Exploring Site Collections: retrieve Site Collections, Site Collection Administrators, quotas
  • Working with the Recycle Bins: finding items, getting file counts and bytes, deleted sites
  • Exploring Webs: web templates, finding webs, finding webs based on template, Quick Launch and Top Link Bar navigation
  • Exploring Lists and Libraries: finding all lists, lists by type, lists by Content Type, columns/fields, document count by web or library
  • Exploring Content Types
  • Finding documents: by a word in the title, file type, content type, size, date age, checked out status, approval status and many more…
  • Deleting content
  • Downloading and uploading files

Lab:

  • Explore the farm.
  • Inventory site collections.
  • Create a recycle bin report.
  • Finding all blog sites.
  • Find all picture libraries.
  • Find all PDF files over 5 MB.
  • Delete all videos in a site collection.

After completing this module, students will be able to:

  • Explorer, inventory and maintain SharePoint content using PowerShell.

Module 5: Users and Security

This module covers the use of PowerShell to explore and document SharePoint permissions.

Lessons:

  • Users: find a user, get a list of all users, working with Active Directory groups
  • SharePoint groups: Get lists of groups, get the members of a group, find all groups a user belongs to, find the groups associated with a web
  • Expanding users lists that include Active Directory groups
  • Documenting Broken Inheritance / Unique Permissions: webs, lists, libraries, folders, items
  • Working with Role Assignments

Lab:

  • Get a list of all users who have access to a Site Collection.
  • Get a list of all groups in a Site Collection.
  • Get a list of all groups a user belongs to.
  • List all users who may have access to a SharePoint securable.
  • Get a list of all securables with broken inheritance.

After completing this module, students will be able to:

  • Explore and document users and user permissions.
  • Explore and document SharePoint groups.
  • Explore and document broken inheritance.

Module 6: Managing Sites

This module explorers Site Collection and Web management from PowerShell.

Lessons:

  • Finding Inactive Webs
  • Creating and Deleting Site Collections
  • Getting Site Collection Data
  • Creating and Deleting Subsites
  • Working With SharePoint Designer Settings

Lab:

  • Create a report for inactive sites.
  • Create a site collection and subsites.
  • Delete a site.
  • Delete a site collection.
  • Disable SharePoint Designer in all site collections.

After completing this module, students will be able to:

  • Manage SharePoint Site Collections and webs from PowerShell.

Audience

  • SharePoint server administrators.
  • SharePoint auditors.
  • Also valuable for SharePoint developers.

3/06/2016

SharePoint: The Waffle button is the Waffle button!

 

Something for the SharePoint trivia department…

What do you call this:

image

I had always thought that calling the App Launcher button in Office 365 and SharePoint 2016 the “waffle button” was a bit of a joke. It does look kind of like a waffle, there is no mouse-over tip to give a hint, and no one would ever guess “App Launcher”. Turns out that the word “waffle” is actually in the HTML! So going forward, I will consider both to be an acceptable name for the button.

image

 

Ok, back to work… do something useful…

 

.

Note to spammers!

Spammers, don't waste your time... all posts are moderated. If your comment includes unrelated links, is advertising, or just pure spam, it will never be seen.