Update Teamviewer sessions based on database table

Original help question is here: http://forum.devolutions.net/topic16163-teamviewer-session-update-via-powershell.aspx

I had a need to update our sessions based on the name of the logged on user. We use a logon script that collects the username and teamviewer id and places into a database. Using the Powershell script below updates the names of the teamviewer sessions.

Database:
USE [Teamviewer]
GO

/****** Object: Table [dbo].[TeamViewerID] Script Date: 7/9/2014 12:31:09 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[TeamViewerID](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[TVId] [nchar](10) NULL,
[ComputerName] [nvarchar](50) NULL,
[UserName] [nvarchar](50) NULL,
[CombinedName] [nvarchar](50) NULL,
[LastUpdate] [smalldatetime] NULL
) ON [PRIMARY]

GO


Logon script:
'****************************************
'* Teamviewer ID to Database Script *
'* Written by Ken Stoner *
'* For Teamviewer versions: 8,9 *
'****************************************

Dim allComp(500,4)
Dim arrStr, count2

' ForAppending = 8 ForReading = 1, ForWriting = 2
Const ForAppending = 8
Const ForReading = 1
Const ForWriting = 2
Const HKEY_CURRENT_USER = &H80000001
Const HKEY_LOCAL_MACHINE = &H80000002

Function EnvString(variable)
set objShell = WScript.CreateObject( "WScript.Shell" )
variable = "%" & variable & "%"
EnvString = objShell.ExpandEnvironmentStrings(variable)
Set objShell = Nothing
End Function

'Get Enviromental Vars
'user=lcase(EnvString("username"))
strLocalComputer=ucase(EnvString("ComputerName"))
'domain=lcase(EnvString("UserDomain"))

strComputer = "."

Set oReg=GetObject("winmgmts:{impersonationLevel=impersonate}!\" &_
strComputer & "\root\default:StdRegProv")

strKeyPath = "SOFTWARE\Wow6432Node\TeamViewer\Version8"
strValueName = "ClientID"
oReg.GetDWORDValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,dwValue
if len(dwValue) >= 9 then TVID = dwValue end if

if len(TVID) < 1 then

strKeyPath = "SOFTWARE\Wow6432Node\TeamViewer\Version9"
strValueName = "ClientID"
oReg.GetDWORDValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,dwValue
if len(dwValue) >= 9 then TVID = dwValue end if
end if

Set oNetwork = CreateObject("WScript.Network")
sDomain = oNetwork.UserDomain
sUser = oNetwork.UserName
sADSPath= sDomain & "/" & sUser
Set oUser = GetObject("WinNT://" & sADSPath & ",user")
strUser = oUser.FullName

NewName = strUser & " - " & strLocalComputer

Dim conn, connectString, strSQL
Set conn=CreateObject("ADODB.Connection")
connectString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=username;Data Source=databaseservername;Password=password;DATABASE=Teamviewer"
conn.open connectString


strSQL="INSERT INTO TeamViewerID ([TVId],[ComputerName],[UserName],[CombinedName],[LastUpdate]) VALUES ('" & TVId & "','" & strLocalComputer & "','" & strUser & "','" & NewName & "','" & Now & "')"
'MsgBox strSQL

Dim objRS
Set objRS = Conn.Execute(strSQL,Rows)

Conn.Close
Set objRS = Nothing

wscript.quit



PowerShell to update RDM:
#********************************************
#* PS script to update Teamvier session names *
#* Written by Ken Stoner *
#********************************************

$ServerName = "databaseserver"
$DatabaseName = "Teamviewer"
$Query = "Select distinct rtrim(ltrim(TVId)) as TVID, CombinedName from (SELECT *,MaxDate= Max([LastUpdate])OVER (PARTITION BY TVId)FROM [Teamviewer].[dbo].[TeamViewerID]) as s where LastUpdate = MaxDate and len(TVId) <>0"

#Timeout parameters
$QueryTimeout = 120
$ConnectionTimeout = 30

#Action of connecting to the Database and executing the query and returning results if there were any.
$conn=New-Object System.Data.SqlClient.SQLConnection
$ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $ServerName,$DatabaseName,$ConnectionTimeout
$conn.ConnectionString=$ConnectionString
$conn.Open()
$cmd=New-Object system.Data.SqlClient.SqlCommand($Query,$conn)
$cmd.CommandTimeout=$QueryTimeout
$ds=New-Object system.Data.DataSet
$da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
[void]$da.fill($ds)
$conn.Close()

$dataTable = $ds.Tables[0]

#Get existing sessions "Teamviwer"
$sessions = Get-RDM-Session | where {$_.Session.Kind -eq "TeamViewer"}
#Get the Teamviwer ID inside from the session
$TVIDs = $sessions.Session.GetProperty("TeamViewer", "ID")


#Update the Sessions or add them
$dataTable | FOREACH-OBJECT {

$HostTVId= $_.TVId
$HostName= $_.CombinedName

if ($TVIDs -contains $HostTVId)
{
#Update session name
$UpdateSession = Get-RDM-Session |where {$_.Session.getProperty("TeamViewer", "ID") -eq $HostTVId}
$UpdateSession.Session.Name = $HostName
Write-Host "Updating: " $UpdateSession.Session.Name
Set-RDM-Session $UpdateSession.Session -NoRefresh;
}
else
{
#Add Session name
Write-Host "Adding: " + $_.CombinedName
$session = New-RDM-Session -Name $_.CombinedName -Kind "TeamViewer"
$session.Group = "TeamViewer"
$session.TeamViewer.ID = $_.TVId;
Set-RDM-Session $session -NoRefresh;
$pass = ConvertTo-SecureString "PWHere" -asplaintext -force;
Set-RDM-Password -ID $session.ID -Password $pass -NoRefresh;
}
}

edited by kstonernasg on 7/16/2014