Author Posts

December 14, 2017 at 1:54 pm

Hi,

I'm running 2 commands and need to output the 2 output header results (CompanyName and Username) from the Get-NAVServerUserPermissionSet command along with all of the output from the Get-Navserverpermission command.
Output headers from Get-NAVServerUserPermissionSet -> UserSecurityID, PermissionSetID, CompanyName,Scope,AppID,UserName,PermissionSetName
Output headers from Get-NavServerPermission -> PermissionSetID,ObjectType,ObjectID,ReadPermission,InsertPermission,ModifyPermission,DeletePermission,ExecutePermission,SecurityFilter,PermissionSetName,ObjectName

Could anyone help me here?


$NavUserPermissionSets = Get-NAVServerUserPermissionSet -ServerInstance "Database" -WindowsAccount Domain\user

$Perms = Foreach($NavUserPermissionSet in $NavUserPermissionSets) {
Get-NavServerPermission -ServerInstance "Database" -PermissionSetId $NavUserPermissionSet.PermissionSetID 
}
$Perms | Export-CSV \\Computer\c$\temp\perms.csv –noType -Encoding:UTF8 -delimiter ';'

December 14, 2017 at 2:19 pm

You could use a PSCustomObject, combine the output from each command, then write to the CSV. Sometimes I use the array as described below, mostly in one script. Sometimes I just write the output from the PSObject (for example when it is in a function), and use the pipeline. Good Luck.

#================================================================================
# Method 1 - Probably the official PowerShell method                            =
#================================================================================
# Define your variables $array and $props
$Array=@()   # an array to hold the objects

#define the structure of the $Props as a Hash Table.
$props = @{ 'ServerName' = $Null;
            'USER'       = $Null ;
            'Count'      = $Null; 
          }

#Loop here thru your data
# Add values to your hash table
$props = @{ 'ServerName' = "wxyz001";
            'USER'       =  "Shawn" ;
            'Count'      = 1000; 
          }


#create a new PSObject with the values from the $Props hash table.
$obj = New-Object -TypeName PSObject -Property $props

# In your loop keep adding the objects to the array of objects.
$array += $obj

# (outside of your loop here ...... )

#at the very end of your script do something. Pipe the array of objects to some PS command
#$array | export-csv c:\temp\A.csv -NoTypeInformation -NoClobber -append 
$Array | FT -AutoSize

#================================================================================
# Method 2 - A shortcut that does exactly the same.                             =
#================================================================================
# Define your variables
$Array=@()      #an array to hold the objects
$Props = "" | Select ServerName, User, Count   # Creating a custom PSObject 


# in the loop, assign values to your $Props object
$Props.ServerName = "server007"
$props.USER = "Willie"
$props.Count = 1000000

#in the loop add your properties to the array of object 
$Array += $props

# at the end pipe the contents of the $Array of PS Objects to some command.
$Array | FT -AutoSize
$Array | Export-csv .\output.csv

December 14, 2017 at 3:01 pm

hi John,
I'm nearly there but the result isn't quite what i expected with just 4 lines of data (should be over 300) and System.Object[] in most of the fields.

$Results = @()
$NavUserPermissionSets = Get-NAVServerUserPermissionSet -ServerInstance "Database" -WindowsAccount Domain\User

Foreach($NavUserPermissionSet in $NavUserPermissionSets) 
{
$NavPerms = Get-NavServerPermission -ServerInstance "Database" -PermissionSetId $NavUserPermissionSet.PermissionSetID 
$Username = $NavUserPermissionSet.UserName
$CompanyName = $NavUserPermissionSet.CompanyName

    $Properties = @{
    
    PermissionSetID = $NavPerms.PermissionSetID
    ObjectType = $NavPerms.ObjectType
    ObjectID = $NavPerms.ObjectID
    ReadPermission = $NavPerms.ReadPermission
    InsertPermission = $NavPerms.InsertPermission
    ModifyPermission = $NavPerms.ModifyPermission
    DeletePermission = $NavPerms.DeletePermission
    ExecutePermission = $NavPerms.ExecutePermission
    PermissionSetName = $NavPerms.PermissionSetName
    ObjectName = $NavPerms.ObjectName
    Username = $Username
    CompanyName = $CompanyName

}

$Results += New-Object psobject -Property $properties

}

