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

Welcome Forums General PowerShell Q&A Creating CSV from Array List; Need One Row Per Item, Even if Duplicate.

Viewing 6 reply threads
  • Author
    Posts
    • #249374
      Participant
      Topics: 9
      Replies: 11
      Points: 151
      Rank: Participant

      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 [email protected] [email protected] DC140EBF2A87344C89407D5E5A4B8B00 Outlook for iOS and Android 27 Outlook 1 Thursday, August 13, 2020 8:46:46 AM
      ADLA, C [email protected] [email protected] iPhone 6 M2DPM9S0G137J20V1RBOVSFFIS iPhone7C2 iOS 12.4.8 16G201 EAS 16.1 Thursday, August 13, 2020 2:38:02 PM
      Adnin, L [email protected] [email protected] 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 [email protected] [email protected] iPhone XS Q31RUCB7KT1A18IGH6TBM7JTQ0 iPhone11C2 iOS 13.6 17G68 EAS 16.1 Thursday, August 13, 2020 2:22:52 PM
      Alcantara de Souza, D [email protected] [email protected] 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 [email protected] [email protected] C6623C76D9103B1EA90D9011658F1371 Outlook for iOS and Android 10 Outlook 1 Thursday, August 13, 2020 2:34:13 PM
      Alcaraz, A [email protected] [email protected] 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

    • #249386
      Participant
      Topics: 7
      Replies: 2458
      Points: 6,439
      Helping Hand
      Rank: Community MVP

      Why don’t you use a nested loop?

      Pseudo code:

      [/crayon]
    • #249392
      Participant
      Topics: 13
      Replies: 1753
      Points: 3,136
      Helping Hand
      Rank: Community Hero

      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:

    • #249395
      Participant
      Topics: 9
      Replies: 11
      Points: 151
      Rank: Participant

      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.

      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

    • #249404
      Participant
      Topics: 0
      Replies: 77
      Points: 343
      Helping Hand
      Rank: Contributor

      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.

    • #249410
      Participant
      Topics: 9
      Replies: 11
      Points: 151
      Rank: Participant

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

    • #249425
      Participant
      Topics: 0
      Replies: 77
      Points: 343
      Helping Hand
      Rank: Contributor

      You could use any of the following:

Viewing 6 reply threads
  • You must be logged in to reply to this topic.