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 ';'
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
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 ';'
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
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.)
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.