Overview of shared with Externals and shared Anonymous in Office 365

The GDPR highlights the need for protection of personal data held by organizations. To be able to do this Microsoft inverted a lot in new features and functions like the Office 365 Security & Compliance Center or the GDPR Assessment.

One of the backend systems helping to fulfill those regulations is the SharePoint Online Search Service. In the SharePoint Online Search schema, we can find two managed properties focusing on sharing and access from outside of your organization.

ViewableByExternalUsers and ViewableByAnonymousUsers

Both had the same setting: Query, Retrieve, Refine and Sort. So we can use them to create some reports based on search queries.

Personal overview

Office 365 let every user search in his SharePoint Online sites, OneDrive for Business files and also in Emails for content. In this scenario Email is of topic. But using this search function at the landing page of Office 365 a user can create a personal overview of content he shared to externals or anonymous.

To do this a user needs to fill in the following query in the search box at the Office 365 landing page:

ViewableByAnonymousUsers=true

In this example, I search for documents located in SharePoint Online sites or in my personal OneDrive for Business which are shared based on an anonymous guest link.

Using the query ViewableByExternalUsers=true shows me the files shared with external users through a sharing link that requires them to log in before they can view the file.

This gives a user an overview of documents he has shared from his OneDrive for Business with externals or anonymous. Because the URL is generic you can use this link for all your users and every user get his person overview: https://www.office.com/search?auth=2&home=1&q=ViewableByAnonymousUsers%3Dtrue

Also you can use this link to create a tile in the Office 365 App Launcher as described in the article: Add custom tiles to the app launcher

The result may look like this:

Team Site overview

Microsoft integrated a new out the box reporting capability in every Team Site. The article: View usage data for your SharePoint Online site is showing all details you need to know. There is also a new tab called “Shared externally”.

The article says: List of files you have access to that have been shared with users outside your organization through a sharing link that requires them to log in before they can view the file. Files shared with anonymous users or files available to users with guest permissions are not included.

To get a list of files shared anonymous in this Team Site we can again use the query: ViewableByAnonymousUsers=true followed by a path filter like for example: path:https:\\yourTeamSiteName.sharepoint.com.

Using Search Center to get an overview

As an administrator, you can also use the search center to get an overview of anonymous shared content or about data and also SharePoint Online Sites them self, shared to externals. The queries are basically the same and you can extend them with additional keyword queries properties.

For example, search all Office 366 Groups external users can access:

ViewableByExternalUsers=true contentclass:sts_site WebTemplate:GROUP

(Because of security trimming in SharePoint Search the user who runs the query needs access to all Team Sites to gets an complete report.)

Of cause there are also options archiving this using PowerShell for Office 365 Groups or using Reports in the Office 365 Security & Compliance Center. Using the SharePoint Online search gives you the power and flexibility to integrate all managed properties as metadata in you report like for example ViewsLifeTime, LastModifiedTime, CreatedBy or ModifiedBy. In addition you can easily scope your report to only show documents using the IsDocument=true query parameter or to focus to special Site Templates like WebTemplate:GROUP to only show Office 365 Groups Team Sites etc.

Using PowerShell to get the report

Using PowerShell to get results from SharePoint Online Search also offers the option to save the report as an *.csv file. To call SharePoint Online Search API using PowerShell and save the result to an *.csv file you can follow the steps explained by Prasham Sabadra in his article Office 365/Sharepoint Online - PowerShell Script To Call Search API And Get The Result.

This example is based on his description. The report is showing all external shared content and sites in an Office 365 Tenant and is saving the result to C:\Temp\ViewableByExternalUsers.csv

# add references to SharePoint client assemblies and authenticate to Office 365 site - required for CSOM   

Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"   

Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"   

Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Search.dll"

#Specify tenant admin and URL 

$User = "Admin@yourTenant.onmicrosoft.com"   

#Configure Site URL and User 

$SiteURL = "https://yourTenant.sharepoint.com"  

#Password 

$Password ="yourPassword"   

$securePassword = ConvertTo-SecureString -String $Password -AsPlainText –Force  

$Creds = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($User,$securePassword)

#client context object and setting the credentials  

$Context = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL) 

$Context.Credentials = $Creds

#Calling Search API - Create the instance of KeywordQuery and set the properties 

$keywordQuery = New-Object Microsoft.SharePoint.Client.Search.Query.KeywordQuery($Context)  

