Sharepoint RootFolder in Query String

csm_sp2013logo_0abaeddb68                     reportingservices

I’ve been working on a GUI program in Sapien Powershell Studio.  This program deploys SSRS reports to a integrated SSRS and Sharepoint instance.  While writing this I discovered that to upload the Files to sharepoint you must have the full path structure to where you are placing the file.  Well turns out what sharepoint gives you when you are browsing to a subfolder is this very weird url with query strings in it. To ensure that I get what I want from my user and I know where to upload the file I had to write a function to parse the query string and return it to my calling function so I could upload properly. This article is about how i was able to get this to function.

First lets look at  the query string I was dealing with:

http://servername/sites/mysite/Shared%20Documents/Forms/Allitems.aspx?RootFolder=%2fsites%2fmysite%2fTest&folderCTID=0x0120004845B289B9EC1E479DF75ADD1F150A9E

So if we look at this query string closely we can see the real folder name for my Sharepoint site and location is after the RootFolder query string %2fsites%2fmysite%2fTest 

So with that in mind I started looking for a way to parse this string and get out what I wanted after the RootFolder query string.  So I attempted using string manipulation and several other methods.  I kept thinking to myself there must be a better way.  So I dug deeper and I found a #Csharp Class that deals with urls: [System.Web.HttpUtility]

Now that I have something to deal with urls.  I sleuth around the class with Powershell until I found this function ParseQueryString.  Now if I take my url and feed it to this class. To be able to use it I must add the type so that Powershell Can see it:

 Add-Type -AssemblyName System.Web
 PS PS:\> [uri]$Url = 'http://servername/sites/mysite/Shared%20Documents/Forms/Allitems.aspx?RootFolder=%2fsites%2fmysite%2fTest&folderCTID=0x0120004845B289B9EC1E479DF75ADD1F150A9E'

[System.Web.HttpUtility]::ParseQueryString($url) http://servername/sites/mysite/Shared Documents/Forms/Allitems.aspx?RootFolder

My return results don’t seem to be what I’m looking for. Since my return results contain two items which look to be an array.  I’ll query them via index number:

PS PS:\> $a = ([System.Web.HttpUtility]::ParseQueryString($url))
PS PS:\> $a[0]
/sites/mysite/Test

Interesting The results are in the first array member so this must be a hashtable of some sort?

PS PS:\> $a.gettype()

IsPublic IsSerial Name BaseType
——– ——– —- ——–
False True HttpValueCollection System.Collections.Specialized.NameValueCollection

No it’s not a Hashtable but a specialized System.Collections.Specialized.NameValueCollection. Hmmm so how do i get the value I want out of the collection?

PS PS:\> $a | get-member -MemberType Properties

TypeName: System.String

Name MemberType Definition
—- ———- ———-
Length Property int Length {get;}

The only property it has is Length which doesn’t really give me what I want the actual text from the query string.  So it must be a method that I need to use:

PS PS:\> $a | get-member -MemberType Method
TypeName: System.String

Name MemberType Definition
—- ———- ———-
Clone Method System.Object Clone(), System.Object ICloneable.Clone()
CompareTo Method int CompareTo(System.Object value), int CompareTo(string strB), int IComparable.CompareTo(System.Object obj), int IComparable[string].CompareTo(string other)
Contains Method bool Contains(string value)

Hmm this variable now says its of type string and all the methods are string methods. After looking around for some time I finally tried somethings in ISE and I got to the method I was looking for: GetValues

system.collections.specilaized.namevaluecollection.getValues

So now I can attempt to see if I can get the values I want from this object:

PS PS:\> $a.GetValues('http://servername/sites/mysite/Shared Documents/Forms/Allitems.aspx?RootFolder')

Cool I got what i was looking for the value for RootFolder query string. Now I’m half way there. Now all that is needed is to construct the getValues method call to only get what I’m looking for RootFolder. 

PS PS:\> $a.GetValues(($a.keys | Where-Object{$_ -like "*RootFolder*"}))</span>

Now I have the value I want so I can now create a function to return the url I need for my program.  Here is the full Function:

function Format-SharepointUrl
{
 param
 (
 [parameter(Mandatory = $true)]
 [uri]$url
 )
add-type -assembly system.web
 $newUrl = $null
 [System.Collections.Specialized.NameValueCollection]$uCollection = [System.Web.Httputility]::ParseQuerySTring($url)
 if ($uCollection.GetValues(($ucollection.keys | ?{ $_ -like "*RootFolder*" })))
 {
 $cUrl = $uCollection.GetValues(($ucollection.keys | ?{ $_ -like "*RootFolder*" }))
 $newUrl = "$($url.Scheme)://$($url.Host)$cUrl"
 }
 $newUrl
}

I hope this helps someone ..

Until then keep scripting

Posting Azure Audit results to Office 365 Sharepoint

Recently I blogged about Auditing azure resources and I also blogged about how to work with the OficeDevPnP.PowerShell Module.  This blog is about how i used both of these items to give myself a Sharepoint list that contains the Azure resources I audited.  This allows for my users to know what is in Azure.  this also allows me to put an Application name to the item I pulled from azure.

Here is what My list looks like in Sharepoint:

image

So now since I have my list built i can import the OfficeDevPnP module:

import-module OfficeDevPnP.PowerShell.V16.Commands

And then connect to my office 365 instance:

Connect-SPOnline –Url 'https://my.sharepoint.com/departments/mysite'

