Increase Width Of One or More Columns

PowerShell Help
1

Friends,
I have a script that that works ok except that in two columns, the data is truncated.
This is the script:

Import-Module activeDirectory 
    [void][Reflection.Assembly]::LoadWithPartialName('Microsoft.VisualBasic') 
    $title = 'Get Office' 
    $msg = 'Enter    MarinesOfficeID  NUMBER'
    $InputUser = [Microsoft.VisualBasic.Interaction]::InputBox($msg, $title) 
    $user=$InputUser.trim() 
    $Number = $user
    Get-ADUser -Filter "MarinesOfficeID -like $Number" -properties *  |
    select Samaccountname, Office, MarinesOfficeID, City, State| format-table

Here is the output, as you can see there are some truncations.
They like this format, one line per person.

Samaccountname     Office MarainesOfficeID City        State
Elizabeth.Miller   ABCDE  123456       Kansas City KS    
Jerry.Macaguire    ABCDE  123456       Kansas City KS    
Mary.Jordan        ABCDE  123456       Kansas City KS    
Charles.Saddle     ABCDE  123456       Kansas City KS    
Christopher.Gal... ABCDE  123456       Kansas City KS    
Lamont.Jones       COOLA  123456       St Louis    MO
Jennifer.Peabody   MOL... 123456       Jones Burro KS    
Christopher.Bau... LO-... 123456       Kansas City KS

I would prefer not to use -Autosize because this produces very long lists, using -Autosize would delay the appearance of the data, they want the console to start rolling very quickly.
So I tried to control the width of the columns, but I cannot get it right. I would appreciate it if you would help fix this:

Import-Module activeDirectory 
    [void][Reflection.Assembly]::LoadWithPartialName('Microsoft.VisualBasic') 
    $title = 'Get Office' 
    $msg = 'Enter    MarinesOfficeID  NUMBER'
    $InputUser = [Microsoft.VisualBasic.Interaction]::InputBox($msg, $title) 
    $user=$InputUser.trim() 
    $Number = $user
    Get-ADUser -Filter "MarinesOfficeID -like $Number" -properties *  |  
   

 select Samaccountname, Office, MarinesOfficeID, City, State, DistinguishedName


 [pscustomobject] @{ SamAccountName='$_.SamAccountName'}; 
 [pscustomobject] @{ Office=$_.Office;Width=10}; 
 [pscustomobject] @{ MarinesOfficeID=$_.MarinesOfficeID}; 
 [pscustomobject] @{ City=$_.City}; 
[pscustomobject] @{ State=$_.State}; 
[pscustomobject] @{ DistinguishedName=$_.DistinguishedName} |


format-Table
@{name='SamAccountName';expression={$_.SamAccountName;Width=24}},
@{name='Office';expression={$_.Office;Width=24 }},
@{name='MarinesOfficeID';expression={$_.MarinesOfficeID;Width=10}},
@{name='City';expression={$_.City;Width=15}},
@{name='State';expression={$_.State;Width=2}},
@{name='DistinguishedName';expression={$_.DistinguishedName;Width=34}}

Thanks in advance for you help on this, I have spent hours and hours on it.

2

Do you mean long or wide lists?

What purpose do you actually want to serve with this output?

You have to move the width infomation outside of the expression script block.

@{name='SamAccountName';expression={$_.SamAccountName};Width=24},

BTW: The maximum length of a sAMAccountName is 20 characters. What if distinguished name is longer than 34 characters?

3

Olaf, thank you for your reply.
This script sometimes will produce long lists of people who match on OfficeID, one person per line. This is good, one person per line.
We have thousands of different OfficeId(s) . Some OffceId(s) match several hundred people, so the console might roll for a minute or more.
When the script starts, It takes 10 seconds to start producing lines of people. But if I use -Autosize, Powershell assembles the whole list before it shows the whole list. This delay might take a minute or more before any output is seen.
The purpose of this Script is to find what Office matches with the input OfficeID.
In our very fast paced work environment, we need to know what Office matches an OfficeID. A team of lowly Active Directory Administrators like myself need this information ten or twenty times every day.
When I use this script , I stop the script with a Cntrl + C when I have seen enough, when I see the good Office numbers I need. They are so lax and so undisciplined around here, some people put junk-data in the Office values, so I am looking for the good ones.
All I really need in this scenario is the good Office values . The other columns are used as a confirmation for the proper use of Office on that record, by state and/or Distinguished Name
That way, I can quickly tell which ones are bad and which ones are good.

I will look at your suggested format and test it. I will get back to you after I keep the boss happy by doing some things.

Yes, the SamAccountName limit is 20 characters. Thank you for reminding me. I recently wrote a script to add new special users and I had to truncate some names down to 20.

If the Distinguished name is greater than 34 I will adjust this number or experiment with the right or left justify until I get it right. Actually, the output when using -Autosize looks great. I will probably use that as a model.

4

Hmmm 
 that’s what I expected. :smirk: In my opinion that’s a bad, error prone and inefficient way. How do you identify the good ones? Wouldn’t it be better to let PowerShell do the tedious work? That’s what we have it for. :wink:

On the other hand 


They shouldn’t have the oportunity to do this. Ideally the should only have a limitted choice to choose from. So there’s no chance of getting arbitrary, unwanted, invalid junk in this AD attribute.

5

