How do you find ALL the Flows that reference a SharePoint site or list?

I asked this question when I first started down the path of learning about Flow:
How do you find all the Flows running on or referencing a SharePoint list?

Think about it, someone in your company creates a Flow that runs when a SharePoint item is updated. Fast forward a year or so, and that coworker has moved on, and the Flow needs to be updated. If you work for a small company or one that hasn’t fallen in love with Power Platform and Flow, you’re likely in luck, and finding the Flow will take a few minutes. In my case, there are currently 2,712 Flows in my tenant that span several environments.

The PowerShell script I’ve created will query a tenant using the Get-AdminFlow command, return all Flows, and then loop through them. The script can be adjusted to target a single environment using the EnvironmentName parameter. Note: running the script using the Get-Flow action will return all the Flows your AD account can access.

#Install-Module AzureAD
#Install-Module -Name Microsoft.PowerApps.Administration.PowerShell  
#Install-Module -Name Microsoft.PowerApps.PowerShell -AllowClobber 

#connect-AzureAD

function Get-UserFromId($id) {
    try {
        $usr = Get-AzureADUser -ObjectId $id
        return $usr.displayName
    }
    catch {
        return $null
    }
}

#get all flows in the tenant
$adminFlows = Get-AdminFlow 

#set path for output
$Path = "$([Environment]::GetFolderPath('Desktop'))\Flow_Search_for_SharePoint_$(Get-Date -Format "yyyyMMdd_HHmmss").csv"

#set target site
$targetSPSite = "https://yourTenant.sharepoint.com/sites/yourSITE"
$targetSPList = "4f4604d2-fa8f-4bae-850f-4908b4708b07"
$targetSites = @()

foreach ($gFlow in $adminFlows) {

    #check if the flow references the target site
    $refResources = $gFlow.Internal.properties.referencedResources | Where-Object { $_.resource.site -eq $targetSPSite }

    #check if the flow references the target list
    #$refResources = $gFlow.Internal.properties.referencedResources | Where-Object { $_.resource.list -eq $targetSPList }

    if ($refResources -ne $null) {

        #optional - get the user who created the Flow
        $createdBy = Get-UserFromId($gFlow.internal.properties.creator.userId)

        $row = @{}
        $row.Add("EnvironmentName", $gFlow.EnvironmentName)
        $row.Add("Name", $gFlow.DisplayName)
        $row.Add("FlowEnabled", $gFlow.Enabled)
        $row.Add("FlowGUID", $gFlow.FlowName)
        $row.Add("CreatedByUser", $createdBy)
        $row.Add("CreatedDate", $gFlow.CreatedTime)
        $row.Add("LastModifiedDate", $gFlow.lastModifiedTime)
        
        $targetSites += $(new-object psobject -Property $row)
    }
}

#output to csv
$targetSites | Export-Csv -Path $Path -NoTypeInformation

If you don’t want to get the display name of the user who created the Flow, comment out the part of the script that calls the Get-UserFromId function, and you won’t need to connect to Azure.

And to answer my original question: How do you find all the Flows running on or referencing a SharePoint list?
In the script, comment out the part of the script that references $targetSPSite and un-comment $targetSPList. You can get the GUID of the list by navigating to list settings and looking at the URL. Another option is to open the list, view the Page Source, then look for the “listId” property.

In a future post(s), I will outline how to search for all Flows that use different connectors, Dynamics 365 tables (dataverse), triggered from Power Apps, or other objects. All of the info is in the properties of the Flow; getting to it can be a little fun.

Get Files From a SharePoint Folder Using PowerShell PNP

How do you get all the files from a folder in SharePoint using PowerShell PNP?

$devConn = Connect-PnPOnline -Url "https://sharepointed.sharepoint.com/sites/siteA/siteB" -Credentials -Credentials (Get-Credential) -ReturnConnection

$folderName = "/Shared Documents/myfolder/anotherfolder"

$folderItems = Get-PnPFolderItem -FolderSiteRelativeUrl $folderName -Connection $devConn

foreach($item in $folderItems)
{
    Write-Host $item.Name
}

Write-Host "done"

Depending on your needs, you could also use a search query with a path filter to get the files.

Example of using the Get-PnPListItem cmdlet with the FolderServerRelativeUrl parameter.

$devConn = Connect-PnPOnline -Url "https://sharepointed.sharepoint.com/sites/siteA/siteB" -Credentials -Credentials (Get-Credential) -ReturnConnection

$folderName = "/sites/spdev2/bw2/Shared Documents/myfolder/anotherfolder"

$folderItems = Get-PnPListItem -List "Shared Documents" -FolderServerRelativeUrl $folderName -Connection $devConn 

foreach($item in $folderItems)
{
   Write-Host $item
}
    

Using SharePoint Keyword Query to Search Across Site Collections

Quick and easy way to search for an item across site collections. I would suggest using one of the Keyword query tools to fine-tune your queries. Also note that SharePoint will limit your search results to 10,000 items, but you can page your results and cycle through them. In the example below, I’m searching across all the site collections off of the /sites/ managed path. With the returned dataset, I’m looping through the rows getting the SPFile of each row.

