Indexing Data from Text file

This topic contains 8 replies, has 4 voices, and was last updated by  Kristian 9 months, 2 weeks ago.

  • Author
    Posts
  • #60808

    Kristian
    Participant

    Hello Powershell.org

    I've got a problem, I'm reading out 3 tables from a SQL Server Database and sending them to a simple text file. I need a way to read in the text file and have structured data so that I can "make it so".

    Three tables are:
    Devices
    Groups
    Device to Group Mappings

    Each Device has a device ID, and each group has a group ID, the Device to group mapping does exactly that. There is no way to export these discovery/inventory ranges, they have to be recreated from scratch. When talking about this at scale manually recreating a 1000 of these isn't going to happen(not by me)

    I've tried numerous things and I'd like to stick to powershell on this one. I'm sure pandas could make quick work of this however I want something that can be used everywhere <3 powershell.
    $device = Import-csv -delimiter ":" -path ...
    $device = get-content ......
    I can reference $device[n] and get the line however this is not good because I don't need the line as the index. I need these to be more PowerShell like.

    I'd like to be able to do things like
    $device.ID
    This would allow me to correlate the three tables in such a way that I can use the built in features of the product to script back the discovery/inventory ranges recreating all my groups nicely

    Example of device ID1 from text file created. If there is a way to read in the text file and have each device be its own powershell object where you can reference each object by index say device ID or Address the rest should be easy.
    Id : 1
    Address : 10.0.147.2
    AddressType : 1
    SNMPGetCommunity : public
    SNMPSetCommunity :
    CIMUserName :
    CIMPassword :
    IPMIUserName :
    IPMIPassword :
    IPMIKGKey :
    IPMIRetry : 2
    IPMITimeout : 5
    EnabledState : 1
    SNMPRetry : 2
    SNMPTimeout : 4
    SubnetType :
    SubnetMask : 255.255.255.0
    Protocols : 64
    DeviceTypeMask : 1
    ICMPRetry : 1
    ICMPTimeout : 1000
    EMCUserName :
    EMCPassword :
    EMCPort :
    WSMANUserName : root
    WSMANPassword : {1, 0, 0, 0...}
    WSMANCertPath :
    WSMANPort : 443
    WSMANTimeout : 60
    WSMANRetries : 3
    WSMANBitSettings : 7
    BladeDiscovery : 0
    NameDisplay :
    SSHUserName :
    SSHPassword :
    SSHPort : 22
    SSHTimeout : 3
    SSHRetries : 3
    RowVersion : {0, 0, 0, 0...}

    Any pointers in the right direction would be great

  • #60811

    Daniel Krebs
    Moderator

    You could use Export-CliXml and Import-CliXml. Export-CliXml creates an XML-based representation of an object and stores it in a file. Import-CliXml brings back the object from the saved file.

    https://msdn.microsoft.com/en-us/powershell/reference/5.1/microsoft.powershell.utility/export-clixml
    https://msdn.microsoft.com/en-us/powershell/reference/5.1/microsoft.powershell.utility/import-clixml

    I believe both cmdlets are available since PowerShell version 2.

    An alternative would be to use ConvertTo-Json and ConvertFrom-Json, and handle the file saving and loading yourself.

    I hope that helps.

  • #60823

    Kristian
    Participant

    Thanks for the reply Daniel however I was not successful and ended up with the same thing I have been since the beginning. Text that looks nice on the console but is useless for processing.

    Example lets take sample "Device to Group Mappings" table. Notice that unlike the other two tables that are like this
    [obj1.header1]
    [obj1.header2]
    [obj1.header3]
    [obj2.header1]
    [obj2.header2]
    ...
    This one is has more traditional headers already.
    [header1][header2][header3]
    Id IdDiscoveryConfiguration IdDiscoveryConfigurationGroup
    102 58 1
    1 1 2
    2 2 2
    3 3 3
    4 4 3
    5 5 4
    6 6 4
    16 16 7
    86 43 7
    87 44 7
    88 45 7
    89 46 7
    90 47 7
    91 48 7
    92 49 7
    93 50 7
    94 51 7
    95 52 7
    96 53 9
    103 59 9

    maybe I'm missing its not so easy in powershell however python this is super straight forward.
    import pandas as pd
    data_frame = pd.read_table('path', sep='\s+')
    df.shape
    (20, 3)
    df.Id or df.IdDiscoveryConfiguration

    which will list out everything nicely and putting in simple comparison logic to rebuild stuff is simple once you can reference the data.

    Powershell leaves me with a (20,1) shape which is not going to work.

    I have the MOL books and I'm not really sure if I missed anything in there that would help me. Again I want this all in powershell so I can give it away for others instead of them having to send me stuff everytime to get this bit of processing done.

    Thanks,

    ~Kristian

  • #60829

    Olaf Soyk
    Participant

    It's a little hard to believe that there is no way to get data from a data base as a table. Anyway this here might help you transforming your list to a table:
    stackoverflow – Powershell convert list formatted record into table format
    Or this guides to a similar direction:
    Create collections of custom objects
    and ... I'm not sure but this might be helpful for you too:
    Join-Object

  • #60835

    Max Kozlov
    Participant

    You can try to use ConvertFrom-String -Template, but I'm think modifying source of your data into objects much easier 🙂

  • #60846

    Kristian
    Participant

    @Olaf Soyk
    Thank you very much for the link Join-Object

    @Max Kozlov
    I went ahead and used Notepad++ for the table that already had headers the way you would expect and turned it into a nice csv and it worked great. The others I was able to come up with a solution.

    Forgive me for not posting the code yet I will edit this post before the end of the night and show you what I came up with 🙂 I think you will like it.

    • #60871

      Kristian
      Participant

      One of my idea's was to read in the text file to a hash table. You can see what I mean assuming you have more than 1 network adapter. Things that would need to be fixed. The keys have extra whitespace that would need to be removed. Also would need to check to see if key is already created if hash table ContainsKey() then could just add or append to its value?

       
      #Get-Netadapter | fl > c:\test.txt
      
      $test = Get-Content -Path C:\test.txt
      
      $global:i = 0
      $global:count = $test.Count
      
      $array0 = @{}
      
      foreach ($line in $test){
      
          $key = $test[$i].Split(':')[0]
          $value = $test[$i].Split(':')[1]  
          $array0.Add("$key","$value")
          
          $i = $i + 1
          if ($i -ge $count) {break}
      
          }
      
  • #60897

    Max Kozlov
    Participant

    you not use $line in your code but use $test[$i], this way is not 'foreach' way 🙂
    you did not explain, you have one file for one device or may devices in one file.

    if many in one than you can 'append' data into hash but it is not practically usable.

    but better if you start with saving your data to array of hashes/objects/ or hash of hashes/objects/
    something like this (if your data guaraneed to not have the same fields for one object)
    [not tested]

    $data = {}.Invoke() # use collection instead of PS array for fast adding
    $current = @{} # init current object hash
    foreach ($line in (Get-Content $datafile) { # foreach have current line in $line variable
      $name, $value = $line -split ' : ' # use PS split for regex usage instead of char array for string .split()
      if ($current.ContainsKey($name)) { # current info already have this key
         # object filled, save it to array
         $data.Add($current)
         $current=@{}      # reinit current object hash
      }
      #fill new field of current object 
      $current[$name]=$value
    }
    # save last object
    if ($current.Count) {
       $data.Add($current)
    }
    

    this way you have collection of hashes (or you can have connection of objects if you save it as
    $data.Add([PSCustomObject]$current) )

    you also can have hash of objects if you have unique field (id, for example)

    #initializing
    $data = @{}
    # ...
    # saving
    $data[$current.ID] = [PSCustomObject]$current
    

    thus you can get all of your device data with $data[$index] or $data[$deviceid]

  • #61345

    Kristian
    Participant

    I ended up using SMSS to export the tables I wanted to excel. Then it was very simple to convert xls file to csv notepad++.

    $device = Import-Csv -Path C:\temp\device.csv
    $group = Import-Csv -Path C:\temp\group.csv
    $map = Import-Csv -Path C:\temp\map.csv
    
    $device | ft -AutoSize
    
    Id Address      SNMPGetCommunity CIMUserName        IPMIUserName SubnetMask      WSMANUserName
    -- -------      ---------------- -----------        ------------ ----------      -------------
    1  10.0.147.2   public                                           255.255.255.128 root
    2  10.0.147.4   public                                           255.255.255.128 root
    5  10.0.148.130 public                                           255.255.255.128 root
    6  10.0.147.3   public                                           255.255.255.128
    7  10.0.147.5   public                                           255.255.255.128
    8  10.0.147.20  public                                           255.255.255.128
    9  10.0.147.22  public                                           255.255.255.128
    
    
    $group | ft -AutoSize
    
    Id GroupName  SNMPGetCommunity SubnetMask
    -- ---------  ---------------- ----------
    1  All Ranges
    2  OOB-iDRAC  public           255.255.255.128
    4  OOB-CMC    public           255.255.255.128
    5  HV-Cluster public           255.255.255.128
    6  EQL        public           255.255.255.128
    
    
    $map | ft -AutoSize
    
    Id IdDiscoveryConfiguration IdDiscoveryConfigurationGroup
    -- ------------------------ -----------------------------
    1  1                        2
    2  2                        2
    5  5                        4
    6  6                        5
    7  7                        5
    8  8                        6
    9  9                        6
    
    
    $tempmap = Join-Object -Left $map -Right $group -where {$args[0].IdDiscoveryConfigurationGroup -eq $args[1].Id}
    -LeftProperties "IdDiscoveryConfiguration" -RightProperties "GroupName","SNMPGetCommunity" -Type AllInBoth
    $tempmap | ft -AutoSize
    
    IdDiscoveryConfiguration GroupName  SNMPGetCommunity
    ------------------------ ---------  ----------------
    1                        OOB-iDRAC  public
    2                        OOB-iDRAC  public
    5                        OOB-CMC    public
    6                        HV-Cluster public
    7                        HV-Cluster public
    8                        EQL        public
    9                        EQL        public
                             All Ranges
    
    $finalmap = Join-Object -left $device -Right $tempmap -where {$args[0].Id -eq $args[1].IdDiscoveryConfiguration}
     -LeftProperties "Address","SubnetMask","WSMANUserName","CIMUserName","SNMPGetCommunity" -RightProperties "GroupName" -T
    ype AllInBoth
    $finalmap | ft -AutoSize
    
    Address      SubnetMask      WSMANUserName CIMUserName        SNMPGetCommunity GroupName
    -------      ----------      ------------- -----------        ---------------- ---------
    10.0.147.2   255.255.255.128 root                             public           OOB-iDRAC
    10.0.147.4   255.255.255.128 root                             public           OOB-iDRAC
    10.0.148.130 255.255.255.128 root                             public           OOB-CMC
    10.0.147.3   255.255.255.128                                  public           HV-Cluster
    10.0.147.5   255.255.255.128                                  public           HV-Cluster
    10.0.147.20  255.255.255.128                                  public           EQL
    10.0.147.22  255.255.255.128                                  public           EQL
                                                                                   All Ranges
    

You must be logged in to reply to this topic.