Ideas on how to parse through a file

Welcome Forums General PowerShell Q&A Ideas on how to parse through a file

  • This topic has 14 replies, 3 voices, and was last updated 3 years ago by
    Participant
    .
Viewing 14 reply threads
  • Author
    Posts
    • #57824
      Participant
      Topics: 21
      Replies: 30
      Points: 0
      Rank: Member

      I wrote a script that pulls all the shares and all those with access to it.

      The file looks like:

      \\fileserver\Marketing\ACT
      DOMAIN\admibistrator
      NT AUTHORITY\SYSTEM
      DOMAIN\Domain Admins
      DOMAIN\Information Technology
      DOMAIN\Marketing
      BUILTIN\Administrators
      DOMAIN\Administration_High
      \\fileserver\Marketing\Annual Meetings
      DOMAIN\admibistrator
      NT AUTHORITY\SYSTEM
      DOMAIN\Domain Admins
      DOMAIN\Information Technology
      DOMAIN\Marketing
      BUILTIN\Administrators
      DOMAIN\Administration_High
      

      Where \\.... is the server share and the names underneath it are the users/groups with access...once it hits a \\ it will represent a new share/users.

      What would be a good way to go about parsing the file so I could say like, search for a share and it would show permissions?

      Would a hash table be best for something like this?

    • #57827
      Keymaster
      Topics: 13
      Replies: 4872
      Points: 1,844
      Helping HandTeam Member
      Rank: Community Hero

      Honestly? Modify your original script to put this into a SQL Express database. One column for UNC, another for permission. Then querying a given UNC would be child's play.

      Otherwise you're looking at a slow process of parsing that file, a memory-hungry (and slow) process of making a data structure in memory... why not just put it into a data structure to begin with?

    • #57829
      Participant
      Topics: 21
      Replies: 30
      Points: 0
      Rank: Member

      Probably due to ignorance...can you give me a small example of putting something into a data structure?

    • #57832
      Keymaster
      Topics: 13
      Replies: 4872
      Points: 1,844
      Helping HandTeam Member
      Rank: Community Hero

      Sure. You could also read our free ebook on the subject, "Creating Trend and Historical Reports." But, briefly:

      Suppose I have a SQL Express instance on my local machine, containing a database named MYDB and a table named MYTABLE. The table has a column named UNC and one named PRINCIPAL, both of which are strings.

      $unc = "Current file share UNC"
      $princ = "Current username"
      
      $connection_string = "Server=.\SQLExpress;AttachDbFilename=C:\MyFolder\MyDataFile.mdf;Database=dbname;Trusted_Connection=Yes;"
      $connection = New-Object System.Data.SqlClient.SqlConnection
      $connection.ConnectionString = $connection_string
      $connection.Open()
      $command = New-Object System.Data.SqlClient.SqlCommand
      $command.Connection = $connection
      
      $sql = "INSERT INTO MYTABLE (UNC,PRINCIPAL) VALUES('$unc','$princ')"
      $command.CommandText = $sql
      $command.ExecuteNonQuery()
      
      $connection.close()
      

      Approximately. That's top-of-head, not code I just tested. Once it's all set up, it's really just three lines to make each query and execute it. Database populated, easy to run reports (SQL Express can come with SQL Server Reporting Services), easy to make queries. Again – I did a whole ebook on this, and it isn't terribly long.

    • #57868
      Ron
      Participant
      Topics: 0
      Replies: 199
      Points: 2
      Rank: Member

      Yes, if you control the source data creation, you should put it in a better format to begin with, maybe XML?

      But this should parse your current output into a hashtable so you can retrieve it by key.

      $lines = @'
      \\fileserver\Marketing\ACT
      DOMAIN\admibistrator
      NT AUTHORITY\SYSTEM
      DOMAIN\Domain Admins
      DOMAIN\Information Technology
      DOMAIN\Marketing
      BUILTIN\Administrators
      DOMAIN\Administration_High
      \\fileserver\Marketing\Annual Meetings
      DOMAIN\admibistrator2
      NT AUTHORITY\SYSTEM
      DOMAIN\Domain Admins
      DOMAIN\Information Technology
      DOMAIN\Marketing
      BUILTIN\Administrators
      DOMAIN\Administration_High
      '@
      
      $hash = @{}
      
      foreach ($line in $($lines -split "`r`n")) {
        if ($line.Substring(0,2) -eq '\\') {
          $hash.Add($line, @())
          $curkey = $line
        } else {
          $hash."$curkey" += $line
        }
      }
      
      $hash |ft -auto
      
    • #57898
      Participant
      Topics: 21
      Replies: 30
      Points: 0
      Rank: Member

      Hey Ron, thanks for the additional help.

      I noticed your first example doesnt work, but i assume thats because the array 'lines' isnt comma deliminated.
      Edit: not an array, but a here-string...still doesnt appear to be working but i'll look at it.

      I like the idea of an xml, but i'm not sure how to create an xml variable and save to it, i've only read from existing xml files before.

      $shares = Get-WmiObject win32_share -ComputerName "vmibfilestore"
      
      foreach($share in (($shares).name | Where-Object {$_ -eq "Marketing"}))
      {
          $permissions = get-childitem -Recurse "\\server\$($share)" | Where-Object{$_.PSIsContainer}
      
          foreach($permission in $permissions)
          {
              $acls = $permission | Get-Acl
      
              #split on :: as path shows as Microsoft.PowerShell.Core\FileSystem::\\server\whatever
              $path = ($acls.path).split("::")[2]
              $users = (@($acls.Access).identityreference).value
          }
      }
      

      Is there an easy way to create an xml variable and save path>line for users under path?

    • #57907
      Ron
      Participant
      Topics: 0
      Replies: 199
      Points: 2
      Rank: Member

      The example should work as posted, you will likely need to adapt it for your data file. If you use get-content to read the file, it's already an array of lines and you won't need to split it.

      For XML, I would just create an array of PSObjects and then export to XML. Then you can read those objects back in with a corresponding import on the other end.

    • #57916
      Participant
      Topics: 21
      Replies: 30
      Points: 0
      Rank: Member

      Can you give me an example of an array of PSObjects?

      Thank you again for your advice!

    • #57920
      Ron
      Participant
      Topics: 0
      Replies: 199
      Points: 2
      Rank: Member
      $array=@()
      
      $object = ''|Select Server,Permissions
      $object.Server = 'Server 1'
      $object.Permissions = @('user1','user2')
      
      $array += $object
      
      $object = ''|Select Server,Permissions
      $object.Server = 'Server 2'
      $object.Permissions = @('user3','user4')
      
      $array += $object
      
      $array | ft -auto
      
      Server   Permissions
      ------   -----------
      Server 1 {user1, user2}
      Server 2 {user3, user4}
      
      
      
    • #57932
      Participant
      Topics: 21
      Replies: 30
      Points: 0
      Rank: Member

      Very helpful, thanks!

    • #57935
      Participant
      Topics: 21
      Replies: 30
      Points: 0
      Rank: Member

      Say I wanted to go down the database route, I have a full fledged copy of SQL standard on our server I can build a DB on.

      For the connection string would it be more like this...?

      $connection_string = "Server=\\servername\SQL2014;AttachDbFilename=\\server\MyFolder\MyDataFile.mdf;Database=dbname;Trusted_Connection=Yes;"
      

      Edit: Nevermind, figured that part out ;)thanks guys!

    • #57937
      Participant
      Topics: 21
      Replies: 30
      Points: 0
      Rank: Member
      $Path = "Current file share UNC"
      $Permission = "Current username"
      
      $connection_string = "Server = server; Database = AD_Validation; Integrated Security = True"
      $connection = New-Object System.Data.SqlClient.SqlConnection
      $connection.ConnectionString = $connection_string
      $connection.Open()
      $command = New-Object System.Data.SqlClient.SqlCommand
      $command.Connection = $connection
      
      $sql = "INSERT INTO Permissions (Path,Permissions) VALUES('$Path','$Permission')"
      $command.CommandText = $sql
      $command.ExecuteNonQuery()
      
      $connection.close()
      

      Is giving me this error:

      Exception calling "ExecuteNonQuery" with "0" argument(s): "String or binary data would be truncated.
      The statement has been terminated."
      At line:1 char:1
      + $command.ExecuteNonQuery()
      + ~~~~~~~~~~~~~~~~~~~~~~~~~~
          + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
          + FullyQualifiedErrorId : SqlException
      
    • #57938
      Keymaster
      Topics: 13
      Replies: 4872
      Points: 1,844
      Helping HandTeam Member
      Rank: Community Hero

      That error usually comes from your SQL statement. I'd check to make sure the field sizes are large enough – e.g., use VARCHAR(MAX) or something. It's telling you that it couldn't insert your data because doing so would have truncated some of it – meaning the field in the database is too small for the data.

      $sql = "INSERT INTO Permissions (Path,Permissions) VALUES('$Path','$Permission')"
      Write-verbose "$sql"
      $command.CommandText = $sql
      $command.ExecuteNonQuery()
      

      Can be useful for troubleshooting. Set $VerbosePreference='Continue' to enable the verbose statement.

    • #58048
      Participant
      Topics: 21
      Replies: 30
      Points: 0
      Rank: Member

      Ah thanks, yeah it was the filesize on nchar.

      I have some files that have ' in the file name...

      $path = "\\server\folder\6_President's Report"
      
       $sql = "INSERT INTO Share_Table (Path,Permission) VALUES(`'$Path`',`'$permission`')"
      

      This doesnt seem to work either...How can i pass a file structure with a ' into the $SQL variable command?

      if i change it to

              $sql = "INSERT INTO Share_Table (Path,Permission) VALUES(""$Path"",""$permission"")"
      

      I get a different error:

      \\server\folddder\Board Info\Board Director Listings
      Exception calling "ExecuteNonQuery" with "0" argument(s): "Invalid column name '\\server\folder\Board
      Info\Board Director Listings'.
      Invalid column name 'domain\admibistrator NT AUTHORITY\SYSTEM'."
      At line:18 char:9
      +         $command.ExecuteNonQuery() | out-null
      +         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
          + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
          + FullyQualifiedErrorId : SqlException
      
    • #58097
      Participant
      Topics: 21
      Replies: 30
      Points: 0
      Rank: Member

      Fixed it with:

              $path = $path -replace "'", "`'`'"
              $sql = "INSERT INTO Share_Table (Path,Permission) VALUES('$Path','$permission')"
      
Viewing 14 reply threads
  • The topic ‘Ideas on how to parse through a file’ is closed to new replies.