Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies. Only from Platform Scholar.
Course Of The Month
3 days, 11 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Need help with an PowerShell command to extract user attributes
Posted on 2016-07-21
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\
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.
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\
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.
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
3
2
2
7 Comments
Active today
Instead of redirecting to a file with > \\server\share\IT\Scripts\
Get-ADUser –Filter * -prop cn,userSharedFolder | select cn,userSharedFolder | Export-CSV \\server\share\IT\Scripts\AD\Ldrive.csv -notype
Active today
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.
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.
Active today
I have been using the attached script and you could extract any AD Attribute info you need. Give it a try.
Just need to key in the $path, $SearchBase and $ADServer
Credit to the author who is listed in the powershell script.
Export_AD_Users_to_CSV.v1.0.txt
Just need to key in the $path, $SearchBase and $ADServer
Credit to the author who is listed in the powershell script.
Export_AD_Users_to_CSV.v1.0.txt
Active today
Export-CSV puts quotes around every field. This ensures that even if the field includes the same character which is used as the delimiter the field isn't broken up. When you open the file in Excel you won't see the quotes (when it's parsed properly).
Add the following parameter to Export-CSV.
I'm not sure why your instance appears to be generating a file with the encoding that Excel doesn't handle well. I can't remember if there was a change in different PS versions in this regard. Or maybe there a difference due to regional settings in Windows.
Add the following parameter to Export-CSV.
-Encoding ASCII
I'm not sure why your instance appears to be generating a file with the encoding that Excel doesn't handle well. I can't remember if there was a change in different PS versions in this regard. Or maybe there a difference due to regional settings in Windows.
Active today
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\
+ Export-Csv -Path "$csvfile" -NoTypeInformation
+ ~~~~~
+ CategoryInfo : InvalidArgument: (:) [Select-Object], ParameterBindingException
+ FullyQualifiedErrorId : NamedParameterNotFound,Mic
As a test I changed line 30 to...
$csvfile = "C:\TG\AllADUsers_$logDate
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.
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\
+ Export-Csv -Path "$csvfile" -NoTypeInformation
+ ~~~~~
+ CategoryInfo : InvalidArgument: (:) [Select-Object], ParameterBindingException
+ FullyQualifiedErrorId : NamedParameterNotFound,Mic
As a test I changed line 30 to...
$csvfile = "C:\TG\AllADUsers_$logDate
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.
Active today
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.
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.
Featured Post
Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies. Only from Platform Scholar.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
In this post we will be converting StringData saved within a text file into a hash table. This can be further used in a PowerShell script for replacing settings that are dynamic in nature from environment to environment.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month3 days, 11 hours left to enroll
630 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.