Ad Space here

Welcome Guest! To enable all features please Login or Register.

Notification

Icon
Error

Share
Options
Go to last post Go to first unread
Dustin Higgins  
#1 Posted : Sunday, November 24, 2019 11:33:01 AM(UTC)
Dustin Higgins

Rank: Advanced Member

Groups: Registered
Joined: 7/1/2018(UTC)
Posts: 64
_United States

Thanks: 1 times
Was thanked: 6 time(s) in 6 post(s)

PowerShell, ScriptMethods, and a Real Problem

I have always been fascinated with PowerShell Custom Objects.  Create a Custom Object and record every last detail.  Custom objects to me really have always been a data record.  They can easily be exported to CSV and viewed in a spreadsheet.

I am really fascinated when Custom Objects are enabled to do really neat things.  The ScriptMethod member type can be added to objects to give them methods that can be called to execute tasks.

The real problem that I had to solve was identifying files on a file system that had certain attributes.  They were broken links that had to be fixed.  Not knowing where they were, my immediate thought is that this was a job for the Get-ChildItem -Recurse command that could be started at the root of the file system.  I would build an object for each file, add the name, file path, attributes (converted to binary), and record whether or not it was bad.  I was quick to realize that this was going to take a long time.  Also, there were a number of file paths that I couldn't access because the path was too long.

​​​​​​​

I am not a file system expert, and I am not a database expert.  I thought long and hard on how to tackle this problem.  What if I built a function that recursed through the directory structure?  What if my file and directory objects could insert themselves into a database?  My next thought was that I would index the file system and build a database that had all of the answers, and could easily be retrieved quickly through SQL queries.  I went for it.  Since the database access is easy through this post Object-Oriented Approach to PowerShell and SQL Server, all I would have to do was build a ScriptMethod that returned a SQL command that I could easily insert into a table.

Here is the function that returns the object that can be inserted into a database:

Code:

