Help Formatting Output of a Script

Welcome Forums General PowerShell Q&A Help Formatting Output of a Script

Viewing 6 reply threads
  • Author
    Posts
    • #257381
      Participant
      Topics: 9
      Replies: 12
      Points: 155
      Rank: Participant

      Hello Everyone,

      I’m trying to find a way to get a full line for each item for this script.  I am pulling all of the users from corporate headquarters and getting mobile device information for any device they have connected with in the past 30 days.  It works great, but the security folks want to be able to sort the information better.  So instead of only having the Identifiable information for the user once, they would like each device to basically be it’s own line.

       

      Here is a screenshot of how the data is currently.

      Here is the code that I’m using:

       

      Thank you everyone for your help and time,

      Rob M

      • This topic was modified 1 month ago by grokkit. Reason: fixed image link
    • #257399
      Participant
      Topics: 16
      Replies: 1794
      Points: 3,300
      Helping Hand
      Rank: Community Hero

      Not really following what you are looking for. The data is by user, not by device. If you want to see how many devices instances there are, you could use grouping:

    • #257459
      Participant
      Topics: 9
      Replies: 12
      Points: 155
      Rank: Participant

      That’s not quite what I’m hoping to do, but instead what I need is if there is an instance of a device, I need the Name, UPN, and Email Field fully filled out.

      Each device in it’s own row I guess would be the simplest way to explain it.

      Thanks,

      Rob

    • #257819
      Senior Moderator
      Topics: 10
      Replies: 184
      Points: 980
      Helping Hand
      Rank: Major Contributor

      As far as PowerShell is concerned, when you import a CSV everything between two commas is a single value. Having multiple pieces of data inside of one cell is not expected behavior and PowerShell doesn’t have an automatic way to handle it. You could set up some logic to break up those values as discussed in this TechNet post. It’s hard to tell without the actual CSV file, but it looks like the data in those cells might be separated by newlines. If that’s true, you can do -split `n to break those cells into separate values, and then associate the separated values with the correct username &etc. That should give you a complete line for each device.

      A better solution would be to fix this information at the source, so that each device is listed with complete information by itself. Why is the CSV like this in the first place?

    • #257825
      Participant
      Topics: 9
      Replies: 12
      Points: 155
      Rank: Participant

      Thanks for that information Grokkit. I can explain the need for the CSV file, as well as the other method I have commented out at the top of the script currently.

      We have a user base of about 50K users, so generally if I’m only trying to look at one location of our company, I’ll pull the username data for that particular location rather than trying to use get-exomailbox -filter ….  it’s just quicker that way.  But there’s no reason I can’t pull the data on the fly another way if it allows me to have each device on it’s own row.

      The CSV file I use to populate the user data for the rest of the script only has 3 columns, UserPrincipalName, PrimarySMTPAddress, and DisplayName.

    • #257873
      Senior Moderator
      Topics: 10
      Replies: 184
      Points: 980
      Helping Hand
      Rank: Major Contributor

      Ok, so you’re more or less stuck with ingesting this file as your input as it is. You can help yourself by looking at the CSV in its raw format in Notepad or another text editor. You’ll probably see data that looks like this (based on your example):

      Even though it’s spread across multiple lines, this is only one row from your CSV. The newline characters that separate the multiple pieces of data inside cells are being preserved inside the double quotes and are reproduced literally when looking at the CSV as unformatted text.

      You won’t be able to process this directly because newline characters are also used to separate rows in the CSV, so if you treat newline as a delimiter you will get bad results when you import the file. However, once these values are stored in a PowerShell object you should be able to process each one and split it on the newline character to get the individual pieces.

      So, if you process each CSV row of data using a foreach loop, you could build new lines as you go (for each individual device discovered when you split the cells containing multiple values) and associate the username and other data with the device data, probably at the same time inside that foreach loop. With that much data it’ll probably take awhile, but it would definitely be faster than editing by hand.

      A simple version of the script would attempt to split every cell to find multiple values, but you could probably make it more efficient if you can be sure that some cells (such as “Name”) will not contain multiple values and exclude them from the split operation.

    • #257915
      Participant
      Topics: 1
      Replies: 85
      Points: 387
      Helping Hand
      Rank: Contributor

      Based on the current data screenshot, you can read in that file as a csv and parse the rows by splitting on newline characters:

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