Remove all Delegate from Skype SQL Database „RTCLocal“

We know that we have sometimes Problem with Delegates, we can´t read / Change / remove with sefautil.exe.

I create a Script for this issue, this is very helpful

If you have more delegate under „Manage Calls from:“ and no other Chance to remove it, you can use this small script.

 

Example: Get-SFBDelegate -DelegatorSip test1@contoso.com -DelegateSIP Test2@contoso.com


function Get-SfBDelegate {
Param([Parameter(Mandatory=$True,Position=0)][string]$delegateSIP,
[Parameter(Mandatory=$True,Position=1)][string]$delegatorSIP)

$SFBPools = Get-CsService | sort identity |select identity
$table =@()
foreach ($SFBPool in $SFBPools.identity)
{
$split = $SFBPool.Split(„:“)
$table += @{‚Function‘ = $split[0];’FQDN’=$split[1]}
}
$out = $table.ForEach({[PSCustomObject]$}) | ?{$.Function -match „registrar“} | Group-Object -Property Function

$menu = @{}
$Fqdn = ($out.group | ?{$_.FQDN -match „pool“} | select fqdn -Unique).FQDN
if ($fqdn.Count -gt „1“){
for ($i=1;$i -le $FQDN.count; $i++) {
Write-Host “ $i. $($FQDN[$i-1])“
$menu.Add($i,($FQDN[$i]))
}
}
else{
for ($i=1;$i -le $FQDN.count; $i++) {
Write-Host “ $i. $($FQDN)“
$menu.Add($i,($FQDN[$i]))
}
}
[int]$ans = Read-Host ‚Enter selection‘
$selection = $menu.Item($ans)
$servers = (Get-CsComputer -Pool $selection).identity

###################################################################
#$Servers = (Get-CsComputer | Where-Object -Property Pool -Match $pool | Select-Object identity).identity

$array =@()

foreach ($server in $Servers){

$DelegateQuery = „Select * From rtc.dbo.Resource Where UserAtHost='$delegateSIP‚“
$DelegateResult= (Invoke-Sqlcmd -ServerInstance $Server\rtclocal -Query $DelegateQuery)
$DelegatorQuery= „Select * From rtc.dbo.Resource Where UserAtHost='$delegatorSIP‚“
$DelegatorResult= (Invoke-Sqlcmd -ServerInstance $Server\rtclocal -Query $DelegatorQuery)

$resourceID = $DelegatorResult.ResourceId
$DelegationQuery = „select * From [rtc].[dbo].[Delegate] Where DelegatorId = '$resourceID‚“
$DelegationResult =(Invoke-Sqlcmd -ServerInstance $Server\rtclocal -Query $DelegationQuery)

foreach ($Delegation in $DelegationResult){
$search = $Delegation.DelegateId
$SQLscript = „select * From rtc.dbo.Resource where ResourceId = '$search‚“
$return= (Invoke-Sqlcmd -ServerInstance $Server\rtclocal -Query $SQLscript)

if ($DelegateResult.ResourceId -notmatch $search){$toRemove = „yes“} else {$toRemove = „no“}

$Subscribs = New-Object psobject -Property @{DelegatorId = $Delegation.DelegatorId;
DelegatorSIP =$DelegatorResult.UserAtHost;
DelegateId = $Delegation.DelegateId;
DelegateSIP =$return.UserAtHost;
FrontendServer = $server;
toRemove = $toRemove}
$array += $Subscribs
}
}

($Global:array = $array)| sort toRemove | ft -a Delegator*, Delegate*, Front*, to*

if (!([string]::IsNullOrEmpty($Global:array))){

$n = „n“
$n = Read-Host -Prompt „Do you like to remove all with property toRemove = yes? Press y = yes, n = no“

if ($n -eq ‚y‘){
foreach ($arr in $array){
if ($arr -match „yes“) {
$removeDelegator = $arr.DelegatorId
$removeDelegate = $arr.DelegateId
$DeleteQuery =“Delete From [rtc].[dbo].[PromptedSubscriber] Where PublisherId = '$removeDelegator‚ AND SubscriberId = '$removeDelegate‚
Delete From [rtc].[dbo].[Delegate] Where DelegatorId = '$removeDelegator‚AND DelegateId = '$removeDelegate‚“

$server = $arr.FrontendServer+“\rtclocal“
Invoke-Sqlcmd -ServerInstance $server -Query $DeleteQuery -ErrorAction SilentlyContinue
Write-Output „DELETED !!“
$arr | ft -a
}

$SubscriberId | ft -a
}
}
}
else {Write-Host „we dont found Entrys for the delegator: $delegatorSIP and delegate: $delegateSIP „}

}# Remove Delegator in SQL DATABASE end