New-WEMVirtualDriveAssignment.ps1

<#
    .Synopsis
    Create a new Virtual Drive Assignment object in the WEM Database.

    .Description
    Create a new Virtual Drive Assignment object in the WEM Database.

    .Link
    https://msfreaks.wordpress.com

    .Parameter IdSite
    ..

    .Parameter IdAction
    ..

    .Parameter IdAdObject
    ..

    .Parameter IdRule
    ..

    .Parameter DriveLetter
    ..

    .Parameter Connection
    ..

    .Example

    .Notes
    Author: Arjan Mensch
#>
function New-WEMVirtualDriveAssignment {
    [CmdletBinding()]
    param (
        [Parameter(Mandatory=$True, ValueFromPipelineByPropertyName=$True, ValueFromPipeline=$True)]
        [int]$IdSite,
        [Parameter(Mandatory=$True, ValueFromPipelineByPropertyName=$True)]
        [int]$IdAction,
        [Parameter(Mandatory=$True)]
        [int]$IdADObject,
        [Parameter(Mandatory=$True)]
        [int]$IdRule,
        [Parameter(Mandatory=$True)][ValidatePattern('^[a-zA-Z]+$')][ValidateLength(1,1)]
        [string]$DriveLetter,

        [Parameter(Mandatory=$True)]
        [System.Data.SqlClient.SqlConnection]$Connection
    )

    process {
        Write-Verbose "Working with database version $($script:databaseVersion)"

        # check uniqueness
        $SQLQuery = "SELECT COUNT(*) AS ObjectCount FROM VUEMAssignedVirtualDrives WHERE IdSite = $($IdSite) AND IdVirtualDrive = $($IdAction) AND IdItem = $($IdADObject) AND IdFilterRule = $($IdRule)"
        $result = Invoke-SQL -Connection $Connection -Query $SQLQuery
        if ($result.Tables.Rows.ObjectCount) {
            # name must be unique
            Write-Error "There's already an Assignment object for this combination of Action, ADObject and Rule in the Configuration"
            Break
        }

        Write-Verbose "Assignment is unique: Continue"

        # check if driveletter is allowed
        $DriveLetter = $DriveLetter.ToUpper()

        # grab configuration properties
        $SQLQuery = "SELECT Value AS Exclusions FROM VUEMParameters WHERE IdSite = $($IdSite) AND Name = 'excludedDriveletters'"
        $result = Invoke-SQL -Connection $Connection -Query $SQLQuery
        $excludedDriveletters = $result.Tables.Rows.Exclusions
        Write-Verbose "Found excluded driveletters: $($excludedDriveletters)"

        # DriveLetter must not be excluded in the Configuration
        if (($excludedDriveLetters -split ";") -contains $DriveLetter) {
            # DriveLetter must not be Excluded
            Write-Error "DriveLetter '$($DriveLetter)' is excluded in the Configuration (Exclusions: $($excludedDriveLetters.Replace(";",", ")))"
            break
        }

        $SQLQuery = "SELECT Value AS AllowReuse FROM VUEMParameters WHERE IdSite = $($IdSite) AND Name = 'AllowDriveLetterReuse'"
        $result = Invoke-SQL -Connection $Connection -Query $SQLQuery
        $allowDriveletterReuse = [bool][int]$result.Tables.Rows.AllowReuse
        Write-Verbose "Found Driveletter Re-use setting: $([string]$allowDriveletterReuse)"

        # drivemapping detected, in all assignments, DriveLetter in combination with IdObject must be unique if re-use is $false
        if (-not $allowDriveletterReuse) {
            $SQLQuery = "SELECT COUNT(*) AS ObjectCount FROM VUEMAssignedNetDrives WHERE IdSite = $($IdSite) AND IdItem = $($IdADObject) AND DriveLetter = '$($DriveLetter)'"
            $result = Invoke-SQL -Connection $Connection -Query $SQLQuery
            if ($result.Tables.Rows.ObjectCount) {
                # DriveLetter must be unique
                Write-Error "There's already a Network Drive object using DriveLetter '$($DriveLetter)' assigned to the same Active Directory object"
                break
            }
            $SQLQuery = "SELECT COUNT(*) AS ObjectCount FROM VUEMAssignedVirtualDrives WHERE IdSite = $($IdSite) AND IdItem = $($IdADObject) AND DriveLetter = '$($DriveLetter)'"
            $result = Invoke-SQL -Connection $Connection -Query $SQLQuery
            if ($result.Tables.Rows.ObjectCount) {
                # DriveLetter must be unique
                Write-Error "There's already a Virtual Drive object using DriveLetter '$($DriveLetter)' assigned to the same Active Directory object"
                break
            }
            $foundLetter = $false
            $SQLQuery = "SELECT IdAssignedActionGroup FROM VUEMAssignedActionGroups WHERE IdSite = $($IdSite) AND IdItem = $($IdADObject)"
            $result = Invoke-SQL -Connection $Connection -Query $SQLQuery
            foreach($row in $result.Tables.Rows) {
                $SQLQuery = "SELECT COUNT(*) AS ObjectCount FROM VUEMAssignedActionGroupsProperties WHERE IdAssignedActionGroup = $($row.IdAssignedActionGroup) AND Properties = '$($DriveLetter)'"
                $subResult = Invoke-SQL -Connection $Connection -Query $SQLQuery
                if ($subResult.Tables.Rows.ObjectCount) {
                    # DriveLetter must be unique
                    $foundLetter = $true
                    Write-Error "There's already an object in an Action Group using DriveLetter '$($DriveLetter)' assigned to the same Active Directory object"
                    break
                }
            }
            if($foundLetter) { break }
        }

        # build the query to create the assignment
        $SQLQuery = "INSERT INTO VUEMAssignedVirtualDrives (IdSite,IdVirtualDrive,IdItem,IdFilterRule,DriveLetter,RevisionId) VALUES ($($IdSite),$($IdAction),$($IdADObject),$($IdRule),'$($DriveLetter)',1)"
        $null = Invoke-SQL -Connection $Connection -Query $SQLQuery

        # grab the new assignment
        $SQLQuery = "SELECT * FROM VUEMAssignedVirtualDrives WHERE IdSite = $($IdSite) AND IdVirtualDrive = $($IdAction) AND IdItem = $($IdADObject) AND IdFilterRule = $($IdRule)"
        $result = Invoke-SQL -Connection $Connection -Query $SQLQuery

        $Assignment = Get-WEMVirtualDriveAssignment -Connection $Connection -IdSite $IdSite -IdAction $IdAction -IdADObject $IdADObject -IdRule $IdRule

        # Updating the ChangeLog
        $IdObject = $result.Tables.Rows.IdAssignedVirtualDrive
        New-ChangesLogEntry -Connection $Connection -IdSite $IdSite -IdElement $IdObject -ChangeType "Assign" -ObjectName $Assignment.ToString() -ObjectType "Assignments\Virtual Drive" -NewValue "N/A" -ChangeDescription $null -Reserved01 $null

        # Return the new object
        return $Assignment
    }
}