$Results | Select-Object PermissionsSetID,ObjectType,ObjectID,ReadPermission,InsertPermission,ModifyPermission,DeletePermission,ExecutePermission,PermissionSetName,ObjectName,Username,CompanyName | Export-CSV \\computer\c$\temp\new_rechte.csv –noType -Encoding:UTF8 -delimiter ';'

December 14, 2017 at 3:08 pm

You simply stitch the 2 objects (easier to think of them as tables at this point), as we stitch 2 database tables based on a common property (column). As in DB/SQL processing the common property must be unique..

# Sample Data
$myGetNAVServerUserPermissionSetOutput = 1..5 | % {
    [PSCustomObject]@{
        UserSecurityID    = "SomeUserSecurityID$PSItem"
        PermissionSetID   = "SomePermissionSetID$PSItem"
        CompanyName       = "SomeCompanyName$PSItem"
        Scope             = "SomeScope$PSItem"
        AppID             = "SomeAppID$PSItem"
        UserName          = "SomeUserName$PSItem"
        PermissionSetName = "SomePermissionSetName$PSItem"
    }
}
$myGetNAVServerUserPermissionSetOutput | FT -a 
$myGetNavServerPermissionOutput = 1..5 | % {
    [PSCustomObject]@{
        ObjectType        = "SomeObjectType$PSItem"
        UserSecurityID    = "SomeUserSecurityID$PSItem"
        ObjectID          = "SomeObjectID$PSItem"
        ReadPermission    = "SomeReadPermission$PSItem"
    }
}
$myGetNavServerPermissionOutput | FT -a 

# Object (table) merge, based on UserSecurityID (unique) common property:
$myMergedTables = foreach ($Item in $myGetNAVServerUserPermissionSetOutput) {    
    $MatchedRecord = $myGetNavServerPermissionOutput | ? { $PSItem.UserSecurityID -eq $Item.UserSecurityID }
    [PSCustomObject]@{
        UserSecurityID    = $Item.UserSecurityID
        PermissionSetID   = $Item.PermissionSetID
        CompanyName       = $Item.CompanyName
        Scope             = $Item.Scope
        AppID             = $Item.AppID
        UserName          = $Item.UserName
        PermissionSetName = $Item.PermissionSetName
        ObjectType        = $MatchedRecord.ObjectType
        ObjectID          = $MatchedRecord.ObjectID
        ReadPermission    = $MatchedRecord.ReadPermission
    }
}
$myMergedTables | FT -a 

Sample output:

UserSecurityID      PermissionSetID      CompanyName      Scope      AppID      UserName      PermissionSetName     
--------------      ---------------      -----------      -----      -----      --------      -----------------     
SomeUserSecurityID1 SomePermissionSetID1 SomeCompanyName1 SomeScope1 SomeAppID1 SomeUserName1 SomePermissionSetName1
SomeUserSecurityID2 SomePermissionSetID2 SomeCompanyName2 SomeScope2 SomeAppID2 SomeUserName2 SomePermissionSetName2
SomeUserSecurityID3 SomePermissionSetID3 SomeCompanyName3 SomeScope3 SomeAppID3 SomeUserName3 SomePermissionSetName3
SomeUserSecurityID4 SomePermissionSetID4 SomeCompanyName4 SomeScope4 SomeAppID4 SomeUserName4 SomePermissionSetName4
SomeUserSecurityID5 SomePermissionSetID5 SomeCompanyName5 SomeScope5 SomeAppID5 SomeUserName5 SomePermissionSetName5

ObjectType      UserSecurityID      ObjectID      ReadPermission     
----------      --------------      --------      --------------     
SomeObjectType1 SomeUserSecurityID1 SomeObjectID1 SomeReadPermission1
SomeObjectType2 SomeUserSecurityID2 SomeObjectID2 SomeReadPermission2
SomeObjectType3 SomeUserSecurityID3 SomeObjectID3 SomeReadPermission3
SomeObjectType4 SomeUserSecurityID4 SomeObjectID4 SomeReadPermission4
SomeObjectType5 SomeUserSecurityID5 SomeObjectID5 SomeReadPermission5

