Split property into 3 properties

This topic contains 16 replies, has 4 voices, and was last updated by Profile photo of Bob McCoy Bob McCoy 1 year, 9 months ago.

  • Author
    Posts
  • #28274
    Profile photo of Tony Pagliaro
    Tony Pagliaro
    Participant

    Hi Guys,
    I have a vendor module I'm using and trying to fix the output from one of the properties on a get command. The property is serialnumber but it returns other info too, which I want to keep and relabel.

    Below is the command output as is, followed by the get member of that, and finally what I want it to eventually look like.

     Get-BETapeDriveDevice |Select SerialNumber 
    SerialNumber
    ------------
    HP      Ultrium 5-SCSI  SNXXXXXXXX
    QUANTUM ULTRIUM 6       SNXXXXXXXX
    QUANTUM ULTRIUM 6       SNXXXXXXXX
    QUANTUM ULTRIUM 6       SNXXXXXXXX
    
    ##################
    
    PS C:\Users\admin> Get-BETapeDriveDevice |Select SerialNumber |gm
       TypeName: Selected.BackupExec.Management.CLI.BETapeDriveDevice
    Name         MemberType   Definition
    ----         ----------   ----------
    Equals       Method       bool Equals(System.Object obj)
    GetHashCode  Method       int GetHashCode()
    GetType      Method       type GetType()
    ToString     Method       string ToString()
    SerialNumber NoteProperty System.String SerialNumber=HP      Ultrium 5-SCSI  SNXXXXXXXX
    
    ##################
    
    PS C:\Users\admin> Get-BETapeDriveDevice |Select SerialNumber .... Some stuff
    Make    Model           SerialNumber
    ----    -----           ------------
    HP      Ultrium 5-SCSI  SNXXXXXXXX
    QUANTUM ULTRIUM 6       SNXXXXXXXX
    QUANTUM ULTRIUM 6       SNXXXXXXXX
    QUANTUM ULTRIUM 6       SNXXXXXXXX
    
    #>
    

    Tried using a split, but gm shows no method by that name.

  • #28276
    Profile photo of Rob Simmers
    Rob Simmers
    Participant

    Try something like this:

    $results = Get-BETapeDriveDevice |
    Select SerialNumber |
    foreach{
        $array = $_ -Split " "
        $props = @{
            Make=$array[0]
            Model=$array[1]
            SerialNumber=$array[2]    
        }
                
        New-Object -TypeName PSObject -Property $props 
    }
    
    $results
    

    While there might not be a Split method, you can still leverage the Split operator to do what you attempting.

  • #28281
    Profile photo of Bob McCoy
    Bob McCoy
    Participant

    The problem here is I don't trust your data. To do a reliable data capture, the data has to be consistent. For instance, is the Make field ALWAYS 7 characters wide? I doubt it. As additional makes get added, I suspect this column width will vary. Same with Model. And unfortunately there's no good field separator because even the white-space count varies.

    So what do you get for ...

    (Get-BETapeDriveDevice).ToString()
    
  • #28284
    Profile photo of Bob McCoy
    Bob McCoy
    Participant

    Since a simple split is not helpful here without a consistent field separator, and the field length may be dynamic based on what data the cmdlet encounters, then you have to dynamically determine the field length on each run. If you go with the assumption that the field headers will always start in the first column of the field, then something like this would work.

    function Get-Serial {
    [CmdletBinding()]
    Param(
        [Parameter(Mandatory=$True,Position=1)]
        [string]$Path
    )
    BEGIN {
        # dymnamically determine column lengths based on header
        $header = Get-Content -Path $Path | select -First 1
        $f0Start = $header.IndexOf("Make")
        $f1Start = $header.IndexOf("Model")
        $f2Start = $header.IndexOf("Serial")
        $f0Len = $f1Start - 1
        $f1Len = $f2Start - $f0Len - 2
    } PROCESS {
    $data = Get-Content -Path $Path | select -Skip 2 | foreach {
    
        # length of last field is based on length of current line
        # minus the lengths of the two previous fields
        $f2Len = $_.Length - $f0Len - $f1Len - 2
    
        [PSCustomObject]@{
            Make = $_.substring($f0Start,$f0Len).trim()
            Model = $_.substring($f1Start,$f1Len).trim()
            Serial = $_.substring($f2Start,$f2Len).trim()
        }
        # just in case you were curious
        Write-Verbose "($f0Start,$f0Len) ($f1Start,$f1Len) ($f2Start,$f2Len)"
    }
    } END {
    # return the extracted dataset
    $data
    }
    }
    (Get-Serial -Path C:\Ephemeral\data.txt).Serial
    # alternatively -----
    Get-Serial -Path C:\Ephemeral\data.txt | select -Property Serial
    
  • #28286
    Profile photo of Rob Simmers
    Rob Simmers
    Participant

    @Bob

    I understand your point about the multiple spaces. Wouldn't a simple fix be just replacing the spaces using a regex search?

    $test = "HP      Ultrium 5-SCSI  SNXXXXXXXX",
            "QUANTUM ULTRIUM 6       SNXXXXXXXX",
            "QUANTUM ULTRIUM 6       SNXXXXXXXX",
            "QUANTUM ULTRIUM 6       SNXXXXXXXX"
    
    $test | 
    foreach{
        $string = $_ -replace "\s+", " "
        $array = $string -Split " "
        $props = @{
            Make=$array[0]
            Model="{0} {1}" -f $array[1], $array[2]
            SerialNumber=$array[3]    
        }
        New-Object -TypeName PSObject -Property $props 
    }
    

    Output:

    SerialNumber Make    Model         
    ------------ ----    -----         
    SNXXXXXXXX   HP      Ultrium 5-SCSI
    SNXXXXXXXX   QUANTUM ULTRIUM 6     
    SNXXXXXXXX   QUANTUM ULTRIUM 6     
    SNXXXXXXXX   QUANTUM ULTRIUM 6    
    
  • #28302
    Profile photo of Bob McCoy
    Bob McCoy
    Participant

    Actually my go-to method is usually RegEx. And I started down that path and had a working script. However, as I said above, I don't trust his limited set of sample data. So since it looked like the cmdlet output had fixed columns l went that direction. It was just a matter of trying to figure out where each column started.

    My preference would have been RegEx, but without a more complete dataset I didn't feel comfortable with that.

  • #28303
    Profile photo of Bob McCoy
    Bob McCoy
    Participant

    Oh, and just in case the OP is wondering, this is sample/prototype code. It uses a text file input since I don't have the upstream cmdlet. It can be easily modified to take input from the pipeline.

  • #28304
    Profile photo of Bob McCoy
    Bob McCoy
    Participant

    And Rob, my problem wasn't with multiple spaces, it was with embedded (non-quoted) spaces and trying to figure which ones were delimiters and which weren't. Again we only saw a very limited set of the total possibilities.

  • #28307
    Profile photo of Tony Pagliaro
    Tony Pagliaro
    Participant
    PS> Get-BETapeDriveDevice |select serialnumber | foreach{
    >>     $string = $_ -replace "\s+", " "
    >>     $array = $string -Split " "
    >>     $props = @{
    >>         Make=$array[0]
    >>         Model="{0} {1}" -f $array[1], $array[2]
    >>         SerialNumber=$array[3]
    >>     }
    >>     New-Object -TypeName PSObject -Property $props
    >> }
    >>
    SerialNumber                Make                        Model
    ------------                ----                        -----
    HUE14200U9}                 @{SerialNumber=HP           Ultrium 5-SCSI
    HU1310UVP3}                 @{SerialNumber=QUANTUM      ULTRIUM 6
    HU1310UVNH}                 @{SerialNumber=QUANTUM      ULTRIUM 6
    HU1310UVNY}                 @{SerialNumber=QUANTUM      ULTRIUM 6
    
    
    PS C:\Users\admin> (Get-BETapeDriveDevice).ToString()
    System.Object[]
    

    I didn't bother posting this before.

    I agree the regex is probably best, I just am not fluent myself. I will see if I can throw in a twist. Say serialnumber is one property of a few and I want to just add in those 2 extra properties to an existing list, inserted in order to make 4 into 6 properties as such:.

    PS> Get-BETapeDriveDevice|select Name, SerialNumber, ReadErrors, WriteErrors|ft -Autosize
    Name SerialNumber                       ReadErrors WriteErrors
    ---- ------------                       ---------- -----------
    L5-1 HP      Ultrium 5-SCSI  SNXXXXXXXX          0           0
    L6-1 QUANTUM ULTRIUM 6       SNXXXXXXXX          0           0
    L6-2 QUANTUM ULTRIUM 6       SNXXXXXXXX          0           1
    L6-3 QUANTUM ULTRIUM 6       SNXXXXXXXX          0           0
    

    I'd like the order to be

    Name,Make,Model,SerialNumber,ReadErrors,WriteErrors
    

    The '/s+' is what I was missing.

    Is there a way to split by the first and last space and ignore the middle spaces, after the replace? Say there is always 3 things in there, but there could be more than one space in the middle thing.

  • #28308
    Profile photo of Bob McCoy
    Bob McCoy
    Participant
    $test = @"
    HP      Ultrium 5-SCSI  SNXXXXXXX1
    QUANTUM ULTRIUM 6       SNXXXXXXX2
    QUANTUM ULTRIUM 6       SNXXXXXXX3
    QUANTUM ULTRIUM 6       SNXXXXXXX4
    "@ -split "`n"
    $pattern = "(\w+?)(?:\s+)(.+?)(?:\s+)(\w+?)$"
    $results = $test | foreach {
        $item = $_ -split $pattern 
        [PSCustomObject]@{
            Make = $item[1]
            Model = $item[2]
            SerialNumber = $item[3]
        }
    }
    $results
    

    You don't have to create a PSCustomObject. The main point here is to show how the $item array is referenced following the split based on the RegEx pattern. You also don't have to use the $pattern variable. I just find it easier to read the code when I separate defining the pattern versus using it.

  • #28310
    Profile photo of Tony Pagliaro
    Tony Pagliaro
    Participant

    I think I may have to visit the vendor forums because I don't know why this produces no output.

    PS>Get-BETapeDriveDevice |
        Select Name, 
        Make, 
        Model,    
        SerialNumber,
        TotalHardReadErrors, 
        TotalHardWriteErrors | 
            foreach {
    	  $a = $_.SerialNumber -Split "^(\w+)\s(.+)\s(\w+)$"
    	    $_.Make = $a[0]
    	    $_.Model = $a[1]
    	    $_.SerialNumber = $a[2]
            }
    

    Not even this:

    PS> $list = Get-BETapeDriveDevice |Select SerialNumber
    PS> $list.SerialNumber
    PS> $list
    SerialNumber
    ------------
    HP      Ultrium 5-SCSI  SNXXXXXXX
    QUANTUM ULTRIUM 6       SNXXXXXXX
    QUANTUM ULTRIUM 6       SNXXXXXXX
    

    For some reason I cannot call just that prop and work with it.

  • #28313
    Profile photo of Warren Frame
    Warren Frame
    Participant

    I would agree with Bob. Regex and text manipulation can be delicate and error prone.

    If you end up relying on the resulting code, you'll be in a very delicate situation, and should ensure you test the effects of introducing new hardware that might not fit the example code.

    Your last note is interesting – are there any format files for this module/snapin?

    Good luck!

  • #28314
    Profile photo of Bob McCoy
    Bob McCoy
    Participant

    So I'll go back to what I said above — I don't trust your data. We're screwing around with text output, not the data. Can you do an Export-CliXml so we can reconstitute the data from your cmdlet as opposed to what Out-Default does to it? If you cannot or will not, then you're right, it's probably best addressed on one of the vendor's forums.

  • #28320
    Profile photo of Tony Pagliaro
    Tony Pagliaro
    Participant

    the format file is available here:
    https://dl.dropboxusercontent.com/u/7510200/BEMCLI.format.ps1xml

    The xml export of the command is small enough for posting here:

    
     Get-BETapeDriveDevice |select name,serialnumber |Export-Clixml
      
              Selected.BackupExec.Management.CLI.BETapeDriveDevice
          System.Management.Automation.PSCustomObject
          System.Object
          L5-1
          HP      Ultrium 5-SCSI  SNXXXXXXXX
          L6-1
          QUANTUM ULTRIUM 6       SNXXXXXXXX    
        L6-2
          QUANTUM ULTRIUM 6       SNXXXXXXXX
          L6-3
          QUANTUM ULTRIUM 6       SNXXXXXXXX
      
  • #28322
    Profile photo of Bob McCoy
    Bob McCoy
    Participant

    Am I missing something??? That is not XML.

  • #28381
    Profile photo of Tony Pagliaro
    Tony Pagliaro
    Participant

    You're not missing anything. The pre/pre tags don't work very well on this site.
    Here's the exported file in the raw
    https://www.dropbox.com/s/ik1hyydvb0qrteb/test.xml?dl=0

  • #28385
    Profile photo of Bob McCoy
    Bob McCoy
    Participant
    $data = Import-Clixml -Path .\test.xml
    $pattern = "(\w+?)(?:\s+)(.+?)(?:\s+)(\w+?)$"
    $results = $data | foreach {
        $name = $_.Name
        $item = $_.SerialNumber -split $pattern 
        [PSCustomObject]@{
            Name = $name
            Make = $item[1]
            Model = $item[2]
            SerialNumber = $item[3]
        }
    }
    $results
    

    That gave me the following results:

    Name Make    Model          SerialNumber
    ---- ----    -----          ------------
    L5-1 HP      Ultrium 5-SCSI SN00000001  
    L6-1 QUANTUM ULTRIUM 6      SN00000002  
    L6-2 QUANTUM ULTRIUM 6      SN00000003  
    L6-3 QUANTUM ULTRIUM 6      SN00000004  
    

    FYI I edited the serial numbers so I would have different results for each entry.

You must be logged in to reply to this topic.