Use PowerShell to get site storage usage

One of the challenges for IT Pros when managing the online collaboration world of SharePoint Online and OneDrive for Business in Microsoft 365, is getting a quick overview of things like storage usage across their environment. In the gold ol’ days you’d just using Windows Explorer or something similar, but in the Microsoft 365 world these tools are not ‘online’ aware.

Thankfully, it is PowerShell to the rescue here again! What you can do is is basically grab all the SharePoint and OneDrive for Business Sites and look at a property called StorageUsageCurrent. Thus,

$sposites=get-sposite
$sposites.storageusagecurrent

will firstly get all the SharePoint sites and then display the storage usage of each in MB. However, that is a bit basic. What you really want is something like this:

for you SharePoint sites and and a separate group for your OneDrive for Business sites like this:

You might also notice that they are also sorted in descending order, from largest to smallest.

The good news is that I have done all that hard work for you and made the script available in my GitHub repo here:

https://github.com/directorcia/Office365/blob/master/o365-spo-getusage.ps1

The only thing that you need to do before running the script is to connect to SharePoint Online using PowerShell as an administrator.

Don’t forget there are also plenty of other handy scripts in my GitHub repo which get updated regularly. So, make your admin life easier and use what I have already created rather than re-inventing the wheel.

Key skills for an IT Professional

If you are an IT professional working in with Microsoft 365 then I would suggest the following are the top five skills that you need to have to be successful going forward. My pick, in order is:

1. PowerShell

2. Azure AD

3. Security

4. Intune

5. SharePoint

and here’s why:

PowerShell

PowerShell gives you the ability to script commands for both cloud and on premises Microsoft services. There are many things you can also only do using PowerShell, however more importantly, you can begin to automate what you do. This reduces the time it takes to complete processes as well as giving more consistent results. It also means that you can potentially offload these tasks to others who only need to know how to run the scripts you have created not understand what they entail.

I also find that understanding the PowerShell side of a process gives you a a much deeper understanding of that process and what is possible. I also think that having to do a bit of coding is a benefit to everyone. It helps you to think more logically, plan and structure what you want to achieve. You however don’t need to become a developer, it is easy to CTL-C and CTRL-V good scripts from various places and integrate them into your processes while making a few changes along the way. You can go as deep as you wish and create really amazing scripts that really make life in IT so much easier, while allowing you to do your job faster.

Remember, software will eat the world.

Azure AD

Identity is key to our modern world. You don’t get access to “stuff” until you prove who you are. Importantly, Azure AD is not the same a traditional on premises Active Directory. It is a subset, where the additional options can be added as needed. However, you need a good understanding of where a user’s primary identity is and how it is managed and secured in the cloud. Without this fundamental knowledge you are really going to struggle to understand things like modern device management and security.

All Microsoft services are underpinned by identity and Microsoft cloud services are underpinned by Azure AD. Thus, to administer, configure, troubleshoot these you need a good understanding of Azure AD.

Security

With so much of our assets now being digital, protecting them is paramount. We need to do this in a way that doesn’t inhibit productivity and that is a real challenge. Poor security to me indicates a fundamental lack of knowledge about the products in question. It also demonstrates a lack of discipline and consistency which are the hallmarks of your adversaries out there trying to gain access to systems you protect.

Security will never be an absolute and that makes it hard for many “IT types” to deal with who like to have a tangible end goal. There is not a finite end point with security, there is simply an ongoing challenge to stay one step ahead of the bad actors. Some see that as a burden while the true security professional sees it as a challenge. The protection of our future lies with good security and the challenges that brings. It therefore, will be a skill that will be in continuing high demand.

Intune

As mentioned, Azure AD doesn’t contain the same resources that on premises Active Directory did. The best example of this is probably Group Policy, which is something that Azure AD does not incorporate. To a large extent, that is now handled by Intune and this why it is such an important skill going forward for IT Professionals to become skilled with. It can also be implemented using things like PowerShell, which again goes to the point of how important this list of skills is across all Microsoft services today.

A key factor with Intune is its ability to configure mobile devices. This is something traditionally IT Professionals have not been able to do. However, with the growing numbers of mobile devices in use and their criticality to businesses of every size, it is now more important than ever to be able to easily configure and secure them directly from the Internet.

