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.
-
AuthorPosts
-
-
November 15, 2016 at 10:25 pm #57824
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?
-
November 15, 2016 at 10:27 pm #57827
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?
-
November 15, 2016 at 10:39 pm #57829
Probably due to ignorance...can you give me a small example of putting something into a data structure?
-
November 15, 2016 at 10:45 pm #57832
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.
-
November 16, 2016 at 2:40 pm #57868
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
-
November 16, 2016 at 4:09 pm #57898
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?
-
November 16, 2016 at 4:28 pm #57907
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.
-
November 16, 2016 at 4:34 pm #57916
Can you give me an example of an array of PSObjects?
Thank you again for your advice!
-
November 16, 2016 at 4:51 pm #57920
$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}
-
November 16, 2016 at 5:59 pm #57932
Very helpful, thanks!
-
November 16, 2016 at 7:43 pm #57935
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!
-
November 16, 2016 at 8:24 pm #57937
$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
-
November 16, 2016 at 8:29 pm #57938
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.
-
November 17, 2016 at 5:00 pm #58048
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
-
November 17, 2016 at 8:07 pm #58097
Fixed it with:
$path = $path -replace "'", "`'`'" $sql = "INSERT INTO Share_Table (Path,Permission) VALUES('$Path','$permission')"
-
-
AuthorPosts
- The topic ‘Ideas on how to parse through a file’ is closed to new replies.