PS scripts Spiceworks

# Dir of Ticket uploads
$dir1 = "C:\Program Files (x86)\Spiceworks\data\uploads\Ticket"
# Dir of unwanted files from signatures
$dir2 = "C:\Program Files (x86)\Spiceworks\data\uploads\Hash"

# Get-FileHash out of Ticket uploads folder, exclude pdf files
$var1 = get-childitem -path $dir1 -Recurse | where name -NotLike "*.PDF" | Get-FileHash -Algorithm MD5
# Get-FileHash out of unwanted files from signatures
$var2 = get-childitem -path $dir2 | Get-FileHash -Algorithm MD5

# compare both hashes if Ticket uploads matches unwanted files from signatures mark for deletion
$marked4Deletion = foreach ($attachment in $var1)
{
if ($var2.Hash -contains $attachment.Hash)
{$attachment.Path}
}
# if there is nothing to delete, stop the script from executing further steps
if ($marked4Deletion -eq $null)
{return "nothing to delete"}

# delete unwanted files from $dir2
$marked4Deletion | Remove-Item -Force

# Get filenames only
$marked4Deletion = foreach ($q in $marked4Deletion) {Split-Path $q -leaf}
#split on comment_id
$marked4Deletion = foreach ($q in $marked4Deletion) {$q.Split([string[]]"-",[StringSplitOptions]"None")[0]}
# Prepare where clause
$whereclause = $marked4Deletion -join ','
# create sql statment for set deletion
$sqlstatement = "delete from comments where id in ($whereclause)"


# connect to database
$conn = new-object System.Data.Odbc.OdbcConnection
$conn.connectionstring = "DSN=Spiceworks"
$conn.open()
# delete the comments
$cmd = New-object System.Data.Odbc.OdbcCommand($sqlstatement,$conn)
$dataset = New-Object System.Data.DataSet
(New-Object System.Data.Odbc.OdbcDataAdapter($cmd)).Fill($dataSet)
$conn.Close()