Olaf, We used to have a master list of the OfficeId (s) which also contained the authorized Office. But no more. Nobody wanted to keep the list current.
The correct Office is not stored in Active Directory; Except that somebody can check all the existing users/people. -Which is what I am doing with this script.
I use this script to show me what the majority of the records have for Office. I can see the oddballs at a glance. The State and City and DistinguishedName help clarify which ones have the correct Office.
So I look to see what most people have.
The Office that most people/users have is the right one. Ha! I know this is bad, but right now it is the best I can do.

In a perfect world the people with Admin Rights in the small offices would not put junk data in the Office Attribute.

If they ever get that Excel Spread Sheet going again that contains over three-thousand OfficeId (s) with the corresponding authorized/correct Office, - Could a script check this Excel SpreadSheet? Could a PowerShell Script check an Excel SpreadSheet and get the correct Office? This would be a part of our Script to Add New Users/People in Active Directory.

6

OK. I have an idea of what you’re dealing with and I’m sorry for you. :wink:

Anyway 


In this case you could use Group-Object for the property OfficeID. This way would PowerShell do the tedious work for you and you would have a sorted list and you could tell the difference with only one look without the need to scann hundrets or thousands of list elements rushing over your screen. :wink: :wink: :stuck_out_tongue:

That’s what I meant. In the process of creating new users or assigning the OfficeIDs they should have only the choice to choose the OfficeID they have rights for and they are menat to use. They could still choose a wrong OfficeID but it wouldn’t be something random at least.

And of course I don’t know how your OfficeIDs are related to the combination of State, City and maybe an OU or something like this but if there is a certain logic behind you could even determine the OficeID automatically.

OK. Have a nice Weekend! :wave:t4: :love_you_gesture:t4: :slightly_smiling_face:

7

Olaf, I looked it up, and is seems easy to get information from an Excel Spreadsheet. I can’t wait to get a script working like this so I can show the boss. I did not think about that.

And I had good success after another hour or so struggling to get it right. At first I was using -properties * But it took 15 seconds before the console started rolling.

Then I listed the properties and now it begins rollilng in 3 seconds.
Each AD Record takes only one line.
It is faster than -AutoSize. It is easy to read. It is useable.
Here is my final:

Import-Module activeDirectory 
    [void][Reflection.Assembly]::LoadWithPartialName('Microsoft.VisualBasic') 
    $title = 'Get Office' 
    $msg = 'Enter    UsdaOfficeID  NUMBER'
    $InputUser = [Microsoft.VisualBasic.Interaction]::InputBox($msg, $title) 
    $user=$InputUser.trim() 
    $Number = $user
    Get-ADUser -Filter "uSDAOfficeID -like $Number" -properties Samaccountname, Office, uSDAOfficeID, City, State, DistinguishedName |
    Ft @{name='SamAccountName';expression={$_.SamAccountName};Width=20},
@{name='Office';expression={$_.Office};Width=24},
@{name='uSDAOfficeID';expression={$_.uSDAOfficeID};Width=10},
@{name='City';expression={$_.City};Width=15},
@{name='State';expression={$_.State};Width=2},
@{name='DistinguishedName';expression={$_.DistinguishedName};Width=85}
8

If the Excel Spreadsheet is actually a CSV file PowerShell has the cmdlet Import-Csv already built in - yes. :wink: 
 but with the additional module from Doug Finke ImportExcel you can even read native Excel files.

That’s what we all here for - to elevate each other. :wink: :+1:t4: :love_you_gesture:t4:

You don’t need that. PowerShell will import the module automatically if needed. A better option would be to use

#Requires -Modules ActiveDirectory

That would prevent the script from running when the required module is not available.

Here you can read more about:

? Why two variables? :wink:

Please do not use aliasses in scripts or in forums as they are very unpolite and make your code harder to read.

You may read

Edit:
For your next experiment you should try this:

Get-ADUser -Filter "uSDAOfficeID -like $Number" -properties Samaccountname, Office, uSDAOfficeID, City, State, DistinguishedName |
    Group-Object -Property uSDAOfficeID |
        Sort-Object -Property Count -Descending |
            Select-Object -First 10

It may take a moment until you see a result but I think it’s worth waiting. :wink:

9

Olaf, thank you for all the good pointers. I am very glad to receive such good and useful information. I promise I will use all your suggestions because I can easily see they make the script work better and also make it better looking.
I am spending many hours of my own time trying to write scripts to help us here at work, and I do try to learn the fundamentals from some books, among these are “Powershell In Depth” by Don Jones, et al.
From page 135:

Here’s a one-line example:

PS C:> get-process | ft Name,ID,@{name=‘VM’;expression={$_.VM /
➄ 1MB};formatstring=‘N2’;align=‘right’;width=8}
Name Id VM ---- – -conhost 1100 78.89 conhost 1820 46.23 conhost 2532 40.99 csrss 324 43.12 csrss 372 72.04 dfsrs 1288 347.19 dfssvc 1468 34.40
This is an awesome trick. But be sure to read section 9.4 so that you can avoid mistakes folks commonly make when employing this and other formatting tricks.

And I am finally starting to learn that a hash-table is a kind of variable. I need to understand related ideas so I can learn to Splat.
Next, I plan to concentrate on learning about how Acive Directory and LDAP work together because I am convince this will help me understand so I can write better scripts. LDAP - I just don’t understand it, how it relates to Active Directory. And I don’t know the best place to learn it.

10

Please don’t hijack other peoples threads. If you have a question regarding the same topic you should create a new thread for yourself and add a link to the thread you’re refering to to your thread. :point_up_2:t4:

Thanks in advance. :+1:t4:

Do you have an actual question?

11

Need single quotes around $number

"uSDAOfficeID -like ‘$Number’”
1 Like