Import-Module DriveMapping # "C:\Users\shayes\Downloads\DriveMapping.psm1" Function Resolve-Error($ErrorRecord=$Error[0]) { $ErrorRecord | Format-List * -Force $ErrorRecord.InvocationInfo | Format-List * $Exception = $ErrorRecord.Exception For ($i = 0; $Exception; $i++, ($Exception = $Exception.InnerException)) { "$i" * 80 $Exception | Format-List * -Force } } Function Connect-mySQL([string]$user,[string]$pass,[string]$mySQLHost,[string]$database) { [void][System.Reflection.Assembly]::LoadWithPartialName("MySql.Data") $connStr = "server=" + $mySQLHost + ";port=3306;uid=" + $user + ";pwd=" + $pass + ";database=" + $database + ";Pooling=FALSE" $conn = New-Object MySql.Data.MySqlClient.MySqlConnection($connStr) $conn.Open() return $conn } Function Disconnect-MySQL($conn) { $conn.Close() } $user = 'root' $pass = '3island6' $database = 'cloudusage' $mySqlHost = 'localhost' $conn = Connect-MySql $user $pass $mySqlHost $database Function MySQLNonQuery($conn, $query) { $command = $conn.CreateCommand() $command.CommandText = $query $rows = $command.ExecuteNonQuery() $command.Dispose() If ($rowsUpdated) { return $rowUpdated } Else { return $false } } Function MySQLQuery($conn, [string]$query) { $cmd = New-Object MySql.Data.MySqlClient.MySqlCommand($query, $conn) $dataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($cmd) $dataSet = New-Object System.Data.DataSet $dataAdapter.Fill($dataSet, "data") $cmd.Dispose() return $dataSet.Tables["data"] } Function Get-FolderSize { [CmdletBinding()] Param ( [string]$path ) $totalSize = (Get-ChildItem $path -recurse -force | measure-object -property length -sum) [double]$totalSize.sum / 1024 / 1024 / 1024 } Function Get-Capacity { [CmdletBinding()] Param ( [string]$computer, [string]$disk ) $cap = (Get-WmiObject Win32_LogicalDisk -ComputerName $computer -Filter "DeviceID='$disk'") $cap.size / 1024 / 1024 / 1024 } Function Get-FreeSpace { [CmdletBinding()] Param ( [string]$computer, [string]$disk ) $free = (Get-WmiObject Win32_LogicalDisk -ComputerName $computer -Filter "DeviceID='$disk'") $free.FreeSpace / 1024 / 1024 / 1024 } $getDB = "SELECT * FROM servers" $db = MySQLQuery $conn $getDB | Select -Skip 1 ForEach ($row in $db) { $parent = $($row.ParentFolder).Replace('\\','\') $id = $($row.ID) $drive = $($row.Drive) $bdr = $($row.CloudServer) $folder = $($row.Folder) $client = "'" + "$($row.Client)" + "'" If ([string]::IsNullOrWhiteSpace($drive)) { } Else { $folderPath = "\\" + "$($bdr)" + "\" + "$($drive)" + "`$\" + "$($parent)" + "\" + "$($folder)" } If ($bdr -eq "MSI-COLO-NAS") { $u = "msi\msibu" $p = 'N&78(*7Yui' | ConvertTo-SecureString -AsPlainText -Force $creds = New-Object System.Management.Automation.PSCredential($u,$p) $uncPath = "\\" + "$($bdr)" + "\" + "$($parent)" + "\" + "$($folder)" Connect-AuthNetworkDrive -DriveLetter Z: -UNCPath $uncPath -Credential $creds $size = Get-FolderSize "Z:\" $capacity = Get-Capacity -Computer "$($env:COMPUTERNAME)" -Disk "Z:" $freeSpace = Get-FreeSpace -Computer "$($env:COMPUTERNAME)" -Disk "Z:" $pctFree = $freeSpace / $capacity Disconnect-AuthNetworkDrive -DriveLetter Z: } ElseIf ($bdr -eq "MSISYN01"){ $u = "msi\msibu" $p = 'N&78(*7Yui' | ConvertTo-SecureString -AsPlainText -Force $creds = New-Object System.Management.Automation.PSCredential($u,$p) $uncPath = "\\" + "$($bdr)" + "\" + "$($parent)" + "\" + "$($folder)" Connect-AuthNetworkDrive -DriveLetter Z: -UNCPath $uncPath -Credential $creds $size = Get-FolderSize "Z:\" $capacity = Get-Capacity -Computer "$($env:COMPUTERNAME)" -Disk "Z:" $freeSpace = Get-FreeSpace -Computer "$($env:COMPUTERNAME)" -Disk "Z:" $pctFree = $freeSpace / $capacity Disconnect-AuthNetworkDrive -DriveLetter Z: } Else { $size = Get-FolderSize "$($folderPath)" $capacity = Get-Capacity -Computer "$($bdr)" -Disk "$($drive):" $freeSpace = Get-FreeSpace -Computer "$($bdr)" -Disk "$($drive):" $pctFree = $freeSpace / $capacity } Write-Host "$($row.Server)" $query = "UPDATE servers SET Size = '$size', Capacity = '$capacity', FreeSpace = '$freeSpace', pctFree = '$pctFree' WHERE ID = '$id'" $update = MySQLQuery $conn $query } Clear-Variable -Name query Clear-Variable -Name update Clear-Variable -Name getDB Clear-Variable -Name db $getDB = "SELECT * FROM servers" $db = MySQLQuery $conn $getDB | Select -Skip 1 $date = (Get-Date -Format "yyyy-MM-dd") $date = '`' + "$($date)" + '`' $addQuery = "ALTER TABLE " + '`history` ' + "ADD COLUMN $($date) decimal(10,2) NULL DEFAULT NULL AFTER " + '`Client`' + ";" $add = MySQLQuery $conn "$($addQuery)" $clients = ($db | Select Client -Unique).Client ForEach ($client in $clients) { $servers = $db | Where-Object {$_.Client -eq "$($client)"} $newSize = 0 $clientID = ($db | Where-Object {$_.Client -eq "$($client)"} | Select-Object ClientID).ClientID[0] $clientID = "'" + "$($clientID)" + "'" $client = "'" + "$($client)" + "'" ForEach ($server in $servers) { $newSize += $server.Size } $newSize = "'" + "$($newSize)" + "'" Write-Host "$($client)" $query = "UPDATE " + '`history` SET ' + "$($date) = $newSize WHERE " + '`ClientID`' + " = $($clientID);" $update = MySQLQuery $conn "$($query)" }