#Sample Query - To get the last year result 

$queryText="ViewableByExternalUsers=true" 

$keywordQuery.QueryText = $queryText 

$keywordQuery.TrimDuplicates=$false 

$keywordQuery.SelectProperties.Add("LastModifiedTime") 

$keywordQuery.SelectProperties.Add("ViewsLifeTime") 

$keywordQuery.SelectProperties.Add("ModifiedBy") 

$keywordQuery.SelectProperties.Add("ViewsLifeTimeUniqueUsers") 

$keywordQuery.SelectProperties.Add("Created") 

$keywordQuery.SelectProperties.Add("CreatedBy") 

$keywordQuery.SortList.Add("ViewsLifeTime","Asc")

#Search API - Create the instance of SearchExecutor and get the result 

$searchExecutor = New-Object Microsoft.SharePoint.Client.Search.Query.SearchExecutor($Context) 

$results = $searchExecutor.ExecuteQuery($keywordQuery) 

$Context.ExecuteQuery() 

#Result Count 

Write-Host $results.Value[0].ResultRows.Count

#CSV file location, to store the result 

$exportlocation = "C:\Temp\ViewableByExternalUsers.csv" 

foreach($result in $results.Value[0].ResultRows) 

{ 

$outputline='"'+$result["Title"]+'"'+","+'"'+$result["Path"]+'"'+","+$result["ViewsLifeTime"]+","+$result["ViewsLifeTimeUniqueUsers"]+","+$result["CreatedBy"]+","+$result["Created"]+","+$result["ModifiedBy"]+","+$result["LastModifiedTime"] 

Add-Content $exportlocation $outputline  

}

Search Driven Applications with Office 365 / SharePoint Online - Part II

Part II: Deeper look what kind of properties and workarounds can be used within Office 365 / SharePoint Online to aggregate information based on the search engine.

As we see in Part I we have to use some tricks to build Search Driven Solution in Office 365 / SharePoint Online. Based on the content and the solutions in Part I we can build a Metadata & Taggs based Content-Browser. The idea behind is using a fixed query showing all the content in the index and then use the Tags & Metadata Refiner to filter and navigate.

The fixed query to show all the content is simply that one: %% or you use a fake query like that one:(IsDocument="True") OR (IsDocument="False") both show anything in the index.

Next Step is configuring the Refiner Panel to only show the Tags & Metadata. To do this we have to delete all the other refiner entries in the undelaying XML. (Webpart settings of the refiner webpart -> Refinement -> Filter Category Definition)

The result looks like this:

<FilterCategories>

<Category Title="Managed Metadata Columns" Description="Managed metadata of the documents" Type="Microsoft.Office.Server.Search.WebControls.TaxonomyFilterGenerator" MetadataThreshold="20" NumberOfFiltersToDisplay="20" MaxNumberOfFilters="20" ShowMoreLink="True" MappedProperty="ows_MetadataFacetInfo" MoreLinkText="show more" LessLinkText="show fewer" ShowCounts="Count"/>

<Category Title="Tags" Description="All managed metadata of the documents and social tags" Type="Microsoft.Office.Server.Search.WebControls.TaxonomyFilterGenerator" MetadataThreshold="20" NumberOfFiltersToDisplay="20" MaxNumberOfFilters="20" ShowMoreLink="True" MappedProperty="ows_MetadataFacetInfo,popularsocialtags" MoreLinkText="show more" LessLinkText="show fewer" ShowCounts="Count"/>

</FilterCategories>

You can see, that I raised the values for MetadataThreshold, NumberOfFiltersToDisplay MaxNumberOfFilters up to 20 and ad the ShowCounts element to the XML.

And here the result:

This is of cause not looking really nice. But it shows which data is available out of the box. For example: Based on the property ows_MetadataFacetInfo and popularsocialtags in combination with the property Count you can easy develop a TagCloud Webpart.

Necessary points here are: using this to excessive will falsify you search ranking etc. Another problem is the performance of this solution. By default only the first 50 items in the index are used to generate the entries in the Refiner (Webpart settings of the refiner webpart -> Refinement -> Accuracy Index). Setting this value up to high you get a poor performance during loading the side. Is the value to low the risk is higher that some Tags ore Managed Metadata values are missing. Before we come to a new point lets me says that this is not really a SearchDriven Solution because what we do is searching for all in the index and then filter it, but for the end-user this wouldn’t make an difference ;-)