SharePoint

Most IT Professionals have some skill or familiarity with Exchange and emails which easily translates to services like Exchange Online. However, when it comes to files and folders in the cloud the service of choice is going to be SharePoint, for which there are a decided lack of skills even though SharePoint has been with us for many years now. As I have spoken about many, many time here, SharePoint is more than just simple storage, it is a collaboration system and needs to be approached in that manner to get the most from it. Not doing so results in lots of pain for both administrators and end users.

So there you have it. If I had to pick five skills in order that characterise a modern IT Professional, these would be they. You don’t need to be an elite ninja in each but likewise you can’t remain ignorant of them. if you work with Microsoft cloud technologies you should be familiar and comfortable with them all. If not, then you need to start investing some time and learning them because they will serve you well now and into the future.

Script to disable direct Shared Mailbox logins

A while back I spoke about

The Insecurity of Shared Mailboxes

and how that even though they have an Azure AD Account they should have their logins disabled and access rights ONLY provided via the mailbox.

To make things easier for people I have now created a script that will allow you to view and potentially disable the direct logins for all your shared mailboxes to make them more secure.

The scripts requires that you are already connected to both Exchange Online and Azure AD.

At the top of the script, you’ll find a variable called $secure. If that is set to $false then no changes will be made to your environment, you’ll just get a report like shown above. Shared mailboxes that have direct login enabled will be displayed in red.

Now, if you change the variable $secure to $true then any shared mailbox that is currently enabled for direct connection will have that ability disabled. The output will display two lines for each mailbox that has direct access enabled. The first line indicates that direct logins are enabled and then the second line will show that has now been secured. In this scenario, all that is happening is that the shared mailbox identity is simply being blocked as I outlined in my earlier article.

The last possibility is that the shared mailboxes direct logins have already been disabled, and in this case the results of the script should simply show that result in green.

In summary then, you can run this script with the variable $secure set to $false to just display the direct login condition of your shared mailboxes. You can run this script with $secure set to $true and then not only will the direct login condition of the shared mailboxes be reported but they will all then be blocked for direct login.

You will find this script in my GitHub Office 365 repository at:

https://github.com/directorcia/Office365/blob/master/o365-exo-sharedblock.ps1

Access the Microsoft Graph with a script

In a recent article I showed how to connect to the Microsoft Graph in a web browser:

Using the Microsoft Graph Explorer

I also showed how you could do the same:

Using Interactive PowerShell

This article is going to focus on how you can do the same thing but directly via a script that won’t prompt you for credentials.

Before running this script you’ll need to follow the Using Interactive PowerShell article and set up an app in your Azure AD.

During that process you’ll need to record three things that will be used here:

1. Application ID

2. Tenant ID

3. Client secret

I borrowed the connection piece of this script from:

https://www.lee-ford.co.uk/getting-started-with-microsoft-graph-with-powershell/

which I found really handy in helping me understand all this. You can download my script from my GitHub repository here:

https://github.com/directorcia/Office365/blob/master/graph-connect.ps1

You’ll need to enter your own Application ID, Tenant ID and Client secret in the variables section. After that, all you need to do is run the script. The results for the query of /security/alerts will end up in a variable called $query which you can view. The actual content of the alerts you’ll find in $query.content.

This Graph connection script should now allow you to connect to the Microsoft Graph for a tenant and start running queries and returning values for entries in there. At the moment it only queries security alerts, but you can modify it to query anything in the Graph for your tenant.

Using interactive PowerShell to access the Microsoft Graph

I recently published an article on how you can browse the Microsoft Graph directly from a web page here:

using the Microsoft Graph Explorer

The next step is to start working with the Microsoft Graph using PowerShell.

This article was recently published by Microsoft:

IT Pros can now easily connect to Microsoft Graph Security with the PowerShell Module!

and one of the confusing things I found where it talks about “Registering your application”, which you need to do successfully before you can run all the PowerShell commands.

Now if I find that confusing I’m sure others will also, as there is a bit of trick in setting it up correctly. So here is what you need to do, step by step, to actually get it all working.

Login to https://portal.azure.com using you Microsoft 365 credentials. Navigate to Azure Active Directory from the list of items on the left.

From the options available on the left select App registration (Preview).

From the pane on the right select New registration at the top of the page.

Give the new application a name. Here I have called it Graph. Next hit the Register button at the bottom.

You should now see the Overview of the app. On the right hand side save both the Application (client) ID and Directory (tenant) ID as you will need these later.

Here’s the bit that isn’t that clear in the existing documentation. Select the Authentication option from the menu and then on the right check the option

urn:ietf:wg:oauth:2.0:oob

From what I can work out, normally apps need to return to a location after using Azure AD authentication. However, because we will be using an interactive PowerShell session, the selected option will simply return there. Again, not really clear in the documentation I read.

You don’t need to make any other changes on the page but ensure you now select Save in the top left.

Next, select the Certificates and secrets option on the left. On the pane that appears on the right select + New client secret.

Give the secret a name and an expiry period and select Add.

You should then see you new secret and the actual value of that secret to the right as shown above. You will need to copy this secret value and keep it secure. treat it like a password.

You will see a banner across the top of the pane telling you that this is only time you get to see the value of the secret in the clear. After you navigate away, you’ll no longer be able to simply copy and paste the complete entry, so do it now and save the secret somewhere secure as you will need it down the track.

Now select API permissions on the left. You should see text Microsoft Graph is hyperlinked, so select this. This means your app already has some basic access to the Microsoft Graph, here just user read right. If the Microsoft Graph entry isn’t visible for some reason you can select the + Add a permission button at the top and then select Microsoft Graph from the following page. Hopefully however, the Microsoft Graph hyperlink will already be there.

There are two boxes at the top of the page. Ensure the left hand (Delegated permissions) one is selected first.

Scroll through the list of permissions in the bottom section until you find the heading SecurityEvents and expand it as shown.

Select both options as shown:

SecurityEvents.Read.All

SecurityEvents.ReadWrite.All

Once these options have been select, press the Update permissions at the bottom of the page.

You’ll be returned to the permission summary page as shown. You should now see the additional permissions you added displayed. You will however note a warning icon next to them as well as a banner across the top informing you that you need to consent to these. We’ll do that shortly, however we want to add some more permissions, so again select the hyperlinked text Microsoft Graph.

This time ensure the box on the right (Application permissions) is selected.

In essence, think of the box on the left (Delegated permissions) as permission for interactive sessions like typing commands into the PowerShell manually. that requires a user to login each time. The right box (Application permissions) however, is going to allow operations without the need for an interactive user login. Thus, we can run a PowerShell script and not be prompted for login. thus, while we are in here it is a good idea to set up both sets of permissions to give you the flexibility later.

As before, scroll through the list of permissions below. Locate the SecurityEvents heading, expand this and select:

SecurityEvents.Read.All

SecurityEvents.ReadWrite.All

Once selected, press the Update permissions button at the bottom of the page to save the changes.

You are again returned to the summary page where you should see all the permissions added. You should see permissions of type Delegated and Application for the security events. A set for each.

If you scroll to the bottom of the page you should see a Grant consent section as shown. Select the Grant admin consent for tenant button below. This means all users will have the permissions you just created. If you don’t do this, then they will have to consent the first time they access the Microsoft Graph using this method.

You should see the above prompt asking you to confirm that you will be consenting for all users in the tenant. Select Yes to continue.

In a few moments, your permission screen should show all green as shown above.

As a final check in the portal, select the Owners option on the left and ensure the appropriate users are listed here. These people will basically have the permissions to edit the application settings, like what has just been configured.

Now you can run an elevated PowerShell window and type:

install-module microsoftgraphsecurity

Once that has installed successfully you can run:

get-graphsecurityalert

Because this is an interactive PowerShell session you’ll need to login to the tenant. A login prompt will appear as shown, however be careful here. You enter your user login AND the Application ID from the app just created in the Azure AD portal here. That is the really long string of digits in the Overview part of the application you just added in Azure AD NOT the user password!

You’ll then be prompted for the user password and MFA if configured

If all of that is good then you should get results as shown above. Now you can continue on with your interactive PowerShell session and all the great stuff in the microsoftgraphsecurity module. Yeah!