$site = New-Object Microsoft.SharePoint.SPSite "https://example.site.com"

$keywordQuery = New-Object Microsoft.office.Server.Search.Query.KeywordQuery $site

$queryText = "SomeField:Taco AND Path:https://example.site.com/sites/*"
$keywordQuery.QueryText = $queryText
$keywordQuery.TrimDuplicates = $false
$searchExec = New-Object Microsoft.Office.Server.Search.Query.SearchExecutor
$searchResults = $searchExec.ExecuteQuery($keywordQuery)

$dTable = $searchResults.Table[000].Table.Rows

foreach($row in $searchResults.Table[000].Table.Rows)
{
      $web = Get-SPWeb $row.SPWebUrl
      $file = $web.GetFile($row.Path)
      Write-Host $file.ServerRelativeUrl
}

The Web application at X could not be found.

Error: The Web application at https://sharepoint.sharepointed.com could not be found. Verify that you have typed the URL correctly. If the URL should be serving existing content, the system administrator may need to add a new request URL mapping to the intended application.

I created a .net console app to update some stuff in SharePoint.  I received the above error when executing the .exe file with a new service account. 

First, I tried granting Shell access to the content db I was working with, but that didn’t solve the problem.

$cDb = Get-SPContentDatabase -site "https://taco.sharepointed.com/" Add-SPShellAdmin -UserName "domain\userAccount -database $cDb

Running the same command without the database switch fixed my problem.

Add-SPShellAdmin -UserName "domain\userAccount"

Use PowerShell to Execute SharePoint Search Queries

In this example, I’m narrowing my search to one library and a search term.
At a high level, the script searches the FoodSite for the word GoodTaco.

function Query-SPSearch {
    param(
        [Parameter(Mandatory=$true)][String]$WebApplicationPath,
        [Parameter(Mandatory=$true)][String]$KeywordQuery,
        [Parameter()][Int32]$Count = 10
    )
 
    $QueryXml = @"
 
<QueryPacket xmlns="urn:Microsoft.Search.Query" >
    <Query>
        <Context>
            <QueryText type="STRING">$KeywordQuery</QueryText>
        </Context>
        <Range>
            <Count>$Count</Count>
        </Range>    
        <IncludeSpecialTermResults>false</IncludeSpecialTermResults>
        <PreQuerySuggestions>false</PreQuerySuggestions>
        <HighlightQuerySuggestions>false</HighlightQuerySuggestions>
        <IncludeRelevantResults>true</IncludeRelevantResults>
        <IncludeHighConfidenceResults>false</IncludeHighConfidenceResults>
    </Query>
</QueryPacket>
"@
    $ServicePath = "/_vti_bin/search.asmx"
    $SearchWS = New-WebServiceProxy -Uri ($WebApplicationPath + $ServicePath) -UseDefaultCredential
    $Results = $SearchWS.QueryEx( $QueryXml )
    # we excluded all other result sets, but just in case get the one we want:
    $Results.Tables["RelevantResults"]
}
 
Query-SPSearch -WebApplicationPath "https://sharepointed.com/sites/foodsite" -KeywordQuery "GoodTaco AND path:https://sharepointed.com/sites/foodsite/tacos" -Count 20 | Format-Table Title, Author, Path

Searching SharePoint Using PowerShell

In this example, I needed to search a farm for every site under a managed path. BUT, the sites I’m searching for were built using a 3rd part tool and would not correctly appear in the search results.  The problem was related to having Trim Duplicates enabled by default.  Easy fix… Set your search property trim duplicates = false.

$site = Get-SPSite "https://sharepointed.com"

$keywordQuery = New-Object Microsoft.Office.Server.Search.Query.KeywordQuery($site)<br>$queryText = "ContentClass:STS_Site AND Path:https://sharepointed.com/TACOS/*"

$keywordQuery.QueryText = $queryText<br>$keywordQuery.TrimDuplicates = $false 
$searchExec = New-Object Microsoft.Office.Server.Search.Query.SearchExecutor<br>$searchResults = $searchExec.ExecuteQuery($keywordQuery)

Write-Host "'r'n"
$table = $searchResults.Table
Write-Host $table.Length" Results Found" -BackgroundColor "Green" -ForegroundColor "Black"
$table | select Title, Path, IsDocument

The search results will display all sites that have Taco as its managed path. If you are not retrieving the results you expect, try switching TrimDuplicates = $false .

Make Your PowerShell Script Environment Aware

In place of hard-coding URLs for each environment, I decided to make a single script that is environmentally aware. Why? Cuts down on the number of scripts that have to be supported for a single development cycle. To make this more dynamic, you could move this to a function script and reference it from all your scripts.

if ((Get-PSSnapin -Name Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue) -eq $null)
{
	Add-PsSnapin Microsoft.SharePoint.PowerShell
}

#get config database server
$ConfigDB = Get-SPDatabase | where-Object{$_.Type -eq "Configuration Database"}
$serverName = $ConfigDB.Server.Displayname

