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