I want to export all users of a SharePoint Farm and found the code below. I'm new to PowerShell, so the solution is probably easy, but I struggle with it.
The code works well except that it outputs the data in a single column with each row entry with the data all listed after each other ("type,user,group,weburl,webtitle"). I'd love it when it would output it into 5 columns kinda like this
type | user | group | weburl | webtitle |
---|---|---|---|---|
type 1 | user 1 | group 1 | weburl 1 | webtitle 1 |
type 1 | user 2 | group 2 | weburl 1 | webtitle 1 |
type 1 | user 3 | group 1 | weburl 2 | webtitle 2 |
Add-PSSnapin microsoft.sharepoint.powershell -ErrorAction SilentlyContinue
$Currentime = get-date -format "yyyyMMdd_hhmmtt"
$filename = "FarmUsers"
$datafile = ("{0}{1}.csv" -f $filename, $Currentime)
$headerfile = "type,user,group,weburl,webtitle"
$headerfile | out-file -FilePath $datafile
$iissitedata = get-spwebapplication
foreach($farmsite in $iissitedata)
{
foreach ($SiteCollection in $farmsite.sites)
{
foreach ($web in $SiteCollection.Allwebs)
{
foreach ($usersite in $web.users)
{
$data = ("RootUser,{0},-,{1},{2}" -f $usersite, $web.url,$web.name)
$data | out-file -FilePath $datafile -append
}
foreach ($group in $web.Groups)
{
foreach ($user in $group.users)
{
$data = ("GroupUser,{0},{1},{2},{3}" -f $user, $group, $web.url, $web.name)
$data | out-file -FilePath $datafile -append
}
}
$web.Dispose()
}
}
}
What changes do I have to make to the script, so that it outputs into columns?
Thanks in advance!
Instead of manually formatting each row in the CSV, you'll want to create a series of objects with properties corresponding to the column names you want, and then let Export-Csv
take care of constructing the CSV file for you:
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue
$Currentime = Get-Date -format "yyyyMMdd_hhmmtt"
$filename = "FarmUsers"
$datafile = ("{0}{1}.csv" -f $filename, $Currentime)
$iissitedata = Get-SPWebApplication
foreach ($farmsite in $iissitedata)
{
foreach ($SiteCollection in $farmsite.sites)
{
foreach ($web in $SiteCollection.Allwebs)
{
foreach ($usersite in $web.users)
{
$data = [pscustomobject]@{
type = "RootUser"
user = $usersite
group = '-'
weburl = $web.url
webtitle = $web.name
}
$data | Export-Csv -LiteralPath $datafile -NoTypeInformation -Append
}
foreach ($group in $web.Groups)
{
foreach ($user in $group.users)
{
$data = [pscustomobject]@{
type = "GroupUser"
user = $user
group = $group
weburl = $web.url
webtitle = $web.name
}
$data | Export-Csv -LiteralPath $datafile -NoTypeInformation -Append
}
}
$web.Dispose()
}
}
}