#replace this with the web app you want to target.  taco, burrito, nacho...
$webApp = "taco"

#set variable equal to the environment url
$siteURL = switch ($serverName.ToLower())
{
	"dev_db" {"http://$webApp.sharepointed.com/"}
	"test_db" {"http://test$webApp.sharepointed.com/"}
	"build_db" {"http://build$webApp.sharepointed.com/"}
	"prod_db" {"http://$webApp.sharepointed.com/"}
}

Same as above, but using a wildcard in the switch statement.

$siteURL = switch -Wildcard ($serverName.ToLower())
{
	"*dev*" {"http://$webApp.sharepointed.com/"}
	"*test*" {"http://test$webApp.sharepointed.com/"}
	"*build*" {"http://build$webApp.sharepointed.com/"}
	"*prod*" {"http://$webApp.sharepointed.com/"}
}

Make sure to check $serverName = $ConfigDB.Server.Displayname
This might need to be replaced with $ConfigDB.Displayname

Use PowerShell to get all your SQL databases and their size

Quick script to get all the databases on a server, then output to a CSV file.

In this example, all I needed was the database name and its size.

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') "YourSQLServer"
$dbs=$s.Databases
$dbs | SELECT Name, Size | Export-Csv c:\test.txt

Other properties:

$dbs | SELECT Name, Collation, CompatibilityLevel, AutoShrink, RecoveryModel, Size, SpaceAvailable

Credit for the base script:
Edwin M Sarmiento

Use PowerShell to add Holidays to Outlook Calendar

Geeking around with PowerShell today trying to add all company holidays to my Outlook calendar. In the script, I’m creating all day appointments and setting the Show As to out of office. Simple enough!

CLOSE Outlook before running the script.

function get-mailfolders {
	$outlookfolders = @()
	$outlook = New-Object -ComObject Outlook.Application
	foreach ($folder in $outlook.Session.Folders){ 

		foreach($mailfolder in $folder.Folders ) {
			$olkf = New-Object PSObject -Property @{
				Path = $($mailfolder.FullFolderPath)
				EntryID = $($mailfolder.EntryID)
				StoreID = $($mailfolder.StoreID)
			} 

			$outlookfolders += $olkf
		}
	}
	$outlookfolders
}

$outlook = new-object -com Outlook.Application
$folder = get-mailfolders | where {$_.Path -like "*calendar*" -and $_.Path -and $_.Path -like "*$mailbox*"}
$calendar = $outlook.Session.GetFolderFromID($folder.EntryID, $folder.StoreID) 

$holidays = @{"01/01/2017"="New Year’s Day"; "01/16/2017"="Martin Luther King Day"; "02/02/2017"="Presidents Day"; "05/29/2017"="Memorial Day"; `
	"07/04/2017"="Independence Day"; "11/4/2017"="Labor Day"; "11/23/2017"="Thanksgiving Break"; "11/24/2017"="Thanksgiving Break"; "12/25/2017"="Christmas Day"}

foreach($holiday in $holidays.GetEnumerator() | Sort Key)
{
	[string]$hName = $holiday.Value
	$hDate = Get-Date $holiday.Key

	$appt = $calendar.Items.Add(1)
	$appt.Start = $holiday.key.ToString()
	$appt.AllDayEvent = $true
	$appt.Subject = $hName
	$appt.Body = $hName

	&amp;amp;lt;#
	Show As / Status
	0 = Free
	1 = Tentative
	2 = Busy
	3 = Out of Office
	#&amp;amp;gt;
	$appt.BusyStatus = 3

	$appt.Save()
}

You might see this error if you haven’t CLOSED Outlook:

new-object : Retrieving the COM class factory for component with CLSID {0006F03A-0000-0000-C000-000000000046} failed due to
the following error: 80080005 Server execution failed (Exception from HRESULT: 0x80080005 (CO_E_SERVER_EXEC_FAILURE)).
At C:\Code\PS\SPO_BHP\Set_Holidays.ps1:19 char:12
+ $outlook = new-object -com Outlook.Application
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : ResourceUnavailable: (:) [New-Object], COMException
+ FullyQualifiedErrorId : NoCOMClassIdentified,Microsoft.PowerShell.Commands.NewObjectCommand

This post helped guide me in the right direction:
Create a calendar item

Add and Remove Shell Access in SharePoint Using PowerShell

How do you add or remove shell access to a web apps content databases?

This script will grant shell access to a user on all the content databases associated with a content database.

$contentDbs = Get-SPContentDatabase -WebApplication "http://yourSharePointWebApp.com/"

foreach($db in $contentDbs)
{
	Add-SPShellAdmin -UserName "domain\user"  -database $db
}

Remove shell access

$contentDbs = Get-SPContentDatabase -WebApplication "http://yourSharePointWebApp.com/"

foreach($db in $contentDbs)
{
	Remove-SPShellAdmin -UserName "domain\user"  -database $db
}

After running the remove script, make sure you check the WSS_Admin_WPG and WSS_WPG groups on the servers in your farm.