The main focus is almost to build solution which are user-friendly and which do not raise up the doings for users. So let’s now have a look at useful properties which are created by the system itself.

As we see in the XML of the Refiner there are two interesting search property: ows_MetadataFacetInfo and popularsocialtags. We can combine those properties to build SearchDriven Applications based on a SearchQueryString as described here: LINK (on this site you also can find a C# code sample for using this stuff developing own WebParts)

For example I will have a SearchQueryString (for my environment of cause) showing me all content tagged with “Tag2” and the taxonomy filed “Region” is “Bern”

https://..../NB/Search/results.aspx?k=%25%25&r=%22owstaxIdRegion%22%3D%23511b3749%2D7ca3%2D4011%2D90d0%2Df88a8bc5fb50%3A%22Bern%22%20socialtagId%3D25dee478%2Df426%2D4dc1%2D9d25%2D340e9ecce093

Let’s disassemble this:

Query fragment	Clear text	meaning
k=%25%25	%%	Search for all in the index
r=%22owstaxIdRegion%22%	R=”owstaxIdRegion”	The managed property the system created from the taxonomy field
%20socialtagId	%blank%socialtagID	The given tag “Tag2”

(SharePoint creates for every Managed Metadata filed crawled properties and managed properties as described here: LINK)

You can use this SearchQueryString now in a SearchResult Webpart or as a fixed link to provide information coming from all crawled content in your Office 365 / SharePoint Online filtered on: “Tag2” is given and Region is “Bern”.

This technique also works similar in the new Office 365 / SharePoint 2013 preview version. The SearchQueryString syntax will be a litte bit diferent, but the idea behind is similar. Of cause with the new versions of Office 365 and SharePoint we have much more options and opportunities. This will be part of the next session. ;-)

In addition there are some very interesting 3th party tools for navigation and aggregating content based on Metadata which also work with Office 365 / SharePoint Online. For Example this one: http://www.metaengine.com/sptermcloud

For me the main handicap on this solution is that it only works with taxonomy fields. If you want to work with external data coming from BCS sources, Azure etc. you need to use the tagging feature because Managed Metadata isn’t available for external data.

Next part of the series we will have a look at SearchDriven Applications with the new Office 365 / SharePoint 2013 preview version…

Webcast with hands on system demos:

Eindeutige custom ID in einer SharePoint Liste generieren

Aus aktuellem, projektbezogenen ;-) , Anlass musste ich mich (mal wieder) mit dem Thema „eindeutige Ticketnummer in einer SharePoint Liste generieren“ beschäftigen. Gut, man könnte einfach die vom System erzeugte ID nehmen. Der Hauptgrund warum das ausscheidet ist, dass hier keine Ergänzungen, Anpassungen etc. möglich sind.

Die TicketID soll sich wie folgt zusammensetze:  

%Test%|eindeutige Nummer|%Text%

also z.B.: SP12896654DE

Es existieren diverse Lösungsansätze dazu im Netz. Meiner ist das „Create Datum“ zu verwenden und daraus eine eindeutige ID zusammenzusetzte. Die nachfolgende Formel generiert eine ID die auf Sekundenbasis eindeutig ist:

="SP"&TEXT(Created,"yy")&STUNDE(Created)&TAG(Created)&MONAT(Created)&MINUTE(Created)&SEKUNDE(Created)&"DE"

Vorgehen:

Eine neue Spalte anlegen. Der Typ der Spalte muss „Calculated“ sein, Name vergeben, Formel einfügen / anpassen und fertig is.

Show up all blog entries from different sites at one place

Show up all blog entries from different sites at one place

Last week at the ShareConf I meet the SPUG community member Dennis de Vries. He told me from a solution based on Search Driven Ideas that he builds. The scope was aggregating all blogs from different SharePoint sites bases on a Search Driven Solution. Here is his post:

http://www.ilikesharepoint.de/2012/06/sharepoint-2010-search-show-up-all-blog-entries-from-different-sites-at-one-place/

SQL Server 2012 Filestream, Filetable and the added value with SharePoint 2010

I wrote about the new SQL Server 2012 Filetable feature based on Filestream, which was already been coming with the last version SQL Server 2008, before (see here: LINK ). Now let’s see Filetable together with SharePoint in action.

