Combining the output from 2 commands.

This topic contains 5 replies, has 3 voices, and was last updated by  Noel 1 month, 1 week ago.

  • Author
    Posts
  • #89219

    Noel
    Participant

    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 ';'
    
    
  • #89221

    JohnM
    Participant

    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
    
    
  • #89222

    Noel
    Participant

    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 ';'
    
  • #89225

    Sam Boutros
    Participant

    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
    
    • #89239

      Noel
      Participant

      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.

  • #89236

    JohnM
    Participant

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

You must be logged in to reply to this topic.