Output the results in csv file for the powershell results

Question

Can anyone advice me how to get output of this Powershell command below in CSV file

Function GetDBUserInfo($Dbase)
{
if ($dbase.status -eq "Normal") # ensures the DB is online before checking
{$users = $Dbase.users | where {$_.login -eq $SQLLogin.name} # Ignore the account running this as it is assumed to be an admin account on all servers
foreach ($u in $users)
{
if ($u)
{
$DBRoles = $u.enumroles()
foreach ($role in $DBRoles) {
if ($role -eq "db_owner") {
write-host $role "on"$Dbase.name -foregroundcolor "red" #if db_owner set text color to red
}
else {
write-host $role "on"$Dbase.name
}
}
#Get any explicitily granted permissions
foreach($perm in $Dbase.EnumObjectPermissions($u.Name)){
write-host $perm.permissionstate $perm.permissiontype "on" $perm.objectname "in" $DBase.name }
}
} # Next user in database
}

else

Skip to next database.

}

Main portion of script start

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null #ensure we have SQL SMO available
foreach ($SQLsvr in get-content "C:\servers.txt") # read the instance source file to get instance names
{
$svr = new-object ("Microsoft.SqlServer.Management.Smo.Server") $SQLsvr
write-host "================================================================================="
write-host "SQL Instance: " $svr.name
write-host "SQL Version:" $svr.VersionString
write-host "Edition:" $svr.Edition
write-host "Login Mode:" $svr.LoginMode
write-host "================================================================================="
$SQLLogins = $svr.logins
foreach ($SQLLogin in $SQLLogins)
{
write-host "Login : " $SQLLogin.name
write-host "Login Type : " $SQLLogin.LoginType
write-host "Created : " $SQLLogin.CreateDate
write-host "Default DB : " $SQLLogin.DefaultDatabase
Write-Host "Disabled : " $SQLLogin.IsDisabled
$SQLRoles = $SQLLogin.ListMembers()
if ($SQLRoles) {
if ($SQLRoles -eq "SysAdmin"){ write-host "Server Role : " $SQLRoles -foregroundcolor "red"}
else { write-host "Server Role : " $SQLRoles
} } else {"Server Role : Public"}
If ( $SQLLogin.LoginType -eq "WindowsGroup" ) { #get individuals in any Windows domain groups
write-host "Group Members:"
try {
$ADGRoupMembers = get-adgroupmember $SQLLogin.name.Split("\")[1] -Recursive
foreach($member in $ADGRoupMembers){
write-host " Account: " $member.name "("$member.SamAccountName")"
}
}
catch
{
#Sometimes there are 'ghost' groups left behind that are no longer in the domain, this highlights those still in SQL
write-host "Unable to locate group " $SQLLogin.name.Split("\")[1] " in the AD Domain" -foregroundcolor Red
}
}
#Check the permissions in the DBs the Login is linked to.
if ($SQLLogin.EnumDatabaseMappings())
{write-host "Permissions:"
foreach ( $DB in $svr.Databases)
{
GetDBUserInfo($DB)
} # Next Database
}
Else
{write-host "None."
}
write-host " ----------------------------------------------------------------------------"
} # Next Login
} # Next Server

Answer 1

You'd have to define what you want first. You have so many "Write-Host" cmdlets it's impossible to know what data is being used to track the progress of you script and what information you want in the CSV.

Start by having the function GetDBUserInfo return something to your the caller (if it's supposed to return anything). Right now it just shows data on the console. Then start placing some of the information you're displaying into variables. Once you've done that you can create a PSCustomObject that holds what you want in the CSV.

Because you use the "Foreach ($x in $y)" format instead of "$y | Foreach-Object {scriptblock code}" you don't have a pipeline so you'll accumulate the PSCustomObjects you create in an array. After the last part of your script (currently line #76) you can pipe the array into Export-VSV.

Answer 2

Exactly what do you want in a CSV file? Your script is enumerating a lot of data and writing a bunch of (non-CSV friendly) stuff to the screen.

Ultimately replace your Write-Host calls with storing the data you care about into an array/hashtable. Then once you've enumerated all the data and populated your array/hashtable then use Export-Csv to convert it to a csv file. But note that you'll need to convert everything into a single structure in an array (because a CSV is just a table of rows) or you'll need to generate separate csv files for each set of data you care about (users, logins, etc).