Powershell Script to Efficiently Locate Large Files in SharePoint 2010/2013

23 Mar
March 23, 2015

Recently I had the request to locate all files that are bigger than 40 MB – and they were located in the entire farm across multiple Site Collections.  Easy you might say? Just use search and query for


Well, I said efficiently and thats the most efficient way – you are right! They key constraint here is that those files were excluded from search – so lets go for the PowerShell way.

The Script

Here comes the script that uses PowerShell to iterate over each Site Collection, every Web and every List – then uses CAML to query each list for items larger than the specified size. It works for SharePoint 2013 and should definitely work for 2010, too.

Enough words, here we go:


The result looks like this:

Get large files script in action

Get large files script in actionYou can easily export that to a csv file

You can easily export that to a csv file with:

$result | export-csv result.csv

I ran it again a quite large SharePoint 2013 environment (~1 TB, 1075 Site Collections) and it came back in ~5 minutes.


You see room for improvement for my PowerShell script to efficiently locate large files? If so, please tell me!

Its over! (My 2014 Review)

04 Jan
January 4, 2015

Traditions are traditions – at least when you repeat at least once, right? So last year I started to review my year with SharePoint – lets do it again.

Conferences & Social

2013 started great – after several attempts I convinced my company to send me to the very last SharePoint Conference in Las Vegas – and it was epic. Not only that I covered 30 SharePoint sessions, I met friends and new friends – I knew some of them via Twitter for a long time, but meeting in person is a totally different story, of course! If you wanna relieve my experience including my summary you can read my posts here.

On the speaking side, I submitted 10-15 sessions this year, some where accepted, some rejected. I was accepted in Zürich, Switzerland at Collaboration Days (international speaker, yay!) and in Duesseldorf at ShareConf, Germany – I had good fun and received good feedback. The most challenging speaking engagement was SP24 – even tho my session was pre-recorded. My inner devil forced me to record my session (50 Minutes) multiple times – in the end I did not need a script, even for the jokes ;)

Additionally I visted the User Group in Munich several times – of course I had a search talk there. Will continue that in 2015, for sure.

With Dave Hollembaek, we started an “informal gathering” / Stammtisch about Enterprise Search (we are not limited to SharePoint, but focused) in Munich – if you wanna join, please feel free!

More conferences are on my roadmap for 2015! 

Blogs & Comments

I spent some time with my little blog this year – new theme with mobile support and 31 blog posts – on the statistics side (I love analytics!) I had 78,783 views on my blog (82% more than 2013) and 133 comments (29% more than 2013) with 103 blog posts in total.


Posting Patterns 2014 - Ipost on Wednesdays

Posting Patterns 2014 – Ipost on Wednesdays

My most viewed posts:

1. #SharePoint 2013 – Create a Search Service Application and Search Topology with Powershell 53 comments 14,409 views
2. Install Office Web Apps 2013 for SharePoint 2013 – with PDF Preview 53 comments 12,536 views
3. Cache cluster is down, restart the cache cluster and Retry 11 comments 6,665 views
4. Installing SharePoint 2013 SP1 with SQL Server 2014 on Windows Server 2012 R2 – Picture Love Story 6 comments 6,465 views
5. Powershell: List all IIS WebApplications (.net version, state, identity) 19 comments 5,797 views

4. was published in 2014 – the others are older posts.

Where do all the visitors come from?

USAUnited States 22,490 views
GermanyGermany 6,237 views
IndiaIndia 5,177 views
UKUnited Kingdom 4,789 views
CanadaCanada 3,342 views

In total my blog was visited from 176 countries.

Search Projects

When I look at my projects of 2014 most of them were somehow SharePoint Search-related. My clients are obviously the best – so they gave my some pretty nice challenges, mostly integration of SAP or third party systems – or indexing huge volumns (90,000,000 items in 2 weeks). Thanks for the continued trust!

SharePoint Certifications

I only managed to pass two Certifications last year – but a new years resolution is to finish both my MCSE (only 70-332 missing!!!) and MCSD fpr SharePoint 2013. I tried to register for a training, but I was greated with:

After December 31st, 2014 if you wish to take a Microsoft certification exam, please visit the Microsoft Learning web site for further information.

So I tried to scheduled an online proctored exam – but Germany is not on the list… so I scheduled a on-site exam beginning of January. Yay!


  • 2015 will be the year of search! Again.

