I want to import file infos and MD5 Hash in SQL Server table using Write-ObjectToSQL Module. I'm trying to do this in one line if possible. Here's what I have:
gci -file|select Directory,Name,Extension,Length,CreationTime,LastAccessTime,LastWriteTime,@{Name='MD5'; Expression={Get-FileHash $_ -Algorithm MD5|select Hash}}|Write-ObjectToSQL -Server ABC -Database PDOC -TableName Files
And here's what I get in SQL Server:
I don't get MD5 column nor Directory column. Here's th Get-Member info:
PS C:\Users\pollusb> gci -file|select Directory,Name,Extension,Length,@{Name='MD5'; Expression={Get-FileHash $_ -Algorithm MD5|select Hash}}|gm
TypeName: Selected.System.IO.FileInfo
Name MemberType Definition
---- ---------- ----------
Equals Method bool Equals(System.Object obj)
GetHashCode Method int GetHashCode()
GetType Method type GetType()
ToString Method string ToString()
Directory NoteProperty DirectoryInfo Directory=C:\Users\pollusb
Extension NoteProperty string Extension=.pdoc
Length NoteProperty long Length=65
MD5 NoteProperty Selected.System.Management.Automation.PSCustomObject MD5=@{Hash=C30CCC4447297D94FE10F54C18DBD5F9}
Name NoteProperty string Name=345.pdoc
So SQL Server will import only NoteProperties that are not object.
Now, if we look at what I get before the Write-ObjectToSQL. I removed the Time columns to get a nicier result.
PS C:\Users\pollusb> gci -file|select Directory,Name,Extension,Length,@{Name='MD5'; Expression={Get-FileHash $_ -Algorithm MD5|select Hash}} |
ft -AutoSize
Directory Name Extension Length MD5
--------- ---- --------- ------ ---
C:\Users\pollusb 345.pdoc .pdoc 65 @{Hash=C30CCC4447297D94FE10F54C18DBD5F9}
C:\Users\pollusb deletedRoute.txt .txt 51 @{Hash=DE585876A6249B9FDF176697F5D35EA4}
C:\Users\pollusb dir.txt .txt 11815 @{Hash=77F3CE7F713966D4F8E39D12E1D77947}
C:\Users\pollusb open.ps1 .ps1 116 @{Hash=F5B0126D5B0A5040BE34FDC257ADD7F4}
C:\Users\pollusb pdoc.bat .bat 34 @{Hash=E3630400A370F6D94ECE198407FDB151}
My question is there a way to transform the MD5 column to have only MD5 result and not the @{Hash= and the closing } AND to transform the Directory column in a string ?
Converting the directory name to a string instead of System.IO.DirectoryInfo
, replace the Select Directory with a custom Directory:
@{n='Directory'; e={$_.Directory -as [String]}}
When you get the file hash, expand the property:
Get-FileHash $_ -Algorithm MD5 | select -ExpandProperty Hash
That will return just the actual hash, not an MD5 object containing a HASH property.
Adding the new properties:
gci -file admin.bat | select @{n='Directory'; e={$_.Directory -as [String]}},
Name,
Extension,
Length,
@{Name='MD5'; Expression={Get-FileHash $_ -Algorithm MD5 | select -ExpandProperty Hash}
} | gm
Which gets you some String NoteProperty's:
Name MemberType Definition
---- ---------- ----------
Equals Method bool Equals(System.Object obj)
GetHashCode Method int GetHashCode()
GetType Method type GetType()
ToString Method string ToString()
Directory NoteProperty System.String Directory=D:\
Extension NoteProperty System.String Extension=.bat
Length NoteProperty System.Int64 Length=2901
MD5 NoteProperty System.String MD5=876056EB083579DFF07E85015D4C0272
Name NoteProperty System.String Name=admin.bat