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:

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:

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