For 2014 I predicted that the Service Pack 1 for SharePoint 2013 will bring some major search enhancements – some under the hood features were added, but I am still waiting for the long “announced” push API. I hope to see Delve for on-prem installations, too – but I think that will stay a wish. On the other side, that will hopefully push some to the cloud – or hybrid at least.

  • Hosting on Azure for Dev/Test will even more accelerate – even in Germany.

I said the same almost last year. But I saw some changes, some new clients moved directly to Office 365 – some at least looked at it. Cloud is still not very popular in Germany, in 2015 I hopefully see some more traction.

Happy New Year!

I wish you all a healthy and wealthy Happy New Year. Hope to see you soon!

SharePoint Search: What Managed Properties do my Items have? (Or: Search Query Tool v2.2. – the missing manual)

01 Jan
January 1, 2015

Creating Search Driven Applications is very easy with SharePoint 2013 – sometimes.

For a really simple example you need 6 steps for SharePoint content (read detailed article here)

  1. Add some columns with values to a list
  2. Full crawl
  3. Create a Managed Property with the corresponding mapping
  4. Full Crawl
  5. Drop a Search WebPart (Search Result WebPart, Content By Search or one of the other options)
  6. Create a query that fits your needs

Bam! Easy, performant solution* and almost maintenance free (*depends on your query).

So? Where is/are the Problem(s)?

Most of the time its a challenge to create the search query (read this: Search Queries explained, 12 parts!) – for the most queries you need to understand what properties you have and can use to answer the business needs. For that you always have the same challenges:

  1. Are the items in the index already?
  2. Can you see it / who can see it?
  3. What properties are available for those items?
  4. Are the properties available on all items?
  5. How often do the items change?
  6. Ranking? Why is that item higher ranked?
  7. What should be visualized and how?
  8. Does my query work (at all)?

And of course many more.

So how to tackle some/all of the problems? Search Query Tool to the rescue!

First of all, there is one tool that gives you a head start: Search Query Tool for SharePoint 2013 (free, codeplex). It is so nice, I had to contribute some time and love, too.

In the following I will show some of the main features of it that help me almost daily:


Search Query Tool 2.2

Search Query Tool 2.2

  1. REST URL with all selected parameters – awesome to create JavaScript apps or apps outside of SharePoint
  2. Query Options – so many and really interesting options. You don’t want Phonetic Queries? There are plenty of different options here, almost all have a descriptive tooltip. Some of the fields have a tiny box right to a textbox, click on it for some useful default values. Quite handy if you don’t know how to put values in.
  3. Result Tabs – you get all the results here:
    1. Status: How many results (with and without duplicates) where delivered in what time.
    2. Raw: Unformatted JSON results (ugly, most of the time I skip that tab) – response headers are sometimes interesting in the case of an error.
    3. Primary Results: 99% of the time I am in this tab.

      Primary Results - shows the standard and selected properties

      Primary Results – shows the default or the Select Properties

    4. Refinement Results: What refiners do you get with the current query. You have to use the Refiners box in the Query Options (2) to see some results.

      What refiners would you get for the current query?

      What refiners would you get for the current query?

    5. Secondary Results: Skipping that most of the times, kinda useless IMHO.
    6. Suggestion Results: You have to use a suggestion query for that.
      Share suggests SharePoint

      Share suggests SharePoint


    7. Debug – I cover that in a future blog post.
  4. All connection options. Very useful to test search with different accounts. You can save the connection information via the menu File >> Save Connection Properties.
  5. The advanced options. I always enable the experimental features!
    1. They enable a preview in the tool for supported files (office and web pages)

      Preview for supported files (OWA is a requirement)

      Preview for supported files (OWA is a requirement)

    2. And most importantly it adds the button that I use most of the time: View all Properties
      The "magic" view all properties link

      The “magic” view all properties link

      The dialog showing all available properties

      The dialog showing all available properties

      The dialog shows all Managed Properties of the selected it. For that it needs the Property Workid, so make sure its in the Select Properties field (otherwise the tool will yell at you!).
      There are differences for SharePoint Online – maybe they will patch it so on-prem faces the same challenge. But once again Mikael Svenson has a solution for that.

Final words

Quite a lot of features for a FREE tool – but for Search challenges it’s really essential. Did I notice it has a freshness boost generator? Or did I mention that it supports the Office Graph Language (GQL), too?

Can you solve all problems with it? I hope so – if not, please drop me a comment or directly on the discussion page.

A to Z: Create a Display Template for SharePoint 2013 Search to visualize a property of an item/document

31 Dec
December 31, 2014


Display Templates can be used in Web Parts to individualize search results (see also Introduction to Display Templates on MSDN).