UserSecurityID      PermissionSetID      CompanyName      Scope      AppID      UserName      PermissionSetName      ObjectType      ObjectID      ReadPermission     
--------------      ---------------      -----------      -----      -----      --------      -----------------      ----------      --------      --------------     
SomeUserSecurityID1 SomePermissionSetID1 SomeCompanyName1 SomeScope1 SomeAppID1 SomeUserName1 SomePermissionSetName1 SomeObjectType1 SomeObjectID1 SomeReadPermission1
SomeUserSecurityID2 SomePermissionSetID2 SomeCompanyName2 SomeScope2 SomeAppID2 SomeUserName2 SomePermissionSetName2 SomeObjectType2 SomeObjectID2 SomeReadPermission2
SomeUserSecurityID3 SomePermissionSetID3 SomeCompanyName3 SomeScope3 SomeAppID3 SomeUserName3 SomePermissionSetName3 SomeObjectType3 SomeObjectID3 SomeReadPermission3
SomeUserSecurityID4 SomePermissionSetID4 SomeCompanyName4 SomeScope4 SomeAppID4 SomeUserName4 SomePermissionSetName4 SomeObjectType4 SomeObjectID4 SomeReadPermission4
SomeUserSecurityID5 SomePermissionSetID5 SomeCompanyName5 SomeScope5 SomeAppID5 SomeUserName5 SomePermissionSetName5 SomeObjectType5 SomeObjectID5 SomeReadPermission5

December 14, 2017 at 3:57 pm

I think your line below needs read like this inside the For Loop {}

$obj = New-Object psobject -Property $properties
$Results += $obj

The way you have it I suspect would work. You probably do not need the select statement, unless you are reordering the data.
(Looks like Sam B has a solution for you. I am going to add that to my bag of tricks.)

December 14, 2017 at 3:59 pm

Hi all,

The command here is fine for me and seems to work in powershell when I output the results -> $results

$Results = @()
$NavUserPermissionSets = Get-NAVServerUserPermissionSet -ServerInstance "Database" -WindowsAccount Domain\User

Foreach($NavUserPermissionSet in $NavUserPermissionSets) 
{
$NavPerms = Get-NavServerPermission -ServerInstance "Database" -PermissionSetId $NavUserPermissionSet.PermissionSetID 
$Username = $NavUserPermissionSet.UserName
$CompanyName = $NavUserPermissionSet.CompanyName

    $Properties = @{
    
    PermissionSetID = $NavPerms.PermissionSetID
    ObjectType = $NavPerms.ObjectType
    ObjectID = $NavPerms.ObjectID
    ReadPermission = $NavPerms.ReadPermission
    InsertPermission = $NavPerms.InsertPermission
    ModifyPermission = $NavPerms.ModifyPermission
    DeletePermission = $NavPerms.DeletePermission
    ExecutePermission = $NavPerms.ExecutePermission
    PermissionSetName = $NavPerms.PermissionSetName
    ObjectName = $NavPerms.ObjectName
    Username = $Username
    CompanyName = $CompanyName

}

$Results += New-Object psobject -Property $properties

}

$Results

This is what I get:

PermissionSetName : {set1, set2,set3}
PermissionSetID   : {setid1, setid2.}
ModifyPermission  : {No, Yes, Yes, Yes...}
ObjectID          : {10, 11, 12, 13...}
ObjectType        : {TableData, TableData, TableData, TableData...}
ExecutePermission : {No, No, No, No...}
ReadPermission    : {Yes, Yes, Yes, Yes...}
ObjectName        : {Change Log Entry, Change Log Setup, Change Log Setup (Table), Change Log Setup (Field)...}
DeletePermission  : {No, Yes, Yes, Yes...}
CompanyName       : 
Username          : Username
InsertPermission  : {No, Yes, Yes, Yes...}

I need to be able to list all the objectID's in the result and export to a csv.

Thanks for all your help.