Ideas on how to parse through a file

This topic contains 14 replies, has 3 voices, and was last updated by Profile photo of Aaron Aaron 2 weeks, 6 days ago.

  • Author
    Posts
  • #57824
    Profile photo of Aaron
    Aaron
    Participant

    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
    Profile photo of Don Jones
    Don Jones
    Keymaster

    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
    Profile photo of Aaron
    Aaron
    Participant

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

  • #57832
    Profile photo of Don Jones
    Don Jones
    Keymaster

    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
    Profile photo of Ron
    Ron
    Participant

    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
    Profile photo of Aaron
    Aaron
    Participant

    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
    Profile photo of Ron
    Ron
    Participant

    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
    Profile photo of Aaron
    Aaron
    Participant

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

    Thank you again for your advice!

  • #57920
    Profile photo of Ron
    Ron
    Participant
    $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
    Profile photo of Aaron
    Aaron
    Participant

    Very helpful, thanks!

  • #57935
    Profile photo of Aaron
    Aaron
    Participant

    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
    Profile photo of Aaron
    Aaron
    Participant
    $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
    Profile photo of Don Jones
    Don Jones
    Keymaster

    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
    Profile photo of Aaron
    Aaron
    Participant

    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
    Profile photo of Aaron
    Aaron
    Participant

    Fixed it with:

            $path = $path -replace "'", "`'`'"
            $sql = "INSERT INTO Share_Table (Path,Permission) VALUES('$Path','$permission')"
    

You must be logged in to reply to this topic.