First we have to activate Filetable which is not been active per default after installing SQL Server 2012. How to activate this can be found here: LINK .

My settings are:

-          There is a database named “FileStreamDemo”.

-          In the “options” menu you can see that there is a Filestream directory named  “FileStreamData”

-          There is a table called “dbo.FileTableData” which points to the FileStreamData directory:

CREATE TABLE [dbo].[FileTableData] AS FILETABLE ON [PRIMARY] FILESTREAM_ON [FileStreamDemoGroup1]

WITH

(

FILETABLE_DIRECTORY = N'FileTableData', FILETABLE_COLLATE_FILENAME = SQL_Latin1_General_CP1_CI_AS

)

-          NON_TRANSACTED_ACCESS = FULL, that allowed us interact with the data in the filetable directory bypassing the SQL Server API and use the windows explorer direct.

-          The script in the background shows the create script for the database. We can see that the database files *.mdf and *.ldf are placed in a different directory than the Filestream data.

I placed several DEMO files in the Filestram directory:

If we now query the table “dbo.FileTableData” the result is:

The columns are fixed and base on the propertys coming from the NTFS file system:

Column Name	Data Type
stream_id	uniqueidentifier ROWGUIDCOL
file_stream	varbinary(max) FILESTREAM
name	nvarchar(255)
path_locator	hierarchyid
creation_time	datetimeoffset(7)
last_write_time	datetimeoffset(7)
last_access_time	datetimeoffset(7)
is_directory	bit
is_offline	bit
is_hidden	bit
is_readonly	bit
is_archive	bit
is_system	bit
is_temporary	bit

Each FileTable includes also these computed (read-only) columns:

Column Name	Data Type
parent_path_locator	hierarchyid
file_type	nvarchar(255)
cached_file_size	bigint

So we see that the content from FileStream directory is returned as BLOB data placed in the column “file_stream” as varbinary(max). By the way: the data placed in here is automatic part of the backup and restored process.

If we now use BCS to connect SharePoint to this database table we have two issues:

1.       We are not able to bring the content directly to the user without writing code that extracts / transforms the BLOB data.

2.       Using SharePoint to unwrap the BLOB data generates load on our SharePoint. The data must be load from SQL Server / Filesystem to SharePoint and from the SharePoint Server to the Client.

These two issues will have the same answer:

Let’s point to the data in the file-system using the given information. That means only the metinformation where the file is placed is transferred and the client picks up the data directly from where it is stored.

-          In scenario 1 the BLOB data is transferred via SharePoint.

-          Scenario 2 shows the way only the metinformation (blue arrows) is transferred via SharePoint and the user access the content directly.

Scenario 2 is what I will show now. The metinformation where the data is located is not part of the data in the “dbo.FileTableData” table. Also there are some columns we do not need to transfer via BCS to our SharePoint. So let’s write a SQL query which is given as the needed and filtered information:

1.  SELECT

2.  stream_id,

3.  name,

4.  file_type,

5.  cached_file_size,

6.  creation_time,

7.  last_write_time,

8.  last_access_time,

9.  is_directory,

10. 'file:\\DEMO2010A\FileStream\FileStreamData\FileTableData\' + name AS Link

11. FROM dbo.FileTableData

The line 10 brings the “magic” within this. This T-SQL call generates for every entry in the “dbo.FileTableData” table a link to the original data in the Filesystem. This link is attached as a new column called “Link”:

This query is now used to create a view called “FileTableView”.

Next step is now connection this view via BCS to SharePoint. I do this using SharePoint Designer to create a new External Content Type. How to create External Content Types and handling BCS is not part of this post. If you need help within this just contact me or have a look here: LINK .

So here is my solution:

As you can see I created an External Content Source called “FileTableData” which is based on the view “FileTableView”. For this use case I only need a ReadList and a ReadItem operation.

Publishing this External Content Type to a SharePoint External List (and of course doing some XSL customizing) generates a list looking like this which shows exactly the content of the FileStream directory:

Clicking the “Link” given to each entry opens the document / folder. The access happens as shown in PIC 4 directly to where the content is stored.

Resume:

This is an interesting way bringing data into a SharePoint environment without blowing up the content databases of SharePoint. This data can also be protected with SQL Server backup and recovery tools. But of course there a several 3th Party Tools doing this ore similar thing with more features around. Anyway, for me it’s a create feature and an additional option in data management.

See this post as webcaste here: