Tag: Powershell reports

PowerShell; SharePoint Online Inventory and User Activity Report

Posted in MicrosoftTagged , , , , , , , , , , , , , Leave a comment

I am frequently asked to generate various reports for SharePoint Online. Usually, I can get the requested information from the Microsoft 365 Reports console, this time was different. We had just merged two Microsoft 365 tenants. The data move had worked according to the tools. The problem was that independent reports showed people were still using the old target sites after the cut date.

Microsoft’s SharePoint activity reports obscure the user’s identity for security reasons. We specifically needed to identify who was accessing data in the old tenant to troubleshoot the issue. There is a setup option you can toggle to display user identities in the reports. I turned on the switch and waited five business days with no results. I couldn’t wait any longer and needed to pivot to another solution. If you want to try, the Microsoft documentation is here.

I decided to go with a PowerShell generated report instead. Power BI would also have been a good choice, but I had an ace up my sleeve. I have been using a PowerShell script written by Salaudeen Rajack on his SharePoint Diary blog for quite a while. I knew that it was almost perfect for this project. I would just need to craft a method for looping it through all of the sub-sites in our old tenant.

Salaudeen’s script is based on the open-source PnP SharePoint PowerShell module. He uses it to create a PowerShell function. This function takes a folder and file level inventory of all the documents stored in any given SharePoint site. It generates a report showing the URL, total size, type, creation date, last accessed date, along with the user account.

I didn’t want to authenticate hundreds of times as the function looped through each site in the tenant. So, I registered the PnP module as an App in Entra ID and used certificate-based authentication. Again, Salaudeen’s blog provides excellent instructions.

All that was left was to generate a list of all the individual site URLs. Then I needed to pass them through a logic loop to call the report function. The resulting report is perfect for understanding your SharePoint Online environment as a whole.

Import-Module -Name PnP.PowerShell
#Function to collect site Inventory
Function Get-PnPSiteInventory{
[cmdletbinding()]
   param([parameter(Mandatory = $true, ValueFromPipeline = $true)] $Web)
    #Skip Apps
    If($Web.url -notlike "$SiteURL*") { return }
   
    Write-host "Getting Site Inventory from Site '$($Web.URL)'" -f Yellow
 
    #Excluded libraries
    $ExcludedLists = @("Form Templates", "Preservation Hold Library")
                               
    #Get All Document Libraries from the Site
    $Lists= Get-PnPProperty -ClientObject $Web -Property Lists
    $Lists | Where-Object {$_.BaseType -eq "DocumentLibrary" -and $_.Hidden -eq $false -and $_.Title -notin $ExcludedLists -and $_.ItemCount -gt 0} -PipelineVariable List | ForEach-Object {
        #Get Items from List  
        $global:counter = 0;
        $ListItems = Get-PnPListItem -List $_ -PageSize $Pagesize -Fields Author, Created, SMTotalSize -ScriptBlock `
                 { Param($items) $global:counter += $items.Count; Write-Progress -PercentComplete ($global:Counter / ($_.ItemCount) * 100) -Activity "Getting Inventory from '$($_.Title)'" -Status "Processing Items $global:Counter to $($_.ItemCount)";}
        Write-Progress -Activity "Completed Retrieving Inventory from Library $($List.Title)" -Completed
     
            #Get Root folder of the List
            $Folder = Get-PnPProperty -ClientObject $_ -Property RootFolder
           
            $SiteInventory = @()
            #Iterate through each Item and collect data          
            ForEach($ListItem in $ListItems)
            {  
                #Collect item data
                $SiteInventory += New-Object PSObject -Property ([ordered]@{
                    SiteName  = $Web.Title
                    SiteURL  = $Web.URL
                    LibraryName = $List.Title
                    ParentFolderURL = $Folder.ServerRelativeURL
                    Name = $ListItem.FieldValues.FileLeafRef
                    Type = $ListItem.FileSystemObjectType
                    ItemRelativeURL = $ListItem.FieldValues.FileRef
                    CreatedBy = $ListItem.FieldValues.Author.Email
                    CreatedAt = $ListItem.FieldValues.Created
                    ModifiedBy = $ListItem.FieldValues.Editor.Email
                    ModifiedAt = $ListItem.FieldValues.Modified
                    FileSize =  $ListItem.FieldValues.SMTotalSize.LookupID
                })
            }
            #Export the result to CSV file
            $SiteInventory | Export-CSV $CSVReport -NoTypeInformation -Append
        }
}
#Parameters
$CSVReport = "C:\Temp\SharePoint_Site_Inventory_And_Activity_Report.csv"
$Pagesize = 2000
#Connect to the SharePoint 365 admin console with a registerd app and get all of the SharePoint Site's URLs
Connect-PnPOnline -Url "https://yoursite-admin.sharepoint.com/" -Interactive -ClientId xxxxxxxxxx-xxxxx-xxxxxxx-xxxxxxxx-xxxx
$Sites = Get-PnPTenantSite
#Call the Function for all SharePoint Online sites in the tenant, one at a time.
Foreach ($Site in $Sites){
    $SiteUrl = $site.Url
    #Connect to each site with the registered app before calling the function for it.
    Connect-PnPOnline -Url $SiteURL -Interactive -ClientId xxxxxxxxxx-xxxxx-xxxxxxx-xxxxxxxx-xxxx
    Get-PnPSubWeb -Recurse -IncludeRootWeb | ForEach-Object { Get-PnPSiteInventory $_ }
}

I ran the script above on a tenant with around 500 sub-sites and about a thousand users. It took nine and a half hours to finish. The resulting CSV file was 42 megabytes in size. You’ll need to know the URL for your SharePoint Online admin console. You’ll also need the ClientID from the Entra app you registered.

The script is configured for an interactive logon so that it will work with MFA. After the first logon prompt each sub-site will use the same session. Once you have generated the report, there is a lot you can do with Excel to highlight various aspects.

References:

Powershell: Real-Time Check of Domain Server’s Uptime

Posted in MicrosoftTagged , , , , , Leave a comment

There are lots of methods available to server administrators for checking the last reboot time of Windows machines. One of the quickest and most useful continues to be provided via Microsoft’s super CLI, PowerShell.

$ErrorActionPreference = “SilentlyContinue”
$Servers = Get-ADComputer -Filter ‘Operatingsystem -Like “*server*”‘ -Properties dnshostname|
    Select-Object dnshostname -ExpandProperty dnshostname
Function CheckReboot {
    Foreach ($Server in $Servers) {
    Invoke-Command -ComputerName $Server {(Get-Date)-(gcim Win32_OperatingSystem).LastBootUpTime}|Select PSComputerName, Days, Hours,Minutes
    }
}

Those few lines will scan the Active Directory database for computer accounts with the word “server” in their names. Once located, the DNS name property of each matching record is stored in the variable $Servers.

The function named CheckReboot processes each DNS name stored in $Servers by sending it through a logic loop. The for each loop uses WMI to subtract the boot up time from the current time. Then, it displays a table showing the computer’s name, along with how many days, hours, and minutes each system has been running for.

You will need to run the script from an administrative  terminal session. If it doesn’t work for you, try adding a lime to import the Active Directory Powershell module and check your execution policy. The machine and account running the script also need remote access to WMI.

Free Exchange Distribution List Memebers Reporting Tool

Posted in MicrosoftTagged , , , , , , Leave a comment

As and Exchange Architect, I often get asked to report on who belongs to a particular distribution list. It is easy enough to open Outlook or ADUC and find the DL group and view the DL members. If your organization has Skype for Business you can also use it to view the membership of a DL. However, exporting information from these tools can be tricky.

PowerShell makes the export task easier if you know how to use it. A quick run of the get-distributiongroup cmdlet with a pipe to out-file, will get you what you want in a hurry. The problem is that we have few employees that are able to use PowerShell from the console but, there are requests to generate this type of report multiple times per day.

What we need is a graphical tool that anybody with enough AD permissions can use to get the data they need. Sure I could write something in C# but I don’t have the time to develop a full app. I know PowerShell already has everything I need to make the report because that’s what I use every time I get asked for it. What if I add a simple graphical interface to a PowerShell script so that my non PS coworkers can easily use it? In the words of Tim Allen; “It’s Tool Time”.

The script below uses the Grid View to display a list of all the Distribution Lists in your Exchange DL OU (you’ll need to input the correct OU path). The user then uses the grid view to sort, filter, or search for the DL they need data from. When they select it, an Excel Spreadsheet will pop up on the screen with the data.

Modern versions of Exchange Server store their DLs in AD as groups so there’s no need to install or load the Exchange PowerShell module to get the data we’re after. This also means we don’t need to give the people who use our tool any special permissions. We will be using the Active Directory module so you may need to install the RSAT depending on the version of Windows.

Import-Module ActiveDirectory
$groups = Get-ADGroup -Filter * -Searchbase "OU=Distribution Lists,OU=Exchange,DC=yourdomain,DC=com"|
Select @{n="Distribution Group"; e={$_.Name}}, DistinguishedName |Sort "Distribution Group"|
Out-GridView -Title "Select a Group, then click OK"  -PassThru
$accounts = Foreach ($group in $groups) {Get-ADGroupMember -Identity $group.DistinguishedName}
$report = Foreach ($account in $accounts) {Get-ADUser -Identity $account -Properties *|
select DisplayName, EmailAddress, TelephoneNumber, Department, City}
$report|Export-Csv -LiteralPath $env:userprofile\documents\dlgroupmemebers.csv -notypeinformation
Invoke-Item $env:userprofile\documents\dlgroupmemebers.csv

Make sure that you replace the Searchbase path with the Active Directory location for your Exchange distribution lists. After that, the instructions are easy. Save the file as a .ps1 and place it on the user’s hard drive.

The person using the tool will need to follow these directions:

  • Right click on the script  –> Open With -> Windows PowerShell

open with powershell

  • Search for or scroll through and click the group or groups (to pick more than one use CTRL + Click).
  • Click the OK button at the bottom of the list.

Grid_View_Select.png

  • A file named groupmembers.csv will be created in the user’s default documents folder and will automatically open with the application associated to that file type (usually Excel).

From the point of view of the person running it, this is a once use app. In reality, its a simple PowerShell script. It wouldn’t take very much effort to convert this report to HTML, use it to cross-referrence mailboxes and other accounts, find the memebers managers, or anything esle you may need. Enjoy.