In this article I will show you how a Display Template for SharePoint 2013 Search is structured, why you need them and how to customize/extend them.

Why YOU need Display Templates?

Not every search hit is equal – even a small icon helps the user to identify content more quickly than if only a monotonous mass of blue links is represented. And now that more and more content is stored in SharePoint, this is even more relevant. And integration solutions that can display many content of external system can be realized, too.

The rapid acquisition of information also plays a major role in user acceptance – if it additionally looks good and the system helps me to recognize information faster – well, search can rock – I use it daily in our own Intranet! In addition, the important content should be categorized and tagged in SharePoint – such additional work should also be visible in search results, so that the effort is also worth even more.

How is a Display Template structured?

Each display template consists of two files: a HTML version of the Display Template that you can edit in a HTML editor, and a JS file that SharePoint uses (dont touch that one!). The HTML file is structured always with the same pattern, in it is defined what property/metadata of the search hit will be shown. The HTML file can also contain JavaScript to dynamically display content or even implement business logic.

The following screenshot shows a typical SharePoint 2013 Search Center, with the 3 components that can be easily adapted: Item Display Template, Item Hover display template and the action bar.

Search Result for PowerPoint hit

Search Result for PowerPoint hit

(1) Item Display Template for a PowerPoint hit
(2) The corresponding Hover Template for PowerPoint
(3) Search Actions that can be directly started from within the search result

How can you change a Display Template?

In the following 5 steps I will show you how to customize the Display Template for PowerPoint so that an additional document property is displayed.

Step 1: Prepare List or Library

To view an additional document property, it must be available on at least one document / list item. For this simple example, I created a text column “technologie” and filled it with values:

Create a column and add at least one value!

Create a column and add at least one value!


Creation of the column is not enough, it must also be filled with at least one value

Step 2: Creating a Managed Property and full crawl

How can this additional value be used in SharePoint Search now? Automatically? Nope, unless its a site colum that you added to the list (see here). Otherwise a so-called Managed Property  must be created now. To do this, go to Site Collection Administration – in SharePoint 2010 you had to go to Central Admin and had to be a farm administrator so this is a big step forward – and there then click on Search Schema. In the tab Managed Properties create a new property:

Create a Managed Property

Create a Managed Property

In the dialog create a property with a name, here its “Technologie”. Select retrievable and add at the very end of the page a mapping. The crawled property is called ows_technologie, just add it to the mapping:

Search for the new column name


The name of the crawled property for SharePoint content is always prefixed with “ows_”

If no value is displayed, the SharePoint search has not found the new column during a crawl. If you want to speed things up (its not like you have lots of choices here), go to Central Administration and start the crawl there. Once the crawled property is displayed, add it, and save the Managed Property with OK. After this the summary page for technology should look like this:

Summary page for the new managed property


The Managed Property will be filled during the next full crawl – if you do not wanna wait, run a full crawl (again) in Central Administration.

Step 3: Customized the Display Template

Now to the actual customization of the Display Templates – the technology property is not automatically displayed. To modify a Display Template, download the display template from Master Page Gallery. For this change to the Site Collection Settings, open Master Page Gallery and then change to the folder “Display Templates” and then “Search”. This folder contains all display templates that can be used for the SharePoint search – as already described, an HTML file for editing and a JavaScript file that SharePoint generates every time the html file changes. For this example we will download the PowerPoint design template (item_PowerPoint.html) and open it with a text editor (eg. Notepad++). The content is a mixture of normal HTML format and Javascript – plus a placeholder language that can be used to display values from the search index.

In our case, just a few modifications are necessary. First adjust the title in the title tag so that we will find the display template later: SPAdvent PowerPoint item.
Then add to the html tag mso:ManagedPropertyMapping: ,’Technologie':’Technologie’ – the coma at the beginning should not be missed. With this we tell SharePoint search that we need for this template display the value of technology and this is to be loaded with the search results.

As last step create a new line after line 38 and insert the following text:

Technologie: _#=ctx.CurrentItem.Technologie=#_

The file should look like this now:

Modifications of the Display Template

Modifications of the Display Template

With this new HTML tag, a new row is displayed in the display template – the ctx.CurrentItem.Technologie will be replaced with the value from the search index, in this example our technology value.

Now save the file under a new name (don’t modify the existing ones!) and upload it back to the design templates library – in the same folder from which we have downloaded it. Simply close the dialog with OK after uploading.

Step 4: Creating the Result Type

