Enumerating SharePoint Sites for Office 365 Groups With PowerShell

On 11/8/16 Microsoft made true of announcements from August 2016 and announced that Office 365 Groups with connected SharePoint Online team were now available. Link – https://blogs.office.com/2016/11/08/create-connected-sharepoint-online-team-sites-in-seconds/

The SharePoint admin inside me began to think about managing all of these new site collections/cluttering up my SharePoint Admin Center! The good news (Spoiler Alert)…It doesn’t! One of my friends (Brian Kinsella – https://www.linkedin.com/in/briankinsella) had the same question, so I decided to dig in to figure out how to get at all of these new site collections.

Most of you with an active Office 365/SharePoint Online deployment are probably familiar with the SharePoint Admin center:

SPAdminCenterPNG

I went ahead and created a new Office 365 Group called “TestO365Groups”

To do this I did the following:

  1. Go into the Mail App > (Under Groups) Clicked the + button
  2. Fill out the appropriate information (Name, Description, Privacy, etc.)
  3. This provisioned a site collection at following URL (I checked this by clicking the Files tab within the group) –  https://mod604710.sharepoint.com/sites/testo365groups/

Now onto the concerns above!

I was not able to see this site collection in the SharePoint Admin page and I was not able access Office 365 Groups via SharePoint Online PowerShell cmdlets (Get-SPOSite)

sposites

Exchange PowerShell to the rescue! Here’s what you can do to find all those Office 365 group URL’s:

  1. Fire up Exchange Online PowerShell – https://technet.microsoft.com/en-us/library/jj984289(v=exchg.160).aspx
  2. Open PowerShell as Admin and connect to the O365 tenant using the following PowerShell:
Run the following command to get each Office 365 group and its associated SharePoint site URL:
Check it out (There’s a lot of properties you can work into this script if you’d like..109 to be exact):

o365groupsposh

-AJB

SharePoint 2013/2016 Cloud Hybrid Search Service Application

I ran through the setup of the new SharePoint 2013/2016 Cloud Hybrid Search Service Application and wrote about it on the Skyline blog! Definitely an exciting new service and the best hybrid search experience to date..

http://www.skylinetechnologies.com/Insights/Skyline-Blog/October-2015/SharePoint-Cloud-Hybrid-Search-Service-Application

SharePoint in Azure – SendGrid Configuration

Recently setup outbound SMTP in an Azure SharePoint farm. There is great documentation (https://azure.microsoft.com/en-us/documentation/articles/sendgrid-dotnet-how-to-send-email/) out there already, but figured I’d share my experiences with it.

Included in Azure is a cloud-based email service called SendGrid. You get 25,000 email credits free a month. Here’s the rest of the pricing: https://sendgrid.com/windowsazure.html

Once this service is created in your environment (Click the first link to get the walkthrough) you will be assigned a username/password for SMTP relay. Make note of this since you’ll be using it later on.

After the SendGrid service is spun up you still need a way for SharePoint to use this. Pointing the outbound email configuration at smtp.sendgrid.com will not work because SendGrid requires a username/password. Good thing SendGrid has some good documentation too: https://sendgrid.com/docs/Integrate/Mail_Servers/iis75.html

After configuring the SMTP Server feature on the SharePoint server (You could use a separate server for relay, but this was dev so I was playing) I tested first with Telnet (Using the example in the SendGrid documentation) and then with PowerShell to verify everything was working in SharePoint.

The documentation is good so follow that, but I did run into 2 items that weren’t discussed:

  1. Add your domain as an alias domain in SMTP
  2. The IP Address of 127.0.0.1 does not work
    • In SMTP > Right Click the SMTP Virtual Server # 1> Properties > Access > Relay Restrictions
    • Click the Relay button and note that 127.0.0.1 is added (Per SendGrid instructions). This needs to be switched to the IP Address of the Azure server

Here’s the PowerShell example:

 

 

SharePoint/Azure ACS Token Signing Certificate. Will you please just sign my tokens?!

Setting up Azure ACS was fun. It’s so easy to get it up/running/connected to SharePoint and you have the instant satisfaction of using Microsoft/Google/Facebook accounts to login to SharePoint. Great success! Note: Microsoft only gives you the UPN claim..which is a unique ID so when users log in it looks gross. Google and Facebook are able to pull in a lot more claims..but Microsoft is more secure in that fashion I suppose.

Anyways there is great documentation out there already on how to get rocking and rolling. Here’s a few I’ve used:

Anyways there isn’t really much documentation out there on the Token Signing Certificate. Most of the documentation out there states to use a self-signed certificate for DEV and get a certificate from a Commercial Certificate Authority for PROD. Alrighty then. Here’s the screen in Azure

ACSTokenSigningCertPage

Not knowing too much in the ADFS token signing cert space (In the past most environments I have worked with use ADCS or PKI to generate these)  I took to the interwebs.

The reason I was researching is because if I were to put in a CSR for acstenant.accesscontrol.windows.net I wouldn’t get it or it would get revoked…I don’t own windows.net. Companies like Comodo have a DCV (Domain Control Validation) questionnaire built right into the certificate purchasing process. For the self-signed cert you can use whatever you want.

I researched to see if Azure ACS could have a friendly name or DNS CName that we could pull the cert for. NOOPE!

http://stackoverflow.com/questions/16589648/can-i-have-a-friendly-name-in-an-acs-service-namespace

I found a great tool by Steve Peschka that allows you to actually export the token signing certificate right out of ACS. The ACS tenant is actually already an HTTPS site so there is a preexisting cert. SWEEET! It works like a charm too..

https://samlman.wordpress.com/2015/03/02/tool-to-get-token-signing-certificate-out-of-acs/

This specific client had their heart set on using the commercial certificate authority so I kept trucking.

The certificate for ACS is described in detail here: https://msdn.microsoft.com/en-us/library/gg185932.aspx

Alright I’m still not sure what subject name to use..until I found this forum post: https://social.msdn.microsoft.com/Forums/vstudio/en-US/0dc942cd-ced1-4d09-9f10-73e325c241a9/adfs-installation-and-token-signning-certficate?forum=Geneva

Frank Lesniak had the answer I was looking for (This was for ADFS, but still applied to ACS):

**I’m just copying his answer in here in case the forum post ever gets deleted

  1. The certificate’s key length should be at least 2048 bits.
  2. Validity period should be as long as possible (given cost), up to 5 years
  3. The signing algorithm should be either SHA-1 or SHA-256. If you need to support ADFS 1.x legacy federation, Windows 2000, Windows XP SP2, or Windows Server 2003, use SHA-1. Otherwise, for best security, use SHA-256. You may need to call your publically-trusted certificate issuer to validate the signing algorithm.
  4. Ensure that the private key is exportable
  5. Subject name does not matter… but something like adfstoken.yourdomainname.com would be a common implementation.
  6. Key usage does not matter.

The key points being #5 and #6 – ADFS does not care what you name the certificate or what kind of certificate is being used (i.e. code signing, server authentication, client authentication, etc.). My advice would be to generate a certificate however you’d normally feel comfortable doing so. For example, many of my clients use IIS to generate the certificate signing request (CSR), then submit the CSR to the commercial CA. Once you’ve loaded the certificate into the computer store, it should be available for AD FS to use.

 

In summary – It doesn’t matter! Use acstoken.yourdomain.com or if you’re already rocking a wildcard cert for everything use that. Any X.509 certificate will do…

Leaving a Yammer Network

I recently decided to leave my first Yammer network…I joined too many networks to keep up with so something had to give. It took me a while to find the “Leave Network” option so I figured I’d post it out there for you/me to reference in the future 🙂 Pretty simple stuff, but I couldn’t find a blog or article that was high in Bing search results

  1. Click the Ellipses (. . .)
  2. Click Edit Profile
  3. Click Networks
  4. Click Leave Network
  5. Wave Goodbye

 

image

-AJB

Office 365/SharePoint Online – Getting Around the List View Threshold Error

I recently ran into a scenario where an Office 365 (SharePoint Online) list was over 10,000 items and I was attempting to delete the site, only to run into the dreaded List View Threshold Hold error. There are a lot of easy ways around this on premise to get rid of this site/list such as using PowerShell, changing the LVT for administrators, turning off LVT for this list, and many more creative/fun ideas. Office 365 doesn’t really have any of these options. The best way for handling this type of scenario that I’ve found is to use a little Access magic! Fire up Access and do the following:

1. Create a new Blank Desktop Database

2. Click External Data | More (Under Import & Link) | SharePoint List

3. Type the name of the SharePoint Online site you are connecting to and select Link to the data source by creating a linked table

4. clip_image002

5. Now sign in to your Office 365 tenant and click Next. Select the list(s) you want to link to and click OK

6. clip_image004

7. Open up the table containing the SharePoint list and create a query to run against it (Click Query Design | SQL View):

8. You will get a prompt indication that the query is running against data in linked tables. Click Yes

9. You will get another prompt indicating that you are able to delete X Number of rows in the specified table. Again, Click Yes

Now check the SharePoint site and notice that it is 10 rows lighter! Change the number in the SQL query to adjust how many items you want to delete at one time. The most I’ve deleted at once is 5000