Solved

powershell export-csv

Posted on 2011-03-11
8
1,310 Views
Last Modified: 2012-05-11
Hello,

I am trying to put a bunch of user into csv and send it via email, but It only adds me one user into it, I am not sure why is it caused, any idea?

Many thanks
Get-QADUser -Enabled -SearchRoot 'xxx'| 
 %{  if (($_.manager -eq $null) -or ($_.accountexpires -eq $null)) `
 {
 
  $_ |  select name,samaccountname,@{N='Manager';E={$_.manager}  },@{N='AccountExpires';E={($_.accountexpires)}} | 
#out-file -filepath C:\subcontractorsExport.txt -encoding ASCII 
 Export-Csv -Path C:\subcontractorsExport.csv -Delimiter ";"
}
}
#Send-MailMessage -From "xx" -To "xx" -Smtpserver "server" -Subject "Export of Subcontractors" -Body "Export of subcontractors without manager or accountexpires" -Attachments "C:\subcontractorsExport.csv"

Open in new window

0
Comment
Question by:Radim88
  • 5
  • 3
8 Comments
 
LVL 13

Expert Comment

by:soostibi
ID: 35106543
You shoul put the export outside of the ForEach.
And I do not really understand why do you need that select-object?
My solution is attached.
Get-QADUser -SearchRoot 'xxx' -enabled -LdapFilter '(!(&(manager=*)(accountExpires=9223372036854775807)))' | Export-Csv -Path C:\subcontractorsExport.csv -Delimiter ";"

Open in new window

0
 
LVL 13

Expert Comment

by:soostibi
ID: 35106553
And maybe you might want to add the -notypeinformation switch to the export.
Get-QADUser -enabled -LdapFilter '(!(&(manager=*)(accountExpires=9223372036854775807)))' | Export-Csv -NoTypeInformation -Path C:\subcontractorsExport.csv -Delimiter ";"

Open in new window

0
 
LVL 13

Expert Comment

by:soostibi
ID: 35106592
Sorry, this is the right solution:
Get-QADUser -SearchRoot 'xxx' -enabled -LdapFilter '(!(&(manager=*)(accountExpires=9223372036854775807)))' | 
select-object name, samaccountname, manager, accountexpires | Export-Csv -NoTypeInformation -Path C:\subcontractorsExport.csv -Delimiter ";"

Open in new window

0
Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 

Author Comment

by:Radim88
ID: 35106593
Hi,
It should be something like this, strange that out-file works.

Get-QADUser -Enabled -SearchRoot 'xx'|  %{          if (($_.manager -eq $null) -or ($_.accountexpires -eq $null)) `
 {
 
  $_ |  select name,samaccountname,@{N='Manager';E={ if($_.manager -eq $null){write 'empty'}else{$_.manager | get-qaduser | select -ExpandProperty name }}  },@{N='AccountExpires';E={((get-date ($_.accountexpires)).adddays(-1)).Tostring(“dd.MM.yyyy”) }} | Export-Csv -Path C:\subcontractorsExport.csv -Delimiter ";"
#out-file -filepath C:\subcontractorsExport.txt -encoding ASCII
# Export-Csv -Path C:\subcontractorsExport.csv -Delimiter ";"
}
}
0
 
LVL 13

Accepted Solution

by:
soostibi earned 125 total points
ID: 35107501
Put your export at the very end!

Get-QADUser -Enabled -SearchRoot 'xx'|  %{          if (($_.manager -eq $null) -or ($_.accountexpires -eq $null)) `
 {
 
  $_ |  select name,samaccountname,@{N='Manager';E={ if($_.manager -eq $null){write 'empty'}else{$_.manager | get-qaduser | select -ExpandProperty name }}  },@{N='AccountExpires';E={((get-date ($_.accountexpires)).adddays(-1)).Tostring(“dd.MM.yyyy”) }} | Export-Csv -Path C:\subcontractorsExport.csv -Delimiter ";"
#out-file -filepath C:\subcontractorsExport.txt -encoding ASCII
# }
} | Export-Csv -Path C:\subcontractorsExport.csv -Delimiter ";"

Out-File works as it appends lines to the file, but the export-csv rewrites.

And consider replacing your first line to:
Get-QADUser -SearchRoot 'xxx' -enabled -LdapFilter '(!(&(manager=*)(accountExpires=9223372036854775807)))'

It's much more efficient.
0
 

Author Comment

by:Radim88
ID: 35107574
Thanks thats it:-), and whatabout that -ldapfilter you are mentioning. Its the same like that mine if:?
0
 
LVL 13

Expert Comment

by:soostibi
ID: 35107585
Yes, it should be the same, but it filter runs on the DC side, not in the PowerShell process.
0
 

Author Closing Comment

by:Radim88
ID: 35107609
thx
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Create and license users in Office 365 in bulk based on a CSV file. A step-by-step guide with PowerShell script examples.
A procedure for exporting installed hotfix details of remote computers using powershell
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question