Ideas on how to parse through a file

PowerShell Help
1

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?

2

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?

3

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

4

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.

5

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
6

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?

7

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.

8

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

Thank you again for your advice!

9 
$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}
10

Very helpful, thanks!

11

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!

12 
$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
13

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.

14

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
15

Fixed it with:

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