Ok so I have my connection to my site now I need to retrieve my azure listing:

Import-azurerm
Login-AzureRMAccount -credential $myAzureCredentials

Then using the Get-DaAazureRMResources function that i blogged about here i can get all my resources for posting to office 365 list:

$myAzureListing = Get-DaAzureRMResources

In order to post to my SharePoint list  I have a field called application which doesn’t exist in Azure but does in my SharePoint list. The next loop will add this Application property to my object.

foreach($a in $myAzureListing )
{ write-debug "$($a.SubscriptionName)"}

With the switch we are going to add a field to the Azure listing called Application so that it meets the requirements of my Office 365 list.   I have two cases where I need the name to be different than what is specified(Azure subscriptionname). So i put those two cases in the switch to account for them.

switch -Wildcard ($a.SubscriptionName)
{
"myApp*" {Add-Member -MemberType NoteProperty -Name 'Application' -InputObject $a -Value 'ThomsApp' }
"CRM" {add-member -MemberType NoteProperty -Name 'Application' -InputObject $a -value 'MyCRM' }
Default
{
Add-Member -MemberType NoteProperty -Name 'Application' -InputObject $a -Value $($a.SubscriptionName )
}
}
}

The Azure listing that my data is going to has a linked list to another list in SharePoint called Applications.  In order to post the new items from the azure pull I’ll need to get the id of each application from the other list. The view of the list is shown below:

image

The applicationlist variable is where a get of the list is done and then the applicationlistObj contains the list in object form.
$applicationList = (Get-SPOListItem -List 'Applications' ).fieldvalues
$applicationListObj = $applicationList | ForEach-Object{New-Object psobject -Property $_}

Now I need to get my existing Azure Assets list to ensure I don’t put any duplicates in this list

$azureSPlistName = 'AzureAssets'
$azureSpList = (get-spolistitem -list 'AzureAssets').fieldvalues | foreach-object{new-object psobject -Property $_}

Now we can loop through each item in the Azure list to put them into our SharePoint list.

foreach($item in $azureList)
{

current assets variable gets us the record that matches the Item we are currently on in our loop.

$currentAssets = $azureSpList |?{$_.ResourceId -eq $item.resourceid}

Here we need to get the application id. this is so that when we update the list we use the value of the application name instead of the friendly name.

$appId = ($applicationListObj | ?{$_.Title -eq $item.application}).id

Now will build our hash to update to the sharepoint list

$azureHash = @{'Title' = $($item.Name);'Application' = $appId ; 'SubscriptionName' = $item.SubscriptionName;'ResourceName' = $item.Resourcename; 'ResourceType' = $item.ResourceType ; 'ResourceGroupName' = $item.ResourceGroupName; 'Tags' = $item.Tags; 'Location' = $item.Location; 'ResourceId'= $item.ResourceId; 'SubscriptionId' = $item.SubscriptionId }

Now we check to see if the record already exists in the SharePoint list if it does we’ll need to perform an update.

If($currentAssets) #we found the record in the sharepoint site now we need to update.
{
Set-SPOListItem -List $azureSPlistName -Identity $currentAssets.id -Values $azureHash
}
else #since we know there isn't another record of this type in our sharepoint list we'll just add it.
{
add-spolistitem -list $azureSPlistName -Values $azureHash
}
}

Hopefully this helps someone with updating or making a new list from objects in Azure.

Full script is below:

import-module OfficeDevPnP.PowerShell.V16.Commands
Connect-SPOnline –Url 'https://my.sharepoint.com/departments/mysite' -Credentials $sp2credentials
$myAzureListing = Get-DaAzureRMResources
foreach($a in $myAzureListing)
{ write-debug "$($a.SubscriptionName)"
switch -Wildcard ($a.SubscriptionName)
{
"myApp*" {Add-Member -MemberType NoteProperty -Name 'Application' -InputObject $a -Value 'ThomsApp' }
"CRM" {add-member -MemberType NoteProperty -Name 'Application' -InputObject $a -value 'myCRM' }
Default
{
Add-Member -MemberType NoteProperty -Name 'Application' -InputObject $a -Value $($a.SubscriptionName )
}
}
}
$applicationList = (Get-SPOListItem -List 'Applications' ).fieldvalues
$applicationListObj = $applicationList | ForEach-Object{New-Object psobject -Property $_}
$azureSPlistName = 'AzureAssets'
$azureSpList = (get-spolistitem -list 'AzureAssets').fieldvalues | foreach-object{new-object psobject -Property $_}
foreach($item in $azureList)
{ </code><code>$currentAssets = $azureSpList |?{$_.ResourceId -eq $item.resourceid}
$appId = ($applicationListObj | ?{$_.Title -eq $item.application}).id
$azureHash = @{'Title' = $($item.Name);'Application' = $appId ; 'SubscriptionName' = $item.SubscriptionName;'ResourceName' = $item.Resourcename; 'ResourceType' = $item.ResourceType ; 'ResourceGroupName' = $item.ResourceGroupName; 'Tags' = $item.Tags; 'Location' = $item.Location; 'ResourceId'= $item.ResourceId; 'SubscriptionId' = $item.SubscriptionId }
If($currentAssets)
{
Set-SPOListItem -List $azureSPlistName -Identity $currentAssets.id -Values $azureHash
}
else
{
add-spolistitem -list $azureSPlistName -Values $azureHash
}
}

Until then keep scripting