In order to tell SharePoint search that it should use our new Display Template for PowerPoint now, we need a new search result type. Go to the site settings and there to search result types. There we add a new one with the name SPAdvent PPT. The only condition we add is that we restrict it to type Microsoft PowerPoint. Finally, we choose the action – the result shall be represented by the SPAdvent PowerPoint display template:

New Result Type for PowerPoint hits

New Result Type for PowerPoint hits

Save it and then the search results type should look like this:

The summary page for the new result type

The summary page for the new result type

Step 5: Look at the result

So that’s it – the new Display Template is ready and will be used on the search page:

Final result with the new technologie value

Final result with the new technologie value

Although there are relatively many steps, still no “real” development or any special development languages are needed. Changes can be made quickly and easily implemented.

This simple example shows how powerful display templates can be and how quickly they can bring added value to the end user.

Further readings:

He’s dead, Jim: SharePoint 2013 Search Troubleshooting

13 Oct
October 13, 2014

Recently we had some strange search issues in our very own intranet and its time to blog about some SharePoint 2013 Search Troubleshooting.

At first I noticed that our Search results were quite old, usually that is not the case because we enabled continuous crawling and I was surprised that it worked that well. How I noticed? I have tiny app that utilized search and supports our core business process: billing. But it suddenly stopped finding new items.

Issue 1: Broken web.config on our search server

As mentioned above I noticed that new items in our SharePoint Environment did not show up – you can quickly confirm that when you go to your search center and search for *. Then check the Last Modified Refiner and as you can see below: we had a problem.


Last Modified refiner – with zero results for today

But once you identified the problem you can tackle it. In this case I told our internal IT that there is something wrong, we checked the crawl log: ~560.000 errors.

That this was not monitored is a different story – but the error I saw that was quite familiar: “Search error: crawler could not connect to the repository”. That means either the SharePoint is not running, there is a firewall in between – or as in our case it returned HTTP 500 errors for every request. I could easily confirm that by opening a browser on the SharePoint crawler itself and open the target site. I looked at our IT admin: “Did you change something?” –  he: “We deployed a feature and that did not work out that well…” – me: “Did you remove it everywhere except the search crawler?” – he nodded. The in this case broken web.config was quickly restored – started a full crawl to get rid of the many errors and we should be fine, right? Right?!!

Issue 2: Cannot plan query for index system

Because full crawls tend to take quite a while – even with like 100 dp/s – I checked the search results the next morning:


That did not work – every search query resulted in that error. The important part is the correlation ID

Ouch – what happened? I right away called our admin: “What did you do?” – he said  “I just changed the password of the farm account via central admin like I did several times” – mhh, that should work, nothing to blame here. So I asked him to send me the uls logs and some screens of the SharePoint Search Service Application:


Full crawl seems to work – a bit slower than it should tho

Crawl logs do not look too suspiciously.


Event log #1


Huston – we have a problem!

Event log – useful as ever to identify that you have a problem but sometimes it does not help you identify what problem you have… so I was keen to see the ULS entry for the correlation ID I saw during the search – so I got it:

SearchServiceApplication::Execute–Exception: Microsoft.SharePoint.SPException: Tried IMS endpoints for operation Execute: Cannot plan query for index system SP2f28f6df0f54. Index fragment ‘1’ has no available cells. Cell statuses: [Cell I.0.1 on node IndexComponent3: Cell status is set to ‘not available’ (cell out of sync or seeding)] at Microsoft.Office.Server.Search.Query.Ims.LoadBalancer.RoundRobinLoadBalancerContext.NextEndpoint(String operationName, String failMessage) at Microsoft.Office.Server.Search.Administration.SearchServiceApplication._ImsQueryInternalType.DoSpLoadBalancedImsOp[T](ImsBackedOperation`1 imsCall, Int32 timeoutInMilliseconds, Int32 wcfTimeoutInMilliseconds, String operationName) at Microsoft.Office.Server.Search.Administration.SearchServiceApplication…. fe0fc09c-8c99-b017-25c9-d3feac78a369

That sounds not that healthy, especially the Cell status is set to ‘not available’ (cell out of sync or seeding). The index is made out of cells – if they are not there, Search is very unhappy. I can only conclude that changing the password during a full crawl is a bad idea.

So we first tried to restart the SharePoint Search Service – no result.
We restarted the entire box – no result.

So we heavy-heartedly did an index reset and the search results immediately came back – empty but working again. But trust me, resetting the index during a workday, with many search-driven applications around is not the greatest experience you can have – and you shouldn’t. There is room for improvement in our intranet, do you agree?