Avatar of meirionwyllt
meirionwyllt
 asked on

Need help with an PowerShell command to extract user attributes

Hello, what I need to do is get a CSV file with two columns - the first with a list of all users in AD, and the other column with the value of the AD attribute 'userSharedFolder' for that user.

I'm a PowerShell learner.  Here's what I have so far...

Get-ADUser –Filter * -prop cn,userSharedFolder | select cn,userSharedFolder  > \\server\share\IT\Scripts\AD\Ldrive.csv

Now, all the info is there in the resulting CSV, but there are a few formatting issues.  Firstly, the output is not on separate columns in Excel - it's all on the same column, just with loads of white space between the username and the userSharedFolder.  How do I get them onto separate columns, with no white space?  Secondly, I have some usernames that have a leading zero, but Excel sees it fit to remove this digit.  Can you stop this from happening at the script level, or would this be an Excel thing?

Any other tips to improve the above command would be welcomed.

Thanks.
PowershellMicrosoft ExcelActive Directory

Avatar of undefined
Last Comment
meirionwyllt

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
footech

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
meirionwyllt

ASKER
Hi, thanks for that.  What I have now are lines of CSV with one cell on each row, in the following format...

cn,"userSharedFolder"

I'm guessing the existence of the comma is a Delimiter issue isn't it.  Am I able to modify the command to make Excel treat this as two columns?  I realise I can do an Insert Object from within Excel but I was just wondering if there is a way to do this from the command.

Also, is there a way of removing the quotes around the userSharedFolder entries?

Thanks.
SOLUTION
Antzs

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
footech

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
meirionwyllt

ASKER
Adding the -Encoding ACSII parameter and then using Excel's 'Text to Columns' button (rather than doing an import) has given me what I need, because it gets rid of the quotes and when I select Text rather than General it keeps the leading zero as well.  Thanks.

Anthony - I tried the script you posted but it kept throwing the error...

Select-Object : A parameter cannot be found that matches parameter name 'Path'.
At \\server\share\IT\scripts\AD\Export_AD_Users_to_CSV.v1.0.ps1:95 char:12
+ Export-Csv -Path "$csvfile" -NoTypeInformation
+            ~~~~~
    + CategoryInfo          : InvalidArgument: (:) [Select-Object], ParameterBindingException
    + FullyQualifiedErrorId : NamedParameterNotFound,Microsoft.PowerShell.Commands.SelectObjectCommand

As a test I changed line 30 to...

$csvfile = "C:\TG\AllADUsers_$logDate.csv"

just to get rid of any possibility of problems with UNC paths and/or spaces in the path.  But it still gave the same error.

Although I've now been able to achieve what I wanted in my original post, I can see the benefit of using this script in the future so it would be nice to get this working.  Do you know what might be the problem?  I have full permissions to both the UNC path and the local folder.

Thanks.
Antzs

Just change those in the red box.  It should work.

Sript
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
meirionwyllt

ASKER
Okay I know what's happening here.  I was doing the above three things correctly, but the problem was when I was editing the Select-Object section.  I had to fiddle with this section to add the 'userSharedFolder' label.  In order to keep the resulting spreadsheet clean I hashed out everything I didn't want.  Among the lines that I didn't want was the final 'lastlogondate' line, which has a pipe at the end of it, which is crucial to the script being able to run.

This was my mistake of course, but this script should be slicker.  The '@Label...' entries should all be on their own individual lines, which would allow them to be switched off and on as required.  The same is true for the $_.GivenName entry - you also can't hash this out without breaking the whole script.

But until I figure out how to do that I'll just delete the rows from Excel.