(this post was written using a SharePoint 2010 environment)
A while ago I was trying to find a way of getting a list of all user profiles that didn’t have a profile picture uploaded. I searched around online a bit and could only find examples that involved writing PowerShell which I thought was way to complicated a thing to do!
So this post will show how you can easily export user profile data from SharePoint, apply some excel filters to find all users without a profile picture.
This method applies to all on-premises versions of SharePoint, but also will work for SharePoint Online.
Get user profile data
To get started, open the Excel desktop app
Press Get Data > From Other Sources > SharePoint List
Enter the root site collection URL for your SharePoint environment
Press OK
Press the From SharePoint List option within Get Data in Excel.Enter your root site collection URL here.
This will open a navigator window, which will display all the available lists within the root site collection.
Scroll down until you find the UserInformationList
Click on it and a preview will load, scroll across the preview and make sure you can see the Picture column
Press Load
Make sure you select the UserInformationList and that the Picture column is visible.
NOTE: at first I was using the Get Data > From Web option but that only brought back the first 30 rows from the UserInformationList.
I didn’t want to increase the view limit as there are thousands of rows to display but I found that the way described doesn’t have that issue.
Now this will take a little while to download all the rows if you have a fair few users, but you’ll see when its finished in the right-hand Queries and Connections pane as it shows you the number of rows downloaded once complete.
Filter your data
The first thing to note about this data is that it does also contain some stuff you wont need. With my list data I noted that I had SharePoint security and domain groups, service accounts and other general use/ non-user accounts listed. Here are some of the filters and logic I applied:
Filter the Picture column to only show blanks
Filter the Content Type column to only show Person
Or filter the ContentTypeID to only show the corresponding Person content type ID
Filter SIPAddress to only show rows with an organisational email address
That’s all there is to it, a pretty quick and super easy way to get a list of all user profiles without a picture 🙂
Data retention and deletion…I’m sure this is a something that anyone involved in Office 365, SharePoint on information management in general gets fed up of saying since the recent GDPR legislation!
Recently we have been rationalising and cleaning up our data in preparation for moving to Office 365. We are starting with SharePoint as the first target repository or silo of content.
The general consensus is to delete files and folders over 7 years old unless there is a pre-existing data retention policy to adhere to. So the next task is to identify those files that fall within our threshold, and ultimately delete.
Luckily, we have Tree Size Pro and ShareGate so I was able to relatively easily identify the files in question (there were a lot!).
The setup
As our SharePoint environment is a) rather full; and b) rather old, I made the decision to incrementally delete files rather than en-masse to mitigate risk, targeting the lists/libraries containing the most out of date content. I started by creating a view in the first library – library A with the following parameters:
Standard library view
Filtered by Created Date if less than or equal to 01/01/2011
Folders or Flat: Show items inside folders Show this view: In all folders
(all other settings are left default)
Results this returned looked good, I could see folders and files in this view that matched the criteria – brilliant! Based on my previous statement I decided to delete in batches out of working hours, again to mitigate risk. I deleted first from library A, then from the first stage and finally from the second stage recycle bin all in this fashion.
The problem
I had permanently deleted around 50% of the total volume of content to be deleted from library A when we started to receive reports of current files being ‘missing’ from library A…not a good day.
After these reports were investigated they were indeed true. It turns out that when folders are included within a library view, folders that match the filter will be shown in the view, regardless of whether the files inside match.
We tested the view exluding folders and all the files returned matched the filter criteria. The same results were demonstrated from a SharGate report of the same nature. The report of all files over 7 years old brought back folders over 7 years old, but they also contained files that were newer.
Conclusion
At present, we are not entirely sure as to why these filters are not able to drill down past a top-level folder. It appears to be difficult to specify via view settings to only show files within folders, including the folder itself that matches the criteria.
We have decided to omitt folders from our reports and views going forward and to solely focus on files as this is the most reliable way we can delete files.
Bonus: for those of you with ShareGate, heres an example of my report we created to bring back all files over 7 years old, excluding folders. I ran this report across the entire intranet application over a weekend and it worked a treat 🙂
Firstly, full credit for this information goes to Mischa Oudhof and this sysadmin blog for this really useful post in 2010! I thought i’d write it up here just for my own sanity (and incase that page were to ever not exist) plus add a few extra bits of my own.
So recently a user was trying to upload multiple documents into SharePoint 2010 by copying/pasting using file explorer for the destination library. When copying across the files they received something like this message:
What are the restrictions?
There are a whole load of restrictions when it comes to file name, length, characters within SharePoint. This affects site collections, sub-sites libraries or lists. Here are some of the more general restrictions:
File and folder name lengths can’t exceed 128 characters in both WSS 2.0 as WSS 3.0
Link list items can’t exceed 256 characters
The entire path of files can’t exceed 260 characters
Here are some, more specific restrcitions again applying to site collections, sub-sites, libraries or lists:
Can’t be longer than 128 characters
Can’t use: ~ # % & * { } \ : < > ? / + | ”
Can’t use the period character consecutively in the middle of a file name (blah…blah.docx)
Can’t use the period character at the end of a file name
Can’t use the period character at the start of a file name
Can’t end with:
.files
_files
-Dateien
_fichiers
_bestanden
_file
_archivos
-filer
_tiedostot
_pliki
_soubory
_elemei
_ficheiros
_arquivos
_dosyalar
_datoteke
_fitxers
_failid
_fails
_bylos
_fajlovi
A deep dive into all the potential restrcitions can be found in the msdn blog here.
Things have changed in Office 365…
Microsoft announced in 2017 that the old maxpath limit has been expanded to 400 Unicode code units. Microsoft also announced more URL improvements for the support of # and % characters.
I had this issue myself in the last week where EVERY SINGLE workflow across the farm on premise stopped working. SharePoint Designer and Nintex workflows all reported “Failed to start” when triggered to run.
The workflows stopped working due to a series of .NET security updates Microsoft released in September 2018. Microsoft released a public KB article on this – with resolution steps which can be found below:
I noticed shortly after the fix was implemented that some of my SharePoint designer workflows were exhibiting odd behaviour. For example the screenshot below shows a SharePoint desinger workflow that previously worked without issue or errors in the history after the fix was applied:
Someone on reddit had already spotted this which drew my attention to the common issue, this only presents itself for workflows with pause steps!
I will update this post with my findings once this latest fix is applied.
Symptom
After applying .NET Security Only patch to resolve CVE-2018-8421 (Remote Code Execution Vulnerability) , all SharePoint out of the box Workflows fail to execute and the log will show an error like this:
09/13/2018 01:59:07.57 w3wp.exe (0x1868) 0x22FC SharePoint Foundation Workflow Infrastructure 72fs Unexpected RunWorkflow: Microsoft.SharePoint.SPException: <Error><CompilerError Line=”-1″ Column=”-1″ Text=”Type System.CodeDom.CodeBinaryOperatorExpression is not marked as authorized in the application configuration file.” /><CompilerError Line=”-1″ Column=”-1″ Text=”Type System.CodeDom.CodeBinaryOperatorExpression is not marked as authorized in the application configuration file.” /><CompilerError Line=”-1″ Column=”-1″ Text=”Type System.CodeDom.CodeBinaryOperatorExpression is not marked as authorized in the application configuration file.” /><CompilerError Line=”-1″ Column=”-1″ Text=”Type System.CodeDom.CodeBinaryOperatorExpression is not marked as authorized in the application configuration file.” /><CompilerError Line=”-1″ Column=”-1″ Text=”Type System.CodeDom.CodeBinaryOperatorExpression is not marked as authorized in the application configuration file.” /><CompilerError Line=”-1″ Column=”-1″ Text=”Type System.CodeDom.CodeBinaryOperatorExpression is not marked as authorized in the application configuration file.” /><CompilerError Line=”-1″ Column=”-1″ Text=”Type System.CodeDom.CodeBinaryOperatorExpression is not marked as authorized in the application configuration file.” /><CompilerError Line=”-1″ Column=”-1″…
The error suggest that System.CodeDom.CodeBinaryOperatorExpression is not in the authorized types.
Cause
Workflow Foundation (WF) will only run workflows when all the dependent types and assemblies are authorized in the .NET config file (or added explicitly via code) under this tree:
<configuration>
<System.Workflow.ComponentModel.WorkflowCompiler>
<authorizedTypes>
<targetFx>
However, after the update, the following lines are necessary for SharePoint 2013 and beyond:
Please notice that sometimes SharePoint Timer Service (SPTimerV4) runs workflows. If you notice that the application showing the error is ULS logs in OWSTIMER.EXE, you should also include the authorized types in [SharePoint Hive Folder]\bin\OWSTIMER.EXE.config. The Hive Folder will change by version of SharePoint. For SharePoint 2016, it is normally at c:\program files\common files\microsoft shared\web server extensions\16. For 2013, at c:\program files\common files\microsoft shared\web server extensions\15.
If you have Nintex workflows you should run like this:
Add-CodeDomAuthorizedType -IncludeNintexWorkflow
To undo the changes, run:
Remove-CodeDomAuthorizedType
The script needs to run only once on any WFE. All web.config files related to SharePoint on all servers will be modified. New web applications created after that will also include the changes. Even if a new WFE is added to the farm, the entries will also be included in web.config. The change is a permanent requirement from now on since the WF patch. You do not need to undo the change before applying the SharePoint patch addressing it.
There is a second script to update OWSTIMER.exe.config. This one should only run if you see the symptoms in ULS logs with process OWSTIMER.EXE. Otherwise, you do not need to update. if you have the problem though, you need to rerun the script if a new machine is added to the farm. No line needs to be uncommented for this one. The script name is:
Microsoft is aware of this issue and patches for SharePoint 2010, 2013 and 2016 are being worked as of 9/17/2018. I will update when we have an ETA. I had confirmation from the product team on 9/18/2018 that this information and solution on this post is in the line with the future patch and it is the recommended action plan until the patch is out. If anything change, I will update the post.
Note 2
Some people using third-party workflows (like Nintex) need to also include this:
(This post was written using a SharePoint 2010 environment)
I recently found myself needing to create a site template for a customer who required multiple sites created following a base format. So I decided to create a site template to achieve this to speed up the site creation process for said customer.
At first I went into the site settings of the site that the template would be based off to create it. I looked under look and feel in site settings, but alas the option to save as site template wasn’t there!
Next, I accessed the site via SharePoint Designer, looked under the site ribbon menu and would you believe the save as template button was greyed out there also!
After researching online, it appears there can be problems creating templates from publishing sites in SharePoint 2010, however I found that following the steps below enabled me to create a new site template and activate it not problem!
Navigate to the site collection/ sub-site you wish to create the template from
Add the following to the URL at the end of the site name
/_layouts/savetmpl.aspx
Go back to the site collection or sub-site you wish to create new sites under
Site Actions – Site Settings – Look and Feel – Page Layouts and site templates
Find your newly created site template within the subsite templates list
Press Add
Press OK
Please note: if your sub-site is inheriting from the parent site, you will need to navigate to the parent and add the subsite template there for it to be available.
UPDATE 27/08/2019
Saving as site template is not a supported feature of SharePoint Online for modern sites. You will find for modern sites that this option is not available, but you have the ability to re-instate it via activating a feature. The official stance from Microsoft is:
Just to be clear. Using save-site-as-a-template with modern
team sites or publishing sites is NOT supported. You can always find
aways with tenant admin permissions to achieve things, but the fact that
it’s doable does not mean it’s supported.
Any sites created with the template which has been produced with this
method can have unexpected issues in the future, so provided steps
should never be used.
Best regards,
Vesa Juvonen, Senior Program Manager, SharePoint Engineering, Microsoft.
UPDATE 27/08/2019
Saving as site template is not a supported feature of SharePoint Online for modern sites. You will find for modern sites that this option is not available, but you have the ability to re-instate it via activating a feature. The official stance from Microsoft is:
” Just to be clear. Using save-site-as-a-template with modern team sites or publishing sites is NOT supported. You can always find aways with tenant admin permissions to achieve things, but the fact that it’s doable does not mean it’s supported.
Any sites created with the template which has been produced with this method can have unexpected issues in the future, so provided steps should never be used. “
– Vesa Juvonen, Senior Program Manager, SharePoint Engineering, Microsoft.
Have you ever been asked to hide a list or library from a SharePoint site? If so, you go straight for selecting ‘no’ to displaying the list or library on the Quick Launch or removing it from the navigation. However, your eagle eyed users notice the handy view all site contents option and see that it is still listed there – they want it gone!
Luckily, all you need is SharePoint Designer and it is as simple as a click of a button…
(These steps were created using SharePoint Server 2010)
Open the site that where list or library resides in SharePoint Designer
Under Lists and Libraries – Select the list or library you wish to hide
On the main list settings page – find the Settings section
Check the Hide from browser option
Thats it! when you option the view all site content page now, that list or library will no longer be showing. Also, if you want to re-instate it at a later date, just un-check the box and it will re-appear.
This also works for SharePoint 2013, 2016 and SharePoint Online, under the site contents page.
