Present file act from many servers at once

I am creating a script that pressent actual security settings on each file and catalog and then exporting it to an Excel file.

Now to the problem. I am importing servernames by using an csv file. So far so good.
But now I want to make a check and create an excel worksheetname for each different servername where info is presented.

There is my problem.

$excel      = New-ConditionalText -Range "A1:E1" -BackgroundColor Yellow -ConditionalTextColor Black
$ExportPath = "g:\Edu.xlsx"

$server     = Get-Content "F:\Scripts\Computers.txt"
$Output = @()
ForEach ($srv in $server){
   $FolderPath = Get-ChildItem -Directory -Path \\$srv\d$\Websites -Recurse -Force
  
ForEach ($Folder in $FolderPath) {
    $Acl = Get-Acl -Path $Folder.FullName
    ForEach ($Access in $Acl.Access) {
$Properties = [ordered]@{ `
    'Folder Name' = $Folder.Name;
    'Folder Path' = $Folder.FullName;
    'Group/User'  = $Access.IdentityReference.ToString();
    'Permissions' = $Access.FileSystemRights;
    'Inherited'   = $Access.IsInherited}
$Output += New-Object -TypeName PSObject -Property $Properties            
}
}}
#$Output | Out-GridView
$Output | Export-Excel $ExportPath `
    -AutoSize `
    -AutoFilter `
    -BoldTopRow `
    -FreezeTopRow `
    -WorksheetName $srv `
    -ConditionalText $excel

You have to move your Export-Excel command inside the outermost foreach loop and use the server name as a part of the file name. :wink:

Ok that created the worksheetnames correctly but information was from each server in every sheet :frowning:
I am missing something small I know but I am totaly stuck right now

Hmmm … did I get you right? You wanted to have the results for each server in a separate file, right?

Something like this should work actually:

$excel = New-ConditionalText -Range "A1:E1" -BackgroundColor Yellow -ConditionalTextColor Black
$server = Get-Content 'F:\Scripts\Computers.txt'
$ExportPath = 'g:\Edu.xlsx'

ForEach ($srv in $server) {
    $FolderPath = Get-ChildItem -Directory -Path \\$srv\d$\Websites -Recurse -Force
    $result = 
    ForEach ($Folder in $FolderPath) {
        $Acl = Get-Acl -Path $Folder.FullName
        ForEach ($Access in $Acl.Access) {
            [PSCustomObject][ordered]@{ 
                'Folder Name' = $Folder.Name
                'Folder Path'     = $Folder.FullName
                'Group/User'      = $Access.IdentityReference.ToString()
                'Permissions'     = $Access.FileSystemRights
                'Inherited'       = $Access.IsInherited
            }
        }
    }
    $ExportExcelParams = @{
        Path            = $ExportPath
        AutoSize        = $true
        AutoFilter      = $true
        BoldTopRow      = $true
        FreezeTopRow    = $true
        WorksheetName   = $srv
        ConditionalText = $excel
    }
    $result | Export-Excel @ExportExcelParams
}

Close but not exactly.
Instead of creating a separate file for every server I want to create a separate worksheet in one excel file with the servername and info from that and only that server.

Ah … ok … without having experiences with this module and without trying it right now … I changed my code suggestion above … try it

This is the solution. :slight_smile:

$excel = New-ConditionalText -Range "A1:E1" -BackgroundColor Yellow -ConditionalTextColor Black
$server = Get-Content "F:\Scripts\Computers.txt"

ForEach ($srv in $server) {
    $FolderPath = Get-ChildItem -Directory -Path \\$srv\d$\Websites -Recurse -Force
    $result = 
    ForEach ($Folder in $FolderPath) {
        $Acl = Get-Acl -Path $Folder.FullName
        ForEach ($Access in $Acl.Access) {
            [PSCustomObject][ordered]@{ 
                'Folder Name' = $Folder.Name
                'Folder Path'     = $Folder.FullName
                'Group/User'      = $Access.IdentityReference.ToString()
                'Permissions'     = $Access.FileSystemRights
                'Inherited'       = $Access.IsInherited
            }
        }
    }
    $ExportExcelParams = @{
        Path            = "g:test.xlsx"
        AutoSize        = $true
        AutoFilter      = $true
        BoldTopRow      = $true
        FreezeTopRow    = $true
        WorksheetName   = $($srv)
        ConditionalText = $excel
    }
    $result | Export-Excel @ExportExcelParams
}

Ah … now you’ve figured it out by yourself … great. :+1:t4: :slightly_smiling_face:

1 Like