function Get-FileObj {     <#     .SYNOPSIS     Function to get a File/Directory Object that can insert itself into a database     .DESCRIPTION     Function to get a File/Directory Object that can insert itself into a database     #>     $Obj = [PSCustomObject]@{             Name = $null         FullName = $null         FullNameLength = $null         LastWriteTime = $null         Type = $null         ATTRIB_Integer = $null         ATTRIB_Binary = $null     }     # Add the GetPropertyList Method     $Obj | Add-Member -Name "GetPropertyList" -MemberType ScriptMethod -Value {         # Used this to give me a nice list of properties to work with since it is used a good bit         $PropList = New-Object System.Collections.Generic.List[string]         foreach ($p in $this.PSObject.Properties) {             $PropList.Add($p.Name)         }         return $PropList     }     # Add the GetBinaryAttribute Method     $Obj | Add-Member -Name "GetBinaryAttribute" -MemberType ScriptMethod -Value {         $this.ATTRIB_Binary = [Convert]::ToString($this.ATTRIB_Integer,2)     }     # Add  the GetSqlInsertCmd Method - Pass only the Database Table Name     $Obj | Add-Member -Name "GetSqlInsertCmd" -MemberType ScriptMethod -Value {         param (             [Parameter(Mandatory=$true)]             [string]$Table         )         # Get a list of the object properties         $PropList = $this.GetPropertyList()         # The ParamHash holds all of the field names and values for the parameterized database command         $ParamHash = @{}         # Look through each property of the object, if it is $null, add the DBNull value to the hash         # If it is not null, add the object property value to the hash         foreach ($p in $PropList) {             if ($this.($p) -eq $null) {                 $ParamHash.Add(($p),[System.DBNull]::Value)             } else {                 $ParamHash.Add(($p),$this.($p))             }         }         # Sb will hold the text for the SQL INSERT command         $Sb = New-Object System.Text.StringBuilder         [void]$Sb.AppendLine("INSERT INTO $($Table) ($([string]::Join(",",$PropList)))")         [void]$Sb.AppendLine("VALUES (@$([string]::Join(",@",$PropList)))")         # Get the actual SQL command by passing the command text and the parameter hash         $Cmd = Get-SqlCommand -CommandText $Sb.ToString() -ParamHash $ParamHash         # Return the SQL command         return $Cmd     }     # Return the object     Return $Obj }

Here is an example of how to use it:

Code:

$File = Get-Item "C:\Path\To\TestFile.txt"

$FileObj = Get-FileObj $FileObj.Name = $File.Name $FileObj.FullName = $File.FullName $FileObj.FullNameLength = $File.FullName.Length $FileObj.LastWriteTime = $File.LastWriteTime $FileObj.Type = "File" $FileObj.ATTRIB_Integer = $File.Attributes.value__

# Call the method to load the binary attribute

$FileObj.GetBinaryAttribute()

# Get the SQL command to load the object into the Files table

$SqlCmd = $FileObj.GetSqlInsertCmd("Files")

# Get the Database connection

$connString = "Data Source=localhost\DHB;Initial Catalog=FilesDB;Integrated Security=true;"

$dbObj = Get-DatabaseObject $connString

# Open the connection

$dbObj.OpenConnection()

# Run the INSERT command

$SqlCmd = $dbObj.ExecuteNonQuery($SqlCmd)

# Close the connection

$dbObj.CloseConnection()

The parameterized INSERT statement looks like this:

Code:

INSERT INTO Files (Name,FullName,FullNameLength,LastWriteTime,Type,ATTRIB_Integer,ATTRIB_Binary)
VALUES (@Name,@FullName,@FullNameLength,@LastWriteTime,@Type,@ATTRIB_Integer,@ATTRIB_Binary)

I used SQL Server Express to store this data.   Of course, a database table called Files would have to be created.  That can easily be accomplished with a CREATE TABLE command.  When I casually mentioned that I made a database out of 1.5 million records from a file system, people just kind of looked at me funny.  It doesn't really bother me at all since I quickly had the answer for this one.  Since I had all of the info in the Custom Objects, I was able to programmatically map directories at a lower level to get the attributes of files that had file paths that were too long.

Feedback is welcomed and follow Dustin Higgins on Twitter

DHB

Edited by user Thursday, January 16, 2020 10:45:53 PM(UTC)  | Reason: Not specified

Sponsor
Ad Space here
Dustin Higgins  
#2 Posted : Thursday, January 16, 2020 11:39:00 AM(UTC)
Dustin Higgins

Rank: Advanced Member

Groups: Registered
Joined: 7/1/2018(UTC)
Posts: 64
_United States

Thanks: 1 times
Was thanked: 6 time(s) in 6 post(s)

When all of your file information is in a database, it is easy to find the file attributes you are looking for:

Code:
select * from Files WHERE Attrib_Binary Like '%1____________' -- Offline O
select * from Files WHERE Attrib_Binary Like '%1___________' -- Compressed C
select * from Files WHERE Attrib_Binary Like '%1__________' -- Reparse Point L
select * from Files WHERE Attrib_Binary Like '%1_________' -- Sparse File P
select * from Files WHERE Attrib_Binary Like '%1________' -- Temporary T
select * from Files WHERE Attrib_Binary Like '%1_______' -- Normal N
select * from Files WHERE Attrib_Binary Like '%1______' -- Encrypted X
select * from Files WHERE Attrib_Binary Like '%1_____' -- Archive A
select * from Files WHERE Attrib_Binary Like '%1____' -- Directory D
select * from Files WHERE Attrib_Binary Like '%1___' -- Volume Label (Obsolete)
select * from Files WHERE Attrib_Binary Like '%1__' -- System S
select * from Files WHERE Attrib_Binary Like '%1_' -- Hidden H
select * from Files WHERE Attrib_Binary Like '%1' -- ReadOnly R

Edited by user Thursday, January 16, 2020 11:44:36 AM(UTC)  | Reason: Not specified

Rss Feed  Atom Feed
Users browsing this topic
Forum Jump  
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.