Array which to use

This topic contains 8 replies, has 4 voices, and was last updated by Profile photo of Dan Potter Dan Potter 1 year, 10 months ago.

  • Author
    Posts
  • #21841
    Profile photo of nOrphf
    nOrphf
    Participant

    Hi

    I have searched, and found too many answers for building arrays so I'm more confused that good is.
    I need to pull some data data source, so I use a foreach loop to fill the array, and later be able to search the array for one cell, get the index number of the row, and then retrieve all data in that row.

    Either I get stuck on the indexof og I can't put data in the array. I have created some soudo code, if some would be so kind to write it in true powershell instead.

    #Declare how?
    $array
    
    ForEach ($row in $DataSource) {
        #Fill the array how?
        $array.Add.Column(1,2,3) = "Info1","Info2","Info3"
        Some.logic
        $array.add.Column(4,5,6) = "Info4","Info5","Info6"
    }
    
    #Get the Index how?
    $index = $array.indexof("info4")
    
    #Output it how?
    Write-Host $array.row($index).column(1) "," $array.row($index).column(5)
    

    Thanks in advanced

    /Lars

  • #21844
    Profile photo of Tim Pringle
    Tim Pringle
    Participant

    Hi nOrphf

    I'm finding the pseudo code a bit confusing. Can you maybe give me an example of the data source and a step by step of how it would work?

    From what i can see you're using a multidimensional array, but don't really follow what the purpose of this is. It sounds like this may be something better done through a combination of an object and array, but I can't get my head round exactly what the purpose of this is.

  • #21845
    Profile photo of Dave Wyatt
    Dave Wyatt
    Moderator

    It looks like you're trying to create a multidimensional array here, which is technically possible, but not recommended. A much better solution would be to create an array of objects, where the "columns" of your current code become the properties of each object. For example:

    $array =  ForEach ($row in $DataSource) {
        [pscustomobject] @{
            Property1 = 'Info1'
            Property2 = 'Info2'
            Property3 = 'Info3'
    
            # etc
        }
    }
     
    #Get the Index how?
    
        # DW - Do you really need the index?  Or is it sufficient to just find the object you're
        # interested in, without being concerned with the index in the array?
        
    #Output it how?
        # Do you mean output it to the screen?  There are several options, which depend on where
        # this code lives.  If you're just running these commands at a console prompt, you don't
        # have to explicitly output anything; it'll go to the screen anyway using PowerShell's
        # default formatting behavior.  (Table for objects with 4 or fewer properties, list format
        # for 5+.)
    
        # This line takes care of both needs:  Finding the object you're interested in, and
        # outputting it with that default behavior.
    
    $array | Where-Object { $_.Property1 -eq 'Info1' }
    
    
  • #21846
    Profile photo of nOrphf
    nOrphf
    Participant

    Hi

    Yes, maybe I should have started with telling what the big picture is 🙂

    My datasource is a Get-MsolUser to get all users in office 365.
    I then later do a get-ADUser to get current AD users, and there status.

    I then, as it is now, want to list all the info int one csv file where I will match the UPN so both AD info and Office 365 info for each info is on one line in the cvs file, and send it to an e-mail.
    I am just thinking, isn't there an out–HTML og convert-html I might do that instead and send the info directly in the mail.

    This is what my script looks like but the indexof doesn't work, so the out-put don't have all the details, which is build on a script found on the net. Maybe it the completely wrong way to attack it, so feel free to suggest other approaches.

    #[System.Collections.ArrayList]$Office365Info = @()
    [Array]$Office365Info = @()
    # Define Hashtables for lookup
    $Sku = @{
    	"DESKLESSPACK" = "Office 365 (Plan K1)"
    	"DESKLESSWOFFPACK" = "Office 365 (Plan K2)"
    	"LITEPACK" = "Office 365 (Plan P1)"
    	"EXCHANGESTANDARD" = "Office 365 Exchange Online Only"
    	"STANDARDPACK" = "Office 365 (Plan E1)"
    	"STANDARDWOFFPACK" = "Office 365 (Plan E2)"
    	"ENTERPRISEPACK" = "Office 365 (Plan E3)"
    	"ENTERPRISEPACKLRG" = "Office 365 (Plan E3)"
    	"ENTERPRISEWITHSCAL" = "Office 365 (Plan E4)"
    	"STANDARDPACK_STUDENT" = "Office 365 (Plan A1) for Students"
    	"STANDARDWOFFPACKPACK_STUDENT" = "Office 365 (Plan A2) for Students"
    	"ENTERPRISEPACK_STUDENT" = "Office 365 (Plan A3) for Students"
    	"ENTERPRISEWITHSCAL_STUDENT" = "Office 365 (Plan A4) for Students"
    	"STANDARDPACK_FACULTY" = "Office 365 (Plan A1) for Faculty"
    	"STANDARDWOFFPACKPACK_FACULTY" = "Office 365 (Plan A2) for Faculty"
    	"ENTERPRISEPACK_FACULTY" = "Office 365 (Plan A3) for Faculty"
    	"ENTERPRISEWITHSCAL_FACULTY" = "Office 365 (Plan A4) for Faculty"
    	"ENTERPRISEPACK_B_PILOT" = "Office 365 (Enterprise Preview)"
    	"STANDARD_B_PILOT" = "Office 365 (Small Business Preview)"
    	}
    		
    # The Output will be written to this file in the current working directory
    $OfficeInfo = "C:\Service\Office_365_Licenses.csv"
    $ADInfo = "C:\Service\Active_Directory_Users.csv"
    
    # Connect to Microsoft Online
    Import-Module MSOnline
    $UserName = "admin@xxxx.onmicrosoft.com"
    $SecurePassword = "" | ConvertTo-SecureString -AsPlainText -Force
    $Credentials = New-Object System.Management.Automation.PSCredential -ArgumentList $UserName, $SecurePassword
    Connect-MsolService -Credential $Credentials
    
    write-host "Connecting to Office 365..."
    
    # Get a list of all licences that exist within the tenant
    $licensetype = Get-MsolAccountSku | Where {$_.ConsumedUnits -ge 1}
    
    # Loop through all licence types found in the tenant
    foreach ($license in $licensetype) 
    {	
    	# Build and write the Header for the CSV file
    	$headerstring = "DisplayName;UserPrincipalName;AccountSku"
    	
    	foreach ($row in $($license.ServiceStatus)) 
    	{
    		
    		# Build header string
    		switch -wildcard ($($row.ServicePlan.servicename))
    		{
    			"EXC*" { $thisLicence = "Exchange Online" }
    			"MCO*" { $thisLicence = "Lync Online" }
    			"LYN*" { $thisLicence = "Lync Online" }
    			"OFF*" { $thisLicence = "Office Profesional Plus" }
    			"SHA*" { $thisLicence = "Sharepoint Online" }
    			"*WAC*" { $thisLicence = "Office Web Apps" }
    			"WAC*" { $thisLicence = "Office Web Apps" }
    			default { $thisLicence = $row.ServicePlan.servicename }
    		}
    		
    		$headerstring = ($headerstring + ";" + $thisLicence)
    	}
    
    	# Gather users for this particular AccountSku
    	$users = Get-MsolUser -all | where {$_.isLicensed -eq "True" -and $_.licenses[0].accountskuid.tostring() -eq $license.accountskuid}
    
    	# Loop through all users and write them to the CSV file
        
    	foreach ($user in $users) {
    		
    		$OutPutObj = New-Object -TypeName psobject
            $OutPutObj | Add-Member -MemberType NoteProperty -Name DisplayName -Value $user.displayname
    		$OutPutObj | Add-Member -MemberType NoteProperty -Name upn -Value $user.userprincipalname
    		$OutPutObj | Add-Member -MemberType NoteProperty -Name AccountSku -Value $Sku.Item($user.licenses[0].AccountSku.SkuPartNumber)
    
    		foreach ($row in $($user.licenses[0].servicestatus)) {
    			
    			# Build data string
    			$OutPutObj | Add-Member -MemberType NoteProperty -Name Extra$($i) -Value $row.ProvisioningStatus
    
    			}
    		
    		$Office365Info += $OutPutObj
    	}
    }
    Out-File -FilePath $ADInfo -InputObject "Active Directory;;;;;Office365" -Encoding UTF8
    Out-File -FilePath $ADInfo -InputObject "DisplayName;UserPrincipalName;Aktiv;Expired;;$($headerstring)" -Encoding UTF8 -Append
    $adusers = Get-ADUser -SearchBase "OU=Users,OU=01-xxxx Accounts,OU=\# xxxx,DC=xxxx,DC=local" -Filter * -Properties Name,UserPrincipalName,Enabled,AccountExpirationDate
    
    foreach ($aduser in $adusers)
    {
        $Index = [Array]::indexof($Office365Info,$aduser.UserPrincipalName)
        
        $index
        if ($Index -ne -1){
            #$Office365Info[$index]
        }else{
            Out-File -FilePath $ADInfo -InputObject "$($aduser.Name);$($aduser.UserPrincipalName);$($aduser.Enabled);$(if ($aduser.AccountExpirationDate -ne $null){($aduser.AccountExpirationDate.ToLongDateString())});;$($Office365Info[$aduser.UserPrincipalName].DisplayName)" -Encoding UTF8 -append
        }
    }
    
    
    Send-MailMessage -Attachments $OfficeInfo, $ADInfo  -Body "Vedhæftet er list over brugere i Office 365 og Active Directory" -From "" -SmtpServer "xxxx.mail.protection.outlook.com" -Subject "Månedlig brugeroversigt" -To "" -Encoding UTF8
    
    write-host ("Done")
    Get-PSSession | Remove-PSSession -Confirm $false
    
  • #21847
    Profile photo of Dave Wyatt
    Dave Wyatt
    Moderator

    I see. Here again, I probably wouldn't worry about finding the index (though that can be done), since all you're using the index to do is obtain a reference to the object in the array at that location. For that, Where-Object works just fine:

    $officeuser = $Office365Info | Where-Object { $_.upn -eq $aduser.UserPrincipalName }
    

    Then in the rest of your code in that loop, you can refer to $aduser and $officeuser.

    Edit: I should probably mention that this is a very inefficient approach, and you might notice some slowness if you're dealing with thousands of accounts. If speed is a concern, I would modify $Office365Info to be a hashtable instead of an array, with the User Principal Name as the hashtable's key property. Once that's done, you can grab the reference to the matching office object without having to do an O(n) search of the array every time through the loop. That lookup would look something like this (but this won't work without modifying some of the other code as well):

    $officeuser = $Office365Info[$aduser.UserPrincipalName]
    
  • #21870
    Profile photo of nOrphf
    nOrphf
    Participant

    Ahhh thank you very much! – No I definitely don't need the index, this is much better...

    It's for a monthly run, so time is not that important here, so I will leave it with the array for now, but keep it in mind, so thanks for the suggestion.

    Regards Lars

    EDIT: An extra question – How can I delete a row from the array?

  • #21876
    Profile photo of Dave Wyatt
    Dave Wyatt
    Moderator

    There are a couple of options there. You can use Where-Object again:

    $Office365Info = $Office365Info | Where-Object { $_ -ne $officeuser }
    

    Or you can just use the -ne operator; comparison operators act as filters when the left operand is a collection:

    $Office365Info = $Office365Info -ne $officeuser
    

    If you decide to dive a bit deeper into the .NET Collections classes, you can use ArrayList or List, both of which have Remove methods:

    $arrayList = New-Object System.Collections.ArrayList
    
    # Populate the list; we'll assume $someObject is in it.
    
    $arrayList.Remove($someObject)
    

    Edit: If you wind up going with the hashtable approach I mentioned earlier, it also has a Remove method. In that case, you would pass in the userprincipal name that is being used as the hashtable's key.

  • #21879
    Profile photo of nOrphf
    nOrphf
    Participant

    Perfect! Thank you so much!

    Regards Lars

  • #22005
    Profile photo of Dan Potter
    Dan Potter
    Participant

    Seems a bit long winded. I wrote this before I implemented a backend sql db for provisioning.

    function msol-info{

    foreach($upn in $upns){

    $licenses = ((get-msoluser -userprincipalname $upn).licenses |select -last 1).servicestatus

    $RMS_S_ENTERPRISE = $licenses | select -index 0
    $OFFICESUBSCRIPTION = $licenses | select -index 1
    $MCOVOICECONF = $licenses | select -index 2
    $SHAREPOINTWAC = $licenses | select -index 3
    $SHAREPOINTENTERPRISE = $licenses | select -index 4
    $EXCHANGE_S_ENTERPRISE = $licenses | select -index 5

    $obj = New-Object PSObject

    $obj | Add-Member -MemberType NoteProperty -Name "UPN" -Value $full
    $obj | Add-Member -MemberType NoteProperty -Name "RMS" -Value $RMS_S_ENTERPRISE.provisioningstatus
    $obj | Add-Member -MemberType NoteProperty -Name "OFFICEPP" -Value $OFFICESUBSCRIPTION.provisioningstatus
    $obj | Add-Member -MemberType NoteProperty -Name "Lync" -Value $MCOVOICECONF.provisioningstatus
    $obj | Add-Member -MemberType NoteProperty -Name "OFFICEOL" -Value $SHAREPOINTWAC.provisioningstatus
    $obj | Add-Member -MemberType NoteProperty -Name "Sharepoint" -Value $SHAREPOINTENTERPRISE.provisioningstatus
    $obj | Add-Member -MemberType NoteProperty -Name "Exchange" -Value $EXCHANGE_S_ENTERPRISE.provisioningstatus
    $obj | Add-Member....AD Account info..

    $obj

    }

    }

You must be logged in to reply to this topic.