Creating CSV from Array List; Need One Row Per Item, Even if Duplicate.

PowerShell Help
1

Hello Everyone,

I’m back again with another question of how to make my script a little bit better.

Currently I have a script that finds all the mailboxes in my environment, and then finds all of the mobile devices per mailbox that have touched the tenant in the past 30 days. The script may be a little slow, but works find for what I thought I needed it to do. The new request from the security team is that I have a complete single line for each mobile device, not a single line per “user”.

 

This is what the output currently looks like: (All Names, UPNs and Emails have been modified for privacy)

Name UPN Email Device Friendly Name Device ID Device Model Device OS Client Type Client Version Last Success Sync
A, A 80250@connect.com arjuna@automotive.in DC140EBF2A87344C89407D5E5A4B8B00 Outlook for iOS and Android 27 Outlook 1 Thursday, August 13, 2020 8:46:46 AM
ADLA, C 80299@connect.com c.adla@company.com iPhone 6 M2DPM9S0G137J20V1RBOVSFFIS iPhone7C2 iOS 12.4.8 16G201 EAS 16.1 Thursday, August 13, 2020 2:38:02 PM
Adnin, L 80241@connect.com l.adnin@company.com a40eea

P1206

 SEC1B1766437B2B9

9DEE3B12285649C684BC3F72ED003932

 SM-A405FN

T4J78EA#ABF

 Android 9

WINDOWS

 EAS

Outlook

 16.0

1.0

 Monday, August 3, 2020 7:53:02 AM

Wednesday, August 12, 2020 12:40:02 PM
Adolphs, S 80110@connect.com SAdolphs@it.com iPhone XS Q31RUCB7KT1A18IGH6TBM7JTQ0 iPhone11C2 iOS 13.6 17G68 EAS 16.1 Thursday, August 13, 2020 2:22:52 PM
Alcantara de Souza, D 80224@connect.com dasouza@it.com.br SM-T385M

LM-X210

 androidc377681850

LGMCjD9KNQHfBFwq

 SM-T385M

LM-X210

 Android 9

Android7.1.2

 EAS

EAS

 16.0

14.1

 Thursday, August 13, 2020 9:50:21 AM

Thursday, August 13, 2020 12:38:34 AM
Alcantara, V 02429@connect.com V.Alcantara@company.com C6623C76D9103B1EA90D9011658F1371 Outlook for iOS and Android 10 Outlook 1 Thursday, August 13, 2020 2:34:13 PM
Alcaraz, A 80345@connect.com a.alcaraz@fasteners.com a5y17ltexx SEC14875E11D358F SM-A520F Android 8 EAS 16 Friday, July 17, 2020 5:58:16 PM
 

They would like to see duplicate Name, UPN, and Email entries if there are multiple devices for that user.

 

Here is my code currently:

https://gist.github.com/rpm44/83ed168f66283fa09219348e0c96c48a

 

Thanks Everyone for your help!

Rob

2

Why don’t you use a nested loop?

Pseudo code:

foreach( $User in $UserList){
    $DeviceIDList = 'All devices of the current user'
    foreach($DeviceID in $DeviceIDList){
        [PSCustomObject]@{
            User = $User
            ID   = $DeviceID
        }
    }
}
3

Not sure how many mailboxes you’re working with, but this sounds like a opportunity for SQL to me, especially if the scripts take a long time to execute. Gather the raw data and use joins and grouping in SQL where it’s meant to do those operations rather than re-writing the scripts for security requests. If it’s not a large amount of mailboxes, you could also just attempt to group with Powershell:

$results = Import-CSV -Path C:\Users\rmartin\OneDrive\Documents\Mobile Devices v2.csv
$test = $results | Group-Object -Property DeviceId
4

Rob,

Thanks for the idea. I think I might just push back on them and have them use SQL to do the work. The original run for this script was over 10,000 users.

[quote quote=249392]Not sure how many mailboxes you’re working with, but this sounds like a opportunity for SQL to me, especially if the scripts take a long time to execute. Gather the raw data and use joins and grouping in SQL where it’s meant to do those operations rather than re-writing the scripts for security requests. If it’s not a large amount of mailboxes, you could also just attempt to group with Powershell:

$results = Import-CSV -Path C:\Users\rmartin\OneDrive\Documents\Mobile Devices v2.csv $test = $results | Group-Object -Property DeviceId [/quote]

5

In your code $Rollup is already an ArrayList. You should add items to the list using [void]$Rollup.Add($Item). $Rollup += $Item is extremely inefficient for 10,000 objects.

6

Can I also use $Rollup.Add($Item) > $Null instead of $Rollup += $Item?




            


            

              
              
              
            



            
          

          

            

              



              
                  
                  
              7
              
            

            

              
You could use any of the following:


$Rollup.Add($Item) > $Null
$Rollup.Add($Item) | Out-Null
[void]$Rollup.Add($Item)
$null = $Rollup.Add($item)