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)
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.
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' }
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
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:
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):
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?
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.
