Link to home
Start Free TrialLog in
Avatar of cawasaki
cawasaki

asked on

powershell script to export user information

hello,

i need powershell command/script to export in csv file the ad user information.

the script must select only user with attribute title="test".

and export all this attribute in this order:

givenName,sn,displayname,department,title,Test,whencreated,AccountExpires,manager

the test attribute is an attribute i have add in my Active directory.

thanks
Avatar of Raheman M. Abdul
Raheman M. Abdul
Flag of United Kingdom of Great Britain and Northern Ireland image

get-item -Path "cn=Sarah French" -Properties givenName,sn,displayname,department,title,Test,whencreated,AccountExpires,manager
 | export-csv "c:\temp\userinfo.csv



Or

Get-ADUSer  samAccountnameHere  -Properties givenName,sn,displayname,department,title,Test,whencreated,AccountExpires,manager
 | export-csv "c:\temp\userinfo.csv
Here is also another way to accomplish what your needing...

Get-ADuser -filter * -properties * | where-object {$_.Title -eq "test"} | Select-Object givenName, sn, displayname, department, title, whencreated, AccountExpires, manager | export-csv "c:\exportuser.csv"

- This script will look for All Users in the domain with all properties
- Find only users that have "Test" in the Title
- Select the above attributes
- Export to CSV file


Hope this helps!
With Quest AD cmdlets, you can try..
Get-QADUser -SizeLimit 0 -All | ? {$_.Title -eq "test"} | Select givenName,sn,displayname,department,title,Test,whencreated,AccountExpires,manager | Export-CSV C:\report.csv -NTI

Open in new window

Avatar of cawasaki
cawasaki

ASKER

hi

@Subsun, the scrpt work and after complete, the csv file is empty.

@Spec01, the script work, but 2 problem:
my special attribute i have add to AD not export, i have this on the export:
Microsoft.ActiveDirectory.Management.ADPropertyValueCollection
and AccountExpires attribute in export i have not a good form, i have this: "9223372036854775807"

thanks
hi

@Subsun, i have modify a little and its work :)

Get-QADUser -SizeLimit 0 -IncludeAllProperties | ? {$_.Title -eq "test"} | Select
givenName,sn,displayname,department,title,test,whencreated,AccountExpires,manager | Export-CSV C:\report.csv -encoding UTF8

Open in new window

-All is actually the alias for –IncludeAllProperties. Anyways I am glad you are able to get the desired result..
hi Subsun,

after test the script, i need to now if this is possible:

the displayname is in this form: john Smith (Microsoft) where Microsoft is the john company.

I need in the export, to export only John Smith in displayname, and export the company without the () after displayname.

for exemple:

givenName,sn,displayname,Company......
Smith,John,John Smith,Microsoft......

and finaly, i need to send the csv file by email.

thanks for help
Try...

Get-QADUser -SizeLimit 0 -IncludeAllProperties | ? {$_.Title -eq "test"} | 
Select givenName,sn,`
 @{N="displayname";E={(($_.displayname -split '\(')[0]).Trim()}},`
  @{N="Company";E={(($_.displayname -split '\(')[1] -replace "\)").Trim()}},`
   department,title,test,whencreated,AccountExpires,manager | 
Export-CSV C:\report.csv -encoding UTF8

Send-MailMessage -From "From@domain.com" -To "To@domain.com" -Attachments "C:\report.csv" -SmtpServer "SMTP.Domain.com" subject "Report mail"

Open in new window

nice i will test immediatly :)
ok, i have test it, the script work.

in email object, i receive subject, i think in script you have miss the - in subject.

its possible:

1- in csv file i have this:

givenName,"sn","displayname","Company","Department","Title","test","whenCreated","AccountExpires"

its possible to change it in the export of script to other think like:

name,lastname,.......

2-its possible to put a date in the csv file report like report_220713.csv?

thank you for help
Yes there is a typo in parameters.. it should be -subject..

To export the properties in required order you need to change the order of properties after select..

For example.. in the script if it is Select givenName,sn then the csv file will be in same order givenName,sn

In script if it is Select sn,givenName then the csv file will be like sn,givenName


And if you are concerned about the quotation marks then If you are going to open it in excel then it won’t be a problem..

Use the following code to change the report file name..
Export-CSV "c:\report_$(Get-date -f ddMMyy).csv"
ok thanks,

what i say, is in export file i need to see this:

name,lastname and not givenname,sn,......

do you understand what i mean?

thanks
ok..
Replace  givenname,sn

to
@{N="name";E={$_.givenname}},@{N="lastname";E={$_.sn}}

Open in new window

hi its ok, but still a small problem:

1-in export file, its possible to delete this line, its the first:

#TYPE Selected.Quest.ActiveRoles.ArsPowerShellSnapIn.Data.ArsUserObject

2-Export-CSV "c:\report_$(Get-date -f ddMMyy).csv"

and after that

Send-MailMessage -From "From@domain.com" -To "To@domain.com" -Attachments "C:\report.csv" -SmtpServer "SMTP.Domain.com" subject "Report mail"

the attachement file name will change, its possible to adapt the script?
==> like   -Attachments "$report"

thanks
$File = "c:\report_$(Get-date -f ddMMyy).csv"

Get-QADUser -SizeLimit 0 -IncludeAllProperties | ? {$_.Title -eq "test"} | 
Select @{N="name";E={$_.givenname}},@{N="lastname";E={$_.sn}},`
 @{N="displayname";E={(($_.displayname -split '\(')[0]).Trim()}},`
  @{N="Company";E={(($_.displayname -split '\(')[1] -replace "\)").Trim()}},`
   department,title,test,whencreated,AccountExpires,manager | 
Export-CSV $File -encoding UTF8 -NTI

Send-MailMessage -From "From@domain.com" -To "To@domain.com" -Attachments $File -SmtpServer "SMTP.Domain.com" subject "Report mail"

Open in new window

i start the script, and after 15mn it have display all user in screen and stop at this:

cmdlet Export-Csv at command pipeline position 1
Supply values for the following parameters:
InputObject:
Try..
$File = "c:\report_$(Get-date -f ddMMyy).csv"

Get-QADUser -SizeLimit 0 -IncludeAllProperties | ? {$_.Title -eq "test"} | 
Select @{N="name";E={$_.givenname}},@{N="lastname";E={$_.sn}},`
 @{N="displayname";E={(($_.displayname -split '\(')[0]).Trim()}},`
  @{N="Company";E={(($_.displayname -split '\(')[1] -replace "\)").Trim()}},`
   department,title,test,whencreated,AccountExpires,manager | Export-CSV $File -encoding UTF8 -NTI

Send-MailMessage -From "From@domain.com" -To "To@domain.com" -Attachments $File -SmtpServer "SMTP.Domain.com" subject "Report mail"

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of SubSun
SubSun
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
its work very well and very fast :)

for manager attribute, i get in this form: cn=......,ou=...,dc=...   its possible  to get only displyname of manager like John Smith?

thanks you
Replace manager  with
@{N="manager";E={(Get-QADUser $_.manager).Name}}

Or if you want displayname

@{N="manager";E={(Get-QADUser $_.manager).displayname}}
very good :).

and last question, its hard to export the file directly to excel file?
To write to excel file you need to construct one... CSV file you can open directly in excel..
thanks