The main trick is selecting the urn:ietf:wg:oauth:2.0:oob option as the Redirect URI when configuring the app.

You may have noticed that we have used the Application ID here but not the Application secret. That is because this is an interactive session where the user is required to login in first. if we don’t want to be prompted for a login we need to use the Application secret. That process will be covered in an up coming article so stay tuned.

Reporting mailbox logins

Before much of what is covered here is possible you need to ensure you have enabled all the logging in your Office 365 tenant. I’ve covered how to do that here:

Enabling Office 365 mailbox auditing

Enable mailbox auditing in Exchange Online

Enable activity auditing in Office 365

Once you have done that you will be able to track what’s going on in your tenant much better.

In the situation of a compromised mailbox, a bad actor has control of it using legitimate credentials. This eliminates looking for failed logins, because there won’t be any. It also makes the finding the bad actor tougher because their access is most likely mixed in with the legitimate user.

The place to start is to run an audit log search as I have detailed here:

Searching the Office 365 activity log for failed logins

However, as I mentioned, we can no longer search for failed logins, we need to use a different search criteria. I would suggest that you instead run a search using the attribute “User signed in to mailbox” as shown above. That will produce something like shown for all users. Problem with this is that times and dates are in UTC not local time and it is cumbersome to manipulate in a web page. You can of course manipulate by exporting the results to a spreadsheet for more control.

Unsurprisingly, I feel PowerShell offers a much better solution to check the logs and report as you can see above. The script to do this I have made freely available at my Github repo here:

https://github.com/directorcia/Office365/blob/master/o365-mblogin-audit.ps1

Basically, it will search the Audit log for Exchange Items that are Mailbox logins and send that output to a nice table via the Out-Grid command. As you can see, using Out-Grid you can now easily sort by time by clicking the column heading, and thanks to the script, the times are local not UTC!

By default, the script will check the last 48 hours but you can easily modify that to suit your needs by either entering the scope in hours or entering a start and end date in the variables at the top of the script.

With this output I can now look for suspect IPs that login into the mailbox and begin hunting from there. However, remember, all of this relies on you enable your auditing BEFORE you need it. So, if you haven’t enabled it, go do it now! You’ll find scripts to enable the logs also in my Office 365 repo here:

https://github.com/directorcia/office365

Updated script to now check for Sweep

The bad actors out there are clever and they’ll use any means at their disposal. Normally, when a user is successfully phished the first thing bad actors do is manipulate the email handling rules of the mailbox to hide their activity.

Unfortunately, there are quite a lot of different ways to forward email in Office 365 including via the mailbox and via Outlook client rules. It was brought to my attention that there is in fact another way that forwarding can be done, using the Sweep function. You can read more about this ability at:

Organize your inbox with Archive, Sweep and other tools in Outlook.com

Sweep rules only run once a day but do provide a potential way for bad actors to hide their activity, however as it turned out Sweep was in fact being exploited by bad actors inside a compromised mailbox.

I have therefore updated my publicly available PowerShell script at:

https://github.com/directorcia/Office365/blob/master/o365-exo-fwd-chk.ps1

That will now also check and report on any Sweep rules in finds in mailboxes as well as any other forwards configured in the tenant.

Let me know if you find any other methods that this doesn’t cover and I’ll look at incorporating those as well.

Using PowerShell to download all my Office 365 scripts together

I really like GitHub but the problem is that it is really a developer not IT Pro style environment. That means there is no easy way (that I know of at least) to simply to copy every file in a repository to a directory on your local computer. Yes, you can do that with Visual Code like I do, but what if you just want a complete copy so you can run the scripts that I have created quickly and easily?

Have no fear, PowerShell to the rescue once again.

I have just created the following script:

https://github.com/directorcia/Office365/blob/master/o365-getrepo.ps1

which, when run, will basically grab every file in my Office 365 repository and download it to the directory you nominate in the variable at the top of the script.

So the process is to copy the above script into the PowerShell ISE. Modify the variable $DestinationPath to suit your environment and then simply run the script.

The great thing is that you can re-rerun the script at any time to grab the